# Export Access 2003 to specific excel worksheet



## mdanehl (Apr 16, 2007)

Hello. I am trying to export several Access 2003 select queries to Excel 2003 using an Access macro. However, I have tried using the macro command "TransferSpreadsheet" and am receiving the error "Cannot update. Database or object is read-only". Per the user/group settings any user has read/write access in the database. I also tried the macro command "OutputTo", however I can determine who to output to a specific worksheet in the workbook.

Any help would be GREATLY appreciated. Thanks in advance.


----------



## slurpee55 (Oct 20, 2004)

Please post what your macro consists of. This may be something as simple as an unrecognized extension, or something else altogether.


----------



## mdanehl (Apr 16, 2007)

Thanks for the response. As I am testing, the only thing I have in my macro is the export. Action: "TransferSpreadsheet" with the following arguments:
Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8 -10
Table Name: [table name] - DOES THIS HAVE TO BE A TABLE OR CAN I USE A QUERY?
File Name: [full path of file] reconciliation.xlt (set up an excel template workbook to use for first transfer)
Has Field Names: Yes
Range: [named range in my workbook - first tried entering the specific tab in the workbook, but that didn't work either]


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

Yes, you can use a query or a table. You should have the Excel file closed when doing this. Also, you cannot specify the range to which it enters. If there is already a sheet name with what you specify, it will be over written. You would have to manipulate the data after the transfer if you wanted it in a specific location. Either that or have one sheet where you can overwrite it and just use that name.

Code for this looks like ..


```
DoCmd.TransferSpreadsheet acExport, , "qryNameHere", "C:\YourFullPathHere\Book1.xls", False, "NewSheetName"
```
Obviously change the details to what you want. You will also probably need permissions to use this table/query from within Access. Ensure you have the rights and run the command.


----------



## OBP (Mar 8, 2005)

mdanehl, can I ask why you wish to use VBA to export the data to an Excel Worksheet?
If you want the data in Excel then it is much better to "live Link" an Excel Worksheet to the Access Query, as the Excel data will remain as up to date as the Access database.


----------



## mdanehl (Apr 16, 2007)

Zack B - thanks for the code!

OBP - when you say "live link" are you referring to the Excel add-in one could purchase?


----------



## slurpee55 (Oct 20, 2004)

Go here: http://office.microsoft.com/en-us/excel/HA100963001033.aspx and look for Connect to Access data from Excel


----------

