Export access table to Excel format

[santoshdream]

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.....

 

[HiTechCoach]

This is a duplicate. see original here:

http://access-programmers.co.uk/forums/showthread.php?t=164790

 

[RAJPUROHIT11]

Yo man,
i dont know about the VB code, but its fairly simple to convert a access table to excel format. you just need to go to the macors and write a new one using the function
OutputTo
in the window near the foot note selecte these options
object type - Table
object name - <name of the table from the dropdown list>
Output format - Microsoft Excel 97-2003 (*.xls)
output file - C:\D\DATA.xls
autostart - no
Tempalte file - <keep it blank>
encoding - <keep it blank>