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]

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

Try:

[COLOR=blue]Private Sub Command3_Click()[/COLOR]
[COLOR=blue]'Export function[/COLOR]
[COLOR=blue]'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL[/COLOR]
[COLOR=blue]'REFERENCE TO DAO IS REQUIRED[/COLOR]
[COLOR=blue]Dim strExcelFile As String[/COLOR]
[COLOR=blue]Dim strWorksheet As String[/COLOR]
[COLOR=blue]Dim strDB As String[/COLOR]
[COLOR=blue]Dim strTable As String[/COLOR]
[COLOR=blue]Dim objDB As Database[/COLOR]
[COLOR=blue]'Change Based on your needs, or use[/COLOR]
[COLOR=blue]'as parameters to the sub[/COLOR]
[COLOR=blue]strExcelFile = "C:\D\DATA.xls"[/COLOR]
[COLOR=blue]strWorksheet = "WorkSheet1"[/COLOR]
[COLOR=blue]strDB = "C:\D\PS.mdb"

 ''' strTable = "Test"

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

[/COLOR]
[COLOR=blue]Set objDB = OpenDatabase(strDB)[/COLOR]
[COLOR=blue]'If excel file already exists, you can delete it here[/COLOR]
[COLOR=blue]If Dir(strExcelFile) <> "" Then Kill strExcelFile[/COLOR]
[COLOR=blue]objDB.Execute _[/COLOR]
[COLOR=blue]"[COLOR=seagreen][B]SELECT *[/B][/COLOR] INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "(" & strTable & ")"
objDB.Close
Set objDB = Nothing[/COLOR]

 
[COLOR=blue]End Sub[/COLOR]

 

[santoshdream]

Thanks a lot....the above mentioned code is working absolutely fine.

I need more help from you I have changed my query to

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.Difference) Not Like "0") AND ((DATA.[Owned By]) Like "SDTR-MAN - FX" Or (DATA.[Owned By])="SDTR - FX" Or (DATA.[Owned By])="SDTR"))
ORDER BY DATA.[Owned By];

could you pleazzzzz help me to fix this query in above mentioned your code.


I have tried the below mentioned code but I failed to write it ..



Private Sub Command0_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"
strTable = "SELECT DATA.Account, DATA.Affiliate, DATA.Deal, DATA.MFR, "
strTable = strTable & " DATA.GL, DATA.YTD_Balance, DATA.Adjustment, "
strTable = strTable & " DATA.Difference, DATA.Description, DATA.[Owned By] "
strTable = strTable & " FROM Data WHERE (((DATA.Difference) Not Like "
strTable = strTable & """0"")) AND (((Data.[Owned By]) Like "
strTable = strTable & """SDTR-MAN - FX"")) OR "
strTable = strTable & """(DATA.[Owned By])="
strTable = strTable & """SDTR - FX"" OR"
strTable = strTable & """(DATA.[Owned By])="
strTable = strTable & """SDTR"")) ORDER BY DATA.Difference"
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

 

[HiTechCoach]

Do you have the original code working in your database?


Not knowing the code works and it wants to export a table, if it were me, I would make the query a make table query and run it at the top of the code.

In the original code, all you would need to do is change this line:

strTable = "Test"

to be the table created with the make table query. You can use the rest of the code unchanged.

 

[HiTechCoach]

Hi,

Thanks a lot for your reply...


Please find the enclosed file as it is showing error msg.

I want to add this query code to export my access table data to Excel sheet.

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.Difference) Not Like "0") AND ((DATA.[Owned By]) Like "SDTR-MAN - FX" Or (DATA.[Owned By])="SDTR - FX" Or (DATA.[Owned By])="SDTR"))
ORDER BY DATA.[Owned By];


" On click submit button" but I am not able to do it. I am so confused please help me.

Please find the enclosed file. In this file plz check the form command button.


Thanks a lot for your time and consideration


Regards

Santosh

I did it the way I suggested last by using a make table query so that the original code will still work since it wants a table and not a query.

Note: I fixt the criteria in the query. You were not using the LIKE properly and the data type "0" was not matching.

See the attached

 

[santoshdream]

Hi,

"THANKS A LOT"

" YOU ARE GREAT "

 

[HiTechCoach]

Hi,

"THANKS A LOT"

" YOU ARE GREAT "

You're welcome!

Glad I could assist.