Transfer xls spreadsheet to Office 10

[Skinny1434]

Hi I am having trouble, since I couldn't transfer a query to a macro enabled excel sheet I backed it to a .xls. Now I am having an error during my Do.cmd.Transferspreadsheet. Does anyone know how to to code the acSpreadsheetTypeExcel in order to transfer a query to an xls document in Office 10. Thank you.

 

[Joe4]

I am using Excel 2007, but the method I am providing you below you work the same.

Create a new Macro, and select the "Transferspreadsheet" Action. Fill out all the arguments, but make sure for the "Spreadsheet Type" argument, you select the option for ".xls" files, which would be the "Microsoft Excel 97 - Excel 2003 Workbook". Now save your Macro.

Highlight the Macro in the Object Explorer, go to the "Database Tools" tab, and click on "Convert Macros to Visual Basic" button on the "Macro" ribbon, and select "Convert".

If you look at the VBA code it created, it should give you the VBA code you need. On mine it looks like:

DoCmd.TransferSpreadsheet acExport, 8,...

It is that code "8" which tells it which Excel format to use.

You should perform these steps on your computer, just in case Excel 2010 uses a different code.

 

[Skinny1434]

Thank you for the reply, I dont see the action for transferspreadsheet in the action catalog. Btw I am using Access 2007 as well.

 

[Joe4]

I dont see the action for transferspreadsheet in the action catalog

From the "Show/Hide" ribbon, click "Show All Actions".