Export access table to Excel format

santoshdream

Registered User.
Local time
Yesterday, 22:30
Joined
Jan 22, 2009
Messages
22
Hi All,

I am trying to use the following code in "blue". Its working fine. But my problem is I want to use the below mentioned query in "red" in the following code but it is showing error msg " Expected end of statement "

**********************************************
SELECT DATA.Account, DATA.Affiliate, DATA.Deal, DATA.MFR, DATA.GL, DATA.YTD_Balance, DATA.Adjustment, DATA.Difference, DATA.Description, DATA.[Owned By]
FROM Data
WHERE (((Data.[Owned By]) Like "SDTR-MAN - FX"))
ORDER BY DATA.Difference;
*************************************************

Private Sub Command3_Click()
'Export function
'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 = "C:\D\DATA.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\D\PS.mdb"
strTable = "Test"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
'I want to use the above mentioned query here"

"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing



End Sub


Please help urgent.....
 

Users who are viewing this thread

Back
Top Bottom