Export table to excel using excel template

Hello - I would like to export a table or query (using Access VBA) to excel 2007, using an excel template (for headings, formatting, totals, etc).

Is this possible?

Post your answer or comment

comments powered by Disqus

I would like to export some tables to Excel using OutputTo. When I open the Excel file with the exported table, I get a message "File error. Some number formats may have been lost."
Please help.


Searching for a way to export a table to Excel file using VBA...
As I had this bookmark problem (see thread in general ) I don't wanna take any chances so I'm gonna backup the data every hour or so...

Please help

Can anyone please help.

I am trying to create a form button that will allow a user to export a table to a new excel spreadsheet.
When the button is clicked I need the explorer window to open in the same directory the application is in, and alow the user to select a new name ,and if need, go to a different directory for the exported file.


I would like to know if there is a way to make a button on a form to allow you to export a table to excel. I want a user to every month hit this button and send it to a default folder so that we can use this file in other ways. Thanks

hi guys,

is there any way i can create a button on a form, to export a whole table to excel and save it?

cuz this database will be used in runtime version, and there's no option to export data like there is in normal access..

i know that i could link an excel file to access and just make auto update, so that it constantly updates its values with the table, and then just go on this excel file and click save as; but could i create a new excel file from a wanted table, and then save it?

i hope some of this makes sense..

I know how to output my tables to excel using a macro but I need some code for a button that will ask me what table I want to output.

I posted this on modules & VBA but got no replies.
I can export tables to xl using DoCmd.TransferSpread function but I wanted to implement this as...
when user pressess the command button on a form, ExportFileDialogBox has to be brought up so that user can choose the xl file name and location of his choice. How can I implement the ExportFileDialogBox ? I learnt that I can get the Active X control from VB and incorporate in Access toolbox..but i have no idea as how to do.. can anyone please help me. try to explain steps as i am new to access programming.
Any help is appreciated.

dear.. guys.
need help for my problem..I have query need to be export to excel using excel templated.. thanks a lot for advice...

Hello, I was hoping someone could help me with exporting a pivot table created in Access to export and save to an excel workbook.

The current code I have in place works fine, however, I have to specify the location and "saveas" type each time I want to export the pivot table to excel:

	DoCmd.OpenQuery "User_Authorization", acViewPivotTable
DoCmd.RunCommand acCmdPivotTableExportToExcel
DoCmd.Close acQuery, "User_Authorization"

I've used the docmd.transferspreadsheet method in the past and don't know if there is another method for the pivot table?

Is there an easier way of doing this? I would like to export the pivot table(s) because there is about 20 I have to do for each autorization group. I'd like to make it faster where I can loop through the records.

Any help/suggestions would greatly be appreciated.

Hi ,

I have a linked ORACLE table in Access 2000 with a date "mydate" of type "Date/time"

I have built a simple query which selects mydate.
the query output shows as mydate as e.g. "6/23/1997"

1. If I export this query to Excel using "output to" in a macro
the same date is displayed as "23-jun-97"

Although if i try to change it it reverts to the format "6/23/1997"
and then goes back to displaying "23-jun-97"

2. I tried using Format([mydate], "mm/dd/yyyy")
Then if i export it to Excel, i cannot sort or change the date format as it is now a text string instead of a date

3. I also tried CDate(Format([mydate], "mm/dd/yyyy"))
but it yields the same results as option 1.

4. I created a excel template ( which i really don't want to do 'cept as a last option) myfile.xlt and used it in the options for outputTo .
It does not seem to have any effect on the data.

Really need some help right now !


I've made a macro that successfully exports a table to an excel spreadsheet. I'm using the TransferSpreadsheet function to do this, but the problem is, I need the table to start further below the sheet at like A10, not A1. The range feature doesn't apparently work with exporting.

Is there anything I can do to get the same affect as this macro, except change the starting point from A1 to A10?

Does anyone know how to export an Access pivot table to Excel. I've used the docmd.transferspreadsheet in the past and it works great for raw query data, however I cannot figure out how to export the pivot table view in a query. Thanks!

The problem I'm having is this ...

When we are exporting the pivot table to Excel, it encounters "load errors". I did some research on the error message and it is basically telling me that the number formats used in the pivot table are not supported by Excel. I need to be using number formats that are supported by Excel in my database tables / pivot tables.

Now when my Boss tries it, however, it works fine. The only difference is that he is using Microsoft Office 2010 and the problem is being encountered on Microsoft Office 2003.

I was wondering if maybe Excel 2003 simply is out of date for the formats being used in the Access database / Pivot Tables, or if I need to edit the formats of the data somewhere in the database tables.

But, the number formats don't seem to be irregular. Just your basic decimal numbers and alpha-numeric strings...

Thanks for any help on this.


I know this is probably an easy question to answer but I have not been able to find it yet.

I'm trying to export a query to excel but I would like to have the Save As window come up so that the user can choose where they are saving it and for the file name I would like to use the resault of a string set by a combo box.

Here's what I have so far and it works to save the excel file and use the combo box selection as a name.
Quote: Function QueryExportToExcel()
On Error GoTo Error_Handler
Dim stQueryToExport
stQueryToExport = [Forms]!frmAvailQueries.cboAvailableQueries
DoCmd.OutputTo acOutputQuery, stQueryToExport, acFormatXLS, "C:" & stQueryToExport & ".xls", , , , acExportQualityPrint
If err.Number = 94 Then
MsgBox "Please remember to select something from the drop down list first."
Exit Function
End If
End Function Thank you

hi all...

i made a database that keeps records in access table. however, on that form there is a button which have this code:

Private Sub Command50_Click()
DoCmd.OpenTable "TxPerlis"
SendKeys "%(TLA)", False
SendKeys "Yes", False
End Sub

the result is, when a user clicks this button, the access table will be automatically exported to excel table, and thus a new xls file is created, which i'm happy to say is what i want. however, when i go to "startup" settings and untick all field, hiding toolbar and all, this button somehow don't export the table to excel anymore. instead, it just opens access table, which i'm not happy with. why is this happening?

Hi all
the problem i am facing is that the table exported by ACCESS to EXCEL is without any kind of formatting, bold text italics etc. , is there any way i can make access export the excel file with bold column headings and the cells having solid borders(basically any formating i wish to give).
Each time i export the table i have to open excel and format the spreadsheet giving headings highlighting stuff and all and it is very troublesome so i did make a macro in EXCEL for it but i still need to open the file in Excel and run the macro. is there some way to run that macro during the export process so that the user just gets the file in the format he/she wishes.
thanks for you help in advanc

This command works well for exporting an access table to excel.
I cannot find the correct syntax to export an access table to a dbf file.

Charllie Crimmel

Access to Xcel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exporthist", "c:exporthist.xls"

Access to DBF
DoCmd.TransferDatabase acExport, acDatabaseTypeDBF, "exporthist", "c:exporthist.dbf"

I have an Access front end to a SQL Server db one function of which is to provide an export to Excel using a query and the OutputTo method of DoCmd. This works perfectly in my development environment (WinXP, Access2003) but, when I implement it on my clients computers on the same corporate network but with the Access2003 replaced by Access2003 Runtime the resulting file is always empty.
Anyone got any ideas? I thought that it might be something missing from the Office installation on the client computers but I'm by no means sure.

Found it, it does work but they had some blank columns in the data which made the query fail to return any lines!!!

Hi all,

I'm having some issues when trying to export a table to excel. Basically when I export the data into a excel file (or csv) some of the data doesn't come through. The column names come through but no data is contained under them.

Here's a bit more infomation:
I have a table with 44 columns. When I export this table to an Excel sheet 3 of the columns don't contain any data. However I can see all the data (including the missing ones under the 3 columns) when viewing the table in Access itself. As far as I know there is no security/permissions features on the tables.

I need to export the tables as I need to create a Data Map for importing them back into MS CRM. I am fairly new to Access so any help will be greatly appreciated.


Hi there Everyone!

Please try to help me as I am very desperate with my problem. I have an access database with a few tables and one query. In the query I have one calculated field (the last one). When I run the query everything runs smoothly, I get the desired results in the query. My problem is with the export to Excel. When I press the export to excel button (external data menu) I get an error saying: Syntax error (missing operator) in query expression (NamesID).
I really have no clue what to do about it! Please try to help me if you can! I am clueless and very desperate about it!

The zipped access file is attached. Please try to run the query and then export it to excel and you should see my problem.

Thank you in advance and have a nice weekend!

P.S.: I am using Access 2007 and Excel 2007.

What would the VBA command line look like to export a report to Excel using a command button ?

I created a pivot table in Access 2007 and there is a button under the Design tab to automatically export your pivot table to Excel. However, in addition to the pivot table, it's exporting all the raw data from the main table. My main table contains over 1 million records. I'd just like the pivot table and the filtered data to export to Excel. Is there a way to do this? My project is due in 15 days. Need to know which direction to go soon. Thanks!

Hi everyone.

Please help an access n00b.

I have created an access database to automate our trademe retail feed. (New Zealands alternative to ebay - retail feed updates our listings from a csv dump)

My database pulls data out of our inventory software and holds tables with auction/global data.

What I need to do is create a button in access to copy said tables to excel sheets (overwriting existing data), then save a separate sheet as CSV in the format of "retailfeed_DDMMYYYY_HHMM.csv.

I have tried and succeded in coping data to excel sheet using script like:

Code: Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Shipping", "C:temptestBook1.xls", False, "Shipping" But I am now stuck in how to save an output sheet to csv (after my workings).

I can't find/figure out how to do it from access. I have tried creating a macro to do it in excel but keeps giving me errors.

I have also tried it in reverse... Using the access button to run a macro in excel to do the import then save but then I just get errors the database is open.

Any help will be really appreciated. I have only spent a few days ripping my hair out in google!!!


I ahev a table that has all my final data in it for several Business Units. I need to export this to excel so that a separate spreadsheet is output for each BU and the name of the spreadsheet contains the name of the BU.

Can anyone give an example of how I could do this please?

Cheers maz

Not finding an answer? Try a Google search.