March 3rd, 2008, 06:14 AM
Join Date: Feb 2008
Time spent in forums: 4 h 30 m
Reputation Power: 7
Export Table data into an Excel SpreadSheet (VBA, ACCESS)
I have an export function below that will export my table "Test" to an Excel Spreadsheet.
However I want it so i can choose where that data in the "Test" table will go in the Excel Spreadsheet i.e. I want to export all the data in to Cell "B2" of the SpreadSheet - at the moment it will export all the data into "A1"
Any help or ideas?
Private Sub Command3_Click()
'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED
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 = "E:\CSC\LDMS\LDMSDatabaseApp\LDMS_Spec.xls"
strWorksheet = "WorkSheet1"
strDB = "E:\CSC\LDMS\LDMSDatabaseApp\LDMS_IFF_APP.mdb"
strTable = "Test"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
Set objDB = Nothing