DoCmd.TransferSpreadSheet behaves differently in 2003 vs. 2007

ions

Access User
Local time
Today, 03:26
Joined
May 23, 2004
Messages
823
Dear Access Expert,

When I run the below code in Access 2003 all works great. The same Docmd line of code produces an error in Access 2007.

The error produced in Access 2007 is captured in the attached gif file.

According to the error I have to set the parameters of the query. Do I Have to create a query def and set the parameters? If so how do I make this work since the 3rd argument for TransferSpreadSheet is a String type.

Thank you for your advice.

Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryName, strFilePath & "\" & strFileName, True
 

Attachments

  • Error only in Access 2007.gif
    Error only in Access 2007.gif
    7.6 KB · Views: 344
I just read this

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

It recommends to use DoCmd.OutputTo acQuery, sDocName, acFormatXLS but this requires the user to provide a file name.

I have to create the filename for the user and don't want this dialog to pop up.

Also could the problem be the acSpreadsheetTypeExcel9 argument? Perhaps I should change this to 11 for Excel 2003?

Thanks.
 
Attached are the options for acSpreadSheetType.

Can't go up to 11. Version 9 is that last one.
 

Attachments

  • acSpreadSheetType.gif
    acSpreadSheetType.gif
    5.4 KB · Views: 567
I learned that if I use UDF in the query criteria instead of Forms![myForm]![mycontrol].value syntax, the DoCmd.transferSpreadSheet function works.

Also I probably dont' have the latest Access 2007 patches since I don't develop in 2007. Where can I find the latest patches and Hotfixes for 2007? Perhaps not having the latest 2007 patch may be the culprit.

I would much rather use the Forms![myForm]![mycontrol].value syntax than UDF because the logic is right in the Query Grid.

Thank you
 
Last edited:
FWIW, I've never used DoCmd.TransferToSpreadsheet. I've just used Excel's CopyFromRecordset and performed the formatting afterward via automation.
 
Banana

If possible can you send me your function that dumps the recordset into Excel.

Are you using early binding or Late binding? I need this to work with both Office 2003 and Office 2007.

Thank you.
 
Ions-

It works for me in Northwind 2007. First, I created this query:

OrderSummaryParam:
PARAMETERS [Enter Order Date:] DateTime;
SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.[Shipped Date], [Order Price Totals].[Price Total] AS [Sub Total], Orders.[Shipping Fee], Orders.Taxes, [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], Orders.[Ship Name], Orders.[Ship Address], Orders.[Paid Date], [Orders Status].[status name] AS Status
FROM [Orders Status] RIGHT JOIN (Orders LEFT JOIN [Order Price Totals] ON Orders.[Order ID] = [Order Price Totals].OrderID) ON [Orders Status].[Status ID] = Orders.[Status ID]
WHERE (((Orders.[Order Date])=[Enter Order Date:]))
ORDER BY Orders.[Order ID] DESC;

When I run this from the Immediate Window:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "OrderSummaryParam", "C:\Documents And Settings\Johnv\My Documents\ZZspreadsheettest.xls"

.. it prompts me for the parameter as expected.

Go here to get the latest 2007 service pack:

http://www.microsoft.com/downloads/...18-79EA-46C6-8A81-9DB49B4AB6E5&displaylang=en

Also check to see that you explicitly declared the parameter.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
 
I will try your suggestions. Thank you John.
 
Explicitly declaring the paramaters in the Query Definition solved the problem in Access 2007 using the DoCmd. TransferSpreadsheet Function.

Thank you for everyone's suggestions.

Cheers.
 
Although the PARAMETERS statement is "optional," there are cases where you *must* use it. Crosstab queries is another example where it's required. I always declare my parameters to avoid this problem and to take advantage of data type validation. For example, if you declare a parameter DateTime, the user *must* enter something that's recognizable as a date or time value.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
 
Looks like John gave you a solution. Still:

Banana

If possible can you send me your function that dumps the recordset into Excel.

Are you using early binding or Late binding? I need this to work with both Office 2003 and Office 2007.

Thank you.

It's basically this:

Code:
[ExcelWorkSheet].[Cell].CopyFromRecordset([Name of Recordset variable])

Where the cell (it can be a range, also) is the upper left where you want to paste the recordset into. A actual code:

Code:
ws.Cells("A2").CopyFromRecordset(rs)

The behavior is almost as if you copied & pasted from Access datasheet into the Excel worksheet, hence me doing the formatting after the fact or using multiple CopyFromRecordset if there's more than one set of data to put into the worksheet.

More info.

It works with either bindings; the only difference would be that the "ws" variable would be a Excel.Worksheet in early, Object in late.
 
Thanks for the explanations Banada. Appreciate it.
 

Users who are viewing this thread

Back
Top Bottom