Export DS to Excel

[David]

It looks like this is a very popular subject, but all the previous messages
have been deleated from the server. In my application, I need to export a
table in my MSAccess database to an Excel 2003 spreadsheet. I have no
trouble with individual ranges, but am having trouble finding a good example
where the data from an entire table is written to the worksheet.

Thanks in advance for any suggestions.

David

 

[Cor Ligthert [MVP]]

David,

Why don't you ask it in the newsgroup

microsoft.public.excel.programmer

And than direct from Access to VBA.

Kelly wrote once a sample from dataset to xls,
I have the idea that the sample has some overhead, however it works.

http://www.kjmsolutions.com/datasetarray.htm

I hope this helps,

Cor

 

[Paul Clement]

¤ It looks like this is a very popular subject, but all the previous messages
¤ have been deleated from the server. In my application, I need to export a
¤ table in my MSAccess database to an Excel 2003 spreadsheet. I have no
¤ trouble with individual ranges, but am having trouble finding a good example
¤ where the data from an entire table is written to the worksheet.
¤

See if the following works for you:

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

AccessConn.Open()

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

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Richard Dudley]

There is a wonderful free library at
http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx for doing this
kind of thing. Carlos also has a code generator that will reverse-engineer
the styles of a finished workbook, and yuo can integrate your data code
with. Very nice stuff.