Hi all,
How can I export my Access Table to Excel Sheet thru VB at runtime.
Pls guide.
Regards,
Prakash
Printable View
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 thisChr(46) by the way, is the dotVB Code:
strExcelFile = "C:\My Documents\AA" & Chr(46) & ".xls"