473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with adding field to Excel spreadsheet using 'alter table'

Hi, I'm trying to add additional column using 'alter table' command via
OleDB to Excel workbook (one sheet called queExportBOND):

Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\BOND.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Dim oCn As New OleDbConnection(strConn)
Try

oCn.Open()

Try

Dim oCmd As New OleDbCommand("ALTER TABLE [queExportBOND] ADD new_column
Double", oCn)
oCmd.ExecuteNonQuery()

Finally

oCn.Close()

End Try

Catch ex As Exception
MsgBox(ex.ToString)

End Try

Unfortunately I'm receiving the error 'Cannot find table or range'. How can
I correct this ? What I'm doing wrong ?

TIA

Przemek
Nov 20 '05 #1
5 10285
On Tue, 18 Nov 2003 19:31:39 +0100, Przemek Wrzesinski <pw****@nospam.gazeta.pl> wrote:

¤ Hi, I'm trying to add additional column using 'alter table' command via
¤ OleDB to Excel workbook (one sheet called queExportBOND):
¤
¤ Dim strConn As String
¤
¤ strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=C:\Temp\BOND.xls;" & _
¤ "Extended Properties=""Excel 8.0;HDR=YES;"""
¤
¤ Dim oCn As New OleDbConnection(strConn)
¤ Try
¤
¤ oCn.Open()
¤
¤ Try
¤
¤ Dim oCmd As New OleDbCommand("ALTER TABLE [queExportBOND] ADD new_column
¤ Double", oCn)
¤ oCmd.ExecuteNonQuery()
¤
¤ Finally
¤
¤ oCn.Close()
¤
¤ End Try
¤
¤ Catch ex As Exception
¤ MsgBox(ex.ToString)
¤
¤ End Try
¤
¤ Unfortunately I'm receiving the error 'Cannot find table or range'. How can
¤ I correct this ? What I'm doing wrong ?

Have you tried adding a $ character to the end of your table (worksheet) name?
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #2
Pięknego dnia Wed, 19 Nov 2003 09:36:38 -0600, osobnik ukrywajacy sie
pod pseudonimem Paul Clement
<Us***********************@swspectrum.com> w wiadomosci
<rd********************************@4ax.com> napisal/a:


Have you tried adding a $ character to the end of your table (worksheet) name?


Yes, I've tried with and without '$' character, and still the error is
appearing :/

Przemek
Nov 20 '05 #3
Cor
Hi przemek,

Did you try this in the ADO.NET newsgroup, there is almost no VB language in
your code, so visitors from this newsgroup are not so affected with your
problem.

(By instance the only SQL I use is the Select and some statements to Create
a database and Insert tables and then is stops) And I use the commandbuilder
for the rest. (You cannot use that for your problem I think).

So maybe you can post this message to the Ado.net newsgroup also, I know
there are a lot who are good in it.

There are here are also who knows alot about it, but that is not why they
visit this newsgroup.

I hope you find your answer soon.

Cor
Nov 20 '05 #4
When I try this I get 'Invalid operation' error, which is what I
expected because I understand ALTER TABLE is not supported for Excel
(neither is DELETE, BTW).

--

"Cor" <no*@non.com> wrote in message news:<eR**************@TK2MSFTNGP10.phx.gbl>...
Hi przemek,

Did you try this in the ADO.NET newsgroup, there is almost no VB language in
your code, so visitors from this newsgroup are not so affected with your
problem.

(By instance the only SQL I use is the Select and some statements to Create
a database and Insert tables and then is stops) And I use the commandbuilder
for the rest. (You cannot use that for your problem I think).

So maybe you can post this message to the Ado.net newsgroup also, I know
there are a lot who are good in it.

There are here are also who knows alot about it, but that is not why they
visit this newsgroup.

I hope you find your answer soon.

Cor

Nov 20 '05 #5
Here's a suggestion for a workaround:

1. Use a SELECT INTO to create a new temporary table (sheet) with an
appended column:

SELECT
RefID, Surname, 0 AS MyNewColumn
INTO NewTempTable
FROM [PersonalDetails$];

2. Drop the original table:

DROP TABLE [PersonalDetails$];

3. Use a SELECT INTO to re-create the original table:

SELECT
RefID, Surname, MyNewColumn
INTO PersonalDetails
FROM [NewTempTable$];
4. Drop the temporary table:

DROP TABLE [NewTempTable$];

The above assumes you are using the OLE DB Provider for Jet.

--

Przemek Wrzesiński <pw****@nospam.gazeta.pl> wrote in message news:<p7********************************@4ax.com>. ..
Pięknego dnia Wed, 19 Nov 2003 09:36:38 -0600, osobnik ukrywajacy sie
pod pseudonimem Paul Clement
<Us***********************@swspectrum.com> w wiadomosci
<rd********************************@4ax.com> napisal/a:


Have you tried adding a $ character to the end of your table (worksheet) name?


Yes, I've tried with and without '$' character, and still the error is
appearing :/

Przemek

Nov 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Tom wilson | last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net. The connection is open, the adapter is connected and the dataset is loaded. Here's the code...
0
by: me here | last post by:
I have a VBA subroutine that links an MS Excel spreadsheet and copies the data into a local table. This process is controlled by a form that allows users to select the spreadsheet from the file...
8
by: mytfein | last post by:
Hi Everyone, Background: Another department intends to ftp a .txt file from the mainframe, for me to process. The objective is to write a vb script that would be scheduled to run daily to...
6
by: David Gartrell | last post by:
Hi i'm trying to import an Excel Spreadsheet into Access2000 but the data types for two of the fields in my imported table are being identified incorrectly. Is there a way of using some VB code in...
3
by: Eyal Zinder | last post by:
Hello All, Here is a little problem I ran into. I'd like to see if anyone can help me solve it I have an HTML table that I assign to a label control. I set my Response.ContentType =...
5
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
5
by: rdemyan via AccessMonster.com | last post by:
I have a table with about 80 fields. I'm using an import process to populate the table. It works fine, except for the following: Users generally don't specify values for a lot of numerical...
9
by: QCLee | last post by:
Sir can you help me to transfer my Access Query to MS excel? i have a command button on the form to export the parameter query named "HVACWindwardQuery" to excel spreadsheet and i got the codes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.