DoCmd TransferSpreadsheet Help

[pperilman]

I have code written to export 30+ queries to excel using the same date parameters. It runs and creates all of the export files but each file only contains the field headers and none of the data. I have included the code below. Please let me know if there is something I am missing.

Private Sub cmdReports_Click()

Dim qrys As DAO.QueryDefs 'You need a reference to DAO
Dim qry As DAO.QueryDef
strFilePath = "M:\Internal Audit\PCard and IExpense\"

Set qrys = CurrentDb.QueryDefs 'skip database variable / only needed once

For Each qry In qrys
If Left(qry.Name, 9) = "Quarterly" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qry.Name, strFilePath & qry.Name & ".xls"
End If
Next
Set qry = Nothing
Set qrys = Nothing


End Sub

 

[adalger]

This method works fine for me in Access 2000 with Office 2007. Can you provide an example of one of the queries that isn't working, and the results of the query itself when you open it from the database window?

TMTOWDI - it's not just for Perl any more

 

[jsteph]

pperilman,
You mentioned 'date parameters'. Do the queries have acual parameters (ie, where clause items enclosed in brackents [])?
If so, is the transferspreasheet asking for those?
It should ask each time the loop runs, and you'd have to manually supply them.

If it's hard-coded dates in the Where clause, then what adalger said is your next step.
--Jim

 

[pperilman]

jsteph - I have a form where I can enter the dates and the query runs based on the dates entered in the form.

adalger - The query is just a select query that I need to run quarterly. There are over 30 of them and I was trying to figure out a way to export them to excel all at once rather than going one by one and using the "Analyze with Excel" feature in Access.

 

[adalger]

Is cmdReports on the same form as the date fields you're using? If not, is this form open and does it have valid dates in those fields when you test the function?

Really, seeing *one* example of the full query, the output when you click your button, and the output when you open the query as a datasheet under the same conditions would be *extremely valuable troubleshooting information* that we could all use to help you get to an answer more quickly.

TMTOWDI - it's not just for Perl any more

 

[pperilman]

cmdReports is the name of the button on the form where the dates are entered.

Here is the SQL for one of the queries.

SELECT [PCard Transactions].[Full Name], [Employee Data Table].[Supervisor Name], [PCard Transactions].[Transaction Date], [PCard Transactions].Amount, [PCard Transactions].[Merchant Code], [PCard Transactions].[Merchant Name], [Merchant Code Table].[MC Description], [PCard Transactions].Account, [COA - Account Code Table].[Account Description], [PCard Transactions].Description
FROM [COA - Account Code Table] INNER JOIN (([Employee Data Table] INNER JOIN [Supervisor Table] ON [Employee Data Table].[Person Name] = [Supervisor Table].Employee) INNER JOIN ([Merchant Code Table] INNER JOIN [PCard Transactions] ON [Merchant Code Table].Value = [PCard Transactions].[Merchant Code]) ON [Employee Data Table].[Person Name] = [PCard Transactions].[Full Name]) ON [COA - Account Code Table].Account = [PCard Transactions].Account
WHERE ((([PCard Transactions].[Transaction Date]) Between ([PCard Transactions].[Transaction Date])=[Forms]![frmMain]![txtStart] And ([PCard Transactions].[Transaction Date])=[Forms]![frmMain]![txtEnd]))
ORDER BY [Employee Data Table].[Supervisor Name], [PCard Transactions].[Transaction Date];


The form is frmMain. The output from my code is just an Excel spreadsheet with the column headings and no data. I am not sure how to share those here.

 

[PHV]

I'd use this WHERE clause:

WHERE [PCard Transactions].[Transaction Date] Between [Forms]![frmMain]![txtStart] And [Forms]![frmMain]![txtEnd]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886