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

  • Thread starter Thread starter Przemek Wrzesinski
  • Start date Start date
P

Przemek Wrzesinski

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
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Piêknego dnia Wed, 19 Nov 2003 09:36:38 -0600, osobnik ukrywajacy sie
pod pseudonimem Paul Clement
<[email protected]> w wiadomosci
Have you tried adding a $ character to the end of your table (worksheet) name?
Click to expand...

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

Przemek
 
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
 
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).
 
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.

--
 
Back
Top