Hi,

I am trying following code its working fine.

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"

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"


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


But my proble is that I want to use the following query code in my above mentioned code but its showing error while I'm going to click submit button.

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];

Please help me.



Please find the enclosed file for your reference. I want to put this code in the FORM1 to " Export EXCEL BUTTON "

I need same help in HOW TO IMPORT EXCEL DATA IN ACCESS TABLE (please see my form1)

" I WANT TO IMPORT DATA FROM MY EXCEL SHEET "DPS.XLS"



" Thanks a lot for your time and consideration "

Regards

Attached Files

macro.zip (46.9 KB, 51 views)

Ok, Try this SQL

SELECT DATA.Account, DATA.Affiliate, DATA.MFR, DATA.GL, DATA.YTD_Balance, DATA.Adjustment, DATA.Difference, DATA.Description, DATA.[Owned By]
FROM DATA
WHERE (((DATA.Difference)<>0)) OR (((DATA.[Owned By]) In ("SDTR-MAN - FX","SDTR - FX","SDTR")));

However, you might find it easier to use the TRANSFERSPREADSHEET method rahter than Office Automation.