Exporting to Excel Sheet

Hi all,

How can I export my Access Table to Excel Sheet thru VB at runtime.

Pls guide.

Regards,
Prakash

Create an instance of Excel, and either use the TransferSpreadsheet method if its just a straight export, or output cell by cell if you need to add in extras (formulas, graphs etc). You'll need to add a ref. to the Excel object library in your project.

There are zillions of examples of how to do this sort of thing on other threads here. Do a few searches and you should be able to find what you need.

J.

VB Code:

Private Sub ExportOneTable()
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
 
'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "C:\My Documents\MySpreadSheet.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\My Documents\MyDatabase.mdb"
strTable = "MyTable"
 
Set objDB = OpenDatabase(strDB)
 
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
 
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
 
End Sub

hack,

thanks for your code is has helped me greatly....

however is the below line:

strExcelFile = "C:\My Documents\MySpreadSheet.xls"

is changed to

strExcelFile = "C:\My Documents\AA..xls"

notice the dot after AA

an error occurs when Select * Into comes into play (invalid bracketing)

is there any way around this as i need to have . after certain letters (share stock code)

cheers

Try this

VB Code:

strExcelFile = "C:\My Documents\AA" & Chr(46) & ".xls"

Chr(46) by the way, is the dot