# Export Access query to Excel Template



## ronyace (Apr 4, 2004)

I have an easy question. I have already looked at several examples on the web and in this forum but don't seem to have the simple solution I am looking for.

I have a form in Access where the user will select parameters and then click a button to preview the report (which I have done already) or another button to export the query to excel. I am able to export the query to a new file using the output to or the transferspreadsheet, however, I want to export the data to an excel template file.

There will be only 1 query to export and I just want it to go into the template Sheet 1 or whatever I want to name it, and then have the user save the file as an .xls. This way my template file will always be available and the user can name it what they want. I don't want to link the file as I saw that solution in another thread.

If you have any suggestions please let me know. By the way, I am not very good with VBA so please go easy on me.

Thanks,
Ronyace


----------



## Rollin_Again (Sep 4, 2003)

Can you post your sample sample database and template file? Just remove any sensitive info before using the "manage attachments" button at the bottom of the posting window. It will be much easier to understand what you are trying to do if we can visualize your data format.

Regards,
Rollin


----------



## ronyace (Apr 4, 2004)

I am attaching the sample database. The form where the export function will go is the ReportGenerator. I have one button that will export a Reason Report. The test button is where I was trying to get the file to transfer to an excel template. The excel template is attached and is called Excel.xlt (I haven't added any calculations but the data should export to the RawData sheet). Hope this helps. Thanks.


----------



## rconverse (Sep 8, 2007)

If you already have an Excel template and you want to export your data into the template each time, you can use the following:


```
strExpFilePath = "G:\Groups\Nuclear Supply Chain\Maintenance Database\Excel Files\ReasonReport.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblName", strExpFilePath, -1
```
This will insert tblName into ReasonReport.xls and will be named "tblName" as opposed to Sheet1.

HTH
Roger


----------



## ronyace (Apr 4, 2004)

Will this work if my file is called ReasonReport.xlt (template file). And how can I get it to ask for me to Save As so I can rename the file and leave the template intact.


----------



## ronyace (Apr 4, 2004)

I tried the technique rconverse suggested. The data is placed on the template file and it is then saved as a template. I want to be able to save the file as an .xls file and rename it and leave the template file intact. Thanks for the suggestion though. Any ideas?


----------

