Excel Export from Access

[OldManRiver]

All,

I'm trying to run an export from Access to Excel, but the transferdatabase method does not work well with the query I have:

    WHRstr = "WHERE ([tim_wdt] >= #" & FMTval & "#) ORDER By [tim_wdt] desc;"
    SQLstr = "SELECT * FROM qryMARTIN " & WHRstr

Seems all the method is capable of is a straight table export.

Been screwing with this code:

    DoCmd.SetWarnings False
'    DoCmd.RunSQL SQLstr
'    DoCmd.TransferDatabase acExport, acSpreadsheetTypeExcel9, RSs, outXLFile
    
    DoCmd.TransferSpreadsheet _
        TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel9, _
        Source:=SQLstr, _
        FileName:=outXLFile
'        ObjectType:=acQuery, _
'        FileName:=outXLFile, _
'        HasFieldNames:=True
    DoCmd.SetWarnings True

as you can see from all the commented out lines.

So my question:
Is there a way to make it work with a query that includes a "WHERE" filtering statement?
or
Do I have to run the query as recordset, save to a temp table and run the transferdatabase on the new temp table?

Thanks!

OMR

 

[pbaldy]

I typically use automation to export, but I'm pretty sure the table argument has to be a table or saved query. I don't think it's the WHERE clause that's getting you, it's the fact that it's not a saved query. Can you create a saved query that gets the criteria from a form and export that?

 

[OldManRiver]

PB,

So if I declare a "QueryDef" and save my query to it it should run?

OMR

 

[pbaldy]

Wouldn't be the first time I was wrong, but that is my thinking, yes.

 

[OldManRiver]

All,

Where I am at with my code. Went to the temp output table, as I know that and code is:

Sub Run_Mtn(MyDays)
    ' Run the Temp Marting Query for last 15 days
    Dim dbs As DAO.Database, DatVal, DatWrt
    Dim DayVal, FMTval, SQLstm, WHRstr
    Set dbs = CurrentDb
    DatWrt = Format(Now, "yy-mm-dd")
    outXLFile = "Y:\Employees\Martin Phillips - Public\qryMARTIN " & MyDays & " Day (" & DatWrt & ").xls"
    outXLWkSht = "Last " & MyDays & " Days"
    DatVal = Format(Now - MyDays, "mm/dd/yy")
    FMTval = DateSerial(Year(DatVal), Month(DatVal), Day(DatVal))
'    DoCmd.Hourglass False
    DoCmd.SetWarnings False
    SQLstm = "INSERT INTO tblMARTIN ( mrt_sts, mrt_tno, mrt_wdt, " & _
             "mrt_nam, mrt_pds, mrt_pno, mrt_full, mrt_stp, mrt_wds, " & _
             "mrt_aml, mrt_mbr, mrt_ahr, mrt_wir, mrt_cnm, mrt_afe ) " & _
             "SELECT b.thd_sts, b.thd_tno, b.tim_wdt, b.cli_nam, " & _
             "b.prj_des, b.thd_pno, b.emp_full, b.itm_stp, b.itm_des, " & _
             "b.mlg_aml, b.mlg_mbr, b.tim_ahr, b.tim_wir, b.cnty_name, " & _
             "b.prj_afe FROM qryMARTIN as b "
    WHRstr = "WHERE ([tim_wdt] >= #" & FMTval & "#) " & _
             "ORDER By [tim_wdt] desc;"
    SQLstm = SQLstm & WHRstr
    DoCmd.RunSQL "DELETE * FROM tblMARTIN"
    DoCmd.RunSQL SQLstm
    DoCmd.TransferDatabase acExport, acSpreadsheetTypeExcel9, "tblMARTIN", outXLFile
    DoCmd.SetWarnings True
    DoEvents
    Set dbs = Nothing
End Sub

But still getting error from the "TransferDatabase" line of:

Type mismatch

and get no highligting so have no clue of what the problem is.

Code runs fine down to that line.

OMR

 

[boblarson]

You are exporting to EXCEL so you should be using the DoCmd.TransferSPREADSHEET not TransferDatabase. Excel is not one of the valid "database" types to use for TransferDatabase.

Use TransferSpreadsheet instead.

 

[pbaldy]

I believe you want TransferSpreadsheet, not TransferDatabase. The third argument of TransferDatabase is the name of a database, not a table.

 

[pbaldy]

Saved again!

 

[OldManRiver]

Excel Export - Working

All,

Got it working but not using the "TransferDatabase" which I want to master.

Here is the working code:

Option Compare Database
Public TargetForm As Form, SourceForm As Form
Sub Run_Mtn(MyDays)
    ' Run the Temp Marting Query for last 15 days
    Dim dbs As dao.Database, DatVal, DatWrt
    Dim DayVal, FMTval, SQLstm, WHRstr
    Set dbs = CurrentDb
    DatWrt = Format(Now, "yy-mm-dd")
'    outXLFile = "Y:\Employees\Martin Phillips - Public\qryMARTIN " & MyDays & " Day (" & DatWrt & ").xls"
    outXLFile = "C:\Ops System\Reports\qryMARTIN " & MyDays & " Day (" & DatWrt & ").xls"
    outXLWkSht = "Last " & MyDays & " Days"
    DatVal = Format(Now - MyDays, "mm/dd/yy")
    FMTval = DateSerial(Year(DatVal), Month(DatVal), Day(DatVal))
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    SQLstm = "INSERT INTO tblMARTIN ( mrt_sts, mrt_tno, mrt_wdt, " & _
             "mrt_nam, mrt_pds, mrt_pno, mrt_full, mrt_stp, mrt_wds, " & _
             "mrt_aml, mrt_mbr, mrt_ahr, mrt_wir, mrt_cnm, mrt_afe ) " & _
             "SELECT b.thd_sts, b.thd_tno, b.tim_wdt, b.cli_nam, " & _
             "b.prj_des, b.thd_pno, b.emp_full, b.itm_stp, b.itm_des, " & _
             "b.mlg_aml, b.mlg_mbr, b.tim_ahr, b.tim_wir, b.cnty_name, " & _
             "b.prj_afe FROM qryMARTIN as b "
    WHRstr = "WHERE ([tim_wdt] >= #" & FMTval & "#) " & _
             "ORDER By [tim_wdt] desc;"
    SQLstm = SQLstm & WHRstr
    DoCmd.RunSQL "DELETE * FROM tblMARTIN"
    DoCmd.RunSQL SQLstm
'    DoCmd.TransferDatabase acExport, acSpreadsheetTypeExcel9, "tblMARTIN", outXLFile
    Call ExpTbl(outXLFile, "tblMARTIN", outXLWkSht)
    DoCmd.SetWarnings True
    DoEvents
    Set dbs = Nothing
    DoCmd.Hourglass False
End Sub
Sub ExpTbl(MyFile, MyTable, MyWSht)
    Dim dbs As dao.Database
    Set dbs = CurrentDb
    'If excel file already exists, you can delete it here
    If Dir(MyFile) <> "" Then Kill MyFile
    dbs.Execute _
        "SELECT * INTO [Excel 8.0;DATABASE=" & MyFile & _
        "].[" & MyWSht & "] FROM " & "[" & MyTable & "]"
    dbs.Close
    Set dbs = Nothing
End Sub

Your help in learing the method I want and optimizing this code will be appreciated.

Thanks!

OMR

 

[Richard66]

Hi there! just out of interest theres a nice thing called access explosion from a company called datapig. I have had a look at it and its very usefull, im not sure if this will help you out with any further data you have to send to a spreadsheet but its great for sending tables and querys to excel. anyway when you get a chance have a look see what you think. Regards Richard.