I keep getting this:
Run-time error 3434:
Cannot expand named range.
Here is my (simple) code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SAP Query", _
I got this error, both using VBA and using the right-click, export to Excel method of TransferSpreadsheet.
I would be happy with either:
Maybe this is the problem: acSpreadsheetTypeExcel9
I think this is the correct version for 2007 thought. Can someone please confirm??
Checking in Acc03 I have the acSpreadsheetTypeExcel9 constant - it appears that Excel9 pertains to the pre-2007 formats. I'm no excel wizard by any means, and don't have Access 2007 handy, but Office 2003 is version 10 and Office 2007 is 11 (I think). Does Access VBA (2007) give a Excel11 option (and what's the .xlsb format?)
Access Wiki: http://www.utteraccess.com/wiki
The .xlsb format is a Binary File Format in Excel.
acSpreadsheetTypeExcel 9 is the Excel 2000 Format.
Try this instead:
DoCmd.TransferSpreadsheet acExport, , "SAP Query", "\\NYC\GB\ACCOUNTING\05_May\Reports\ReportingFile.xlsb"
That should do the trick.
Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
Thanks everyone. As it turns out, TransferSpreadsheets was disabled by default. I enable it by following these instructions:
Then, it was simply a matter of creating a Macro, and converting it to a VBA procedure.
I won’t post the final code, because it will be different for every user, but follow the steps I mentioned above, and it will work fine.