Error Exporting to Excel

[Guest]

I get the error message:

"Exception Details: System.Data.OleDb.OleDbException: Table 'Hank' already
exists."

when executing the following code:

Dim AccessConn As New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:\My Documents\db1.mdb")

AccessConn.Open()

'New sheet in Workbook
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO [Excel 8.0; DATABASE=c:\My Documents\Book11.xls; HDR=NO;].[Hank] FROM
myTable", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

(Code is provided by Paul Clement, 9-13-2005)

It's interesting that the data from the table is exported to the newly
created workbook, "Book11", and the data resdies in the worksheet "Hank", but
the error message "Table 'Hank' already exists" comes up anyway.

What is the best way to get rid of the error as my ASP.Net application bombs?

Thank you, Mark

 

[Cor Ligthert [MVP]]

Mark,

Although I am not sure, you can use this of course only one time.

That is in my opinon not likely because every client can do this again and
again, be aware that you create this access table on the server.

I hope this helps,

Cor

 

[Cor Ligthert [MVP]]

Exceltable

 

[Paul Clement]

¤ I get the error message:
¤
¤ "Exception Details: System.Data.OleDb.OleDbException: Table 'Hank' already
¤ exists."
¤
¤ when executing the following code:
¤
¤ Dim AccessConn As New System.Data.OleDb.OleDbConnection
¤ ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:\My Documents\db1.mdb")
¤
¤ AccessConn.Open()
¤
¤ 'New sheet in Workbook
¤ Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
¤ INTO [Excel 8.0; DATABASE=c:\My Documents\Book11.xls; HDR=NO;].[Hank] FROM
¤ myTable", AccessConn)
¤
¤ AccessCommand.ExecuteNonQuery()
¤ AccessConn.Close()
¤
¤ (Code is provided by Paul Clement, 9-13-2005)
¤
¤ It's interesting that the data from the table is exported to the newly
¤ created workbook, "Book11", and the data resdies in the worksheet "Hank", but
¤ the error message "Table 'Hank' already exists" comes up anyway.
¤
¤ What is the best way to get rid of the error as my ASP.Net application bombs?
¤
¤ Thank you, Mark

The SQL statement you are using will *always* attempt to create a new Table (Worksheet in Excel). If
you're adding data to an existing table you should be using an Insert statement instead of Select
Into.

If you're saying that the Table 'Hank' currently does not exist then I would have to see the
Workbook in order to determine what might be happening.


Paul
~~~~
Microsoft MVP (Visual Basic)