How to export Access query to specific excel worksheet each month

[bconner]

I have an Access Database with 3 reports that I would like to export to excel each month. I would like for all three reports to export to the same workbook just to different tabs. I used the select into below but it doesn't work after the first export...

into [excel 8.0;database=C:\Documents and Settings\bconner\Desktop\Access to Excel.xls].[Tri AR By Rej]

When using the above after the first time it says the the table already exists and then bombs.

Is there an alternative way of exporting an Access query to a specific worksheet within an excel workbook?

 

[namliam]

Look into the Docmd.OutputTo and the Docmd.TransferSpreadsheet in the access help.
Either or both should help you right along...

Good luck !

 

[bconner]

Is it possible to send the query results to a specific worksheet within a workbook?

When I use the docmd.OutputTo it won't let me export multiple queries to the same workbook it overrides each the last tab in the workbook with the new query.

I want to be able to send multiple access queries to the same excel workbook and have it create a tab for each within the workbook.

 

[namliam]

Do you NEED to use the OutputTo?? I am 100% sure TransferSpreadsheet will allow multiple tabs...

 

[MSAccessRookie]

Is it possible to send the query results to a specific worksheet within a workbook?

When I use the docmd.OutputTo it won't let me export multiple queries to the same workbook it overrides each the last tab in the workbook with the new query.

I want to be able to send multiple access queries to the same excel workbook and have it create a tab for each within the workbook.

Try using the DoCmd.TransferSpreadsheet command Instead of the DoCmd.OutputTo command. You will need to do a little research to determine all of the values you need.

[B]DoCmd.TransferSpreadsheet {[COLOR=blue]Direction of Transfer[/COLOR]}, _[/B]
[B]    {[COLOR=blue]SpreadSheet Type[/COLOR]}, {[COLOR=blue]Query to Select Data[/COLOR]}, _[/B]
[B]    {[COLOR=blue]Full Name of Excel WorkBook[/COLOR], [COLOR=blue]True[/COLOR], {[COLOR=blue]Name of WorkSheet[/COLOR]}[/B]

 

[bconner]

Thank you for your help I will try...

 

[MSAccessRookie]

Thank you for your help I will try...

Let us know how it turns out

 

[bconner]

I tried it and it worked like a Charm... Thank you all for helping me with this I really do appreciate you and this forum very much....
Below is what I tested

Function test()

DoCmd.TransferSpreadsheet acExport, , "90JJ AR by Rej", "C:\Documents and Settings\bconner\Desktop\Access to Excel.xls", True

DoCmd.TransferSpreadsheet acExport, , "90JJJ AR by FSC", "C:\Documents and Settings\bconner\Desktop\Access to Excel.xls", True
End Function

 

[kelvin78]

Hi namliam,

Is there any way I can keep the formatting and layout when I use TransferSpreadsheet to export queries to excel sheets?

 

[namliam]

No, well... not with transferspreadsheet alone...

You can open the created excel file in code and format the columns as you see fit...
Have a search for "Excel.Application" and you should find some samples.

 

[Snappy]

I used this and it worked great for a table or query in access but I need something exactly like this to export a report. I have formulas in this report and I can export it doing the outReportData command with no problems but I need 4 reports on specific worksheets in one workbook. The transferspreadsheet doesnt work because it is looking for the table or query name not a report name. This is what I currently do. But it overrides each report. Need one in each tab worksheet. Any help would be great

Private Sub Command626_Click()
Dim outReportData As String
Dim xlFileName As String
outReportData = "ODL EXPORT PRICING"
outReportData = "SUNBRELLA EXPORT PRICING"
outReportData = "SHARKSKIN EXPORT PRICING"
outReportData = "VISTA EXPORT PRICING"
xlFileName = "G:\SUE'S STUFF\WORKING DESKTOP\2013 WESTLAND\EXACT FIT\NEW PARTS\NEWPARTS.XLS"
DoCmd.OutputTo acOutputReport, outReportData, acFormatXLS, xlFileName

 

[mariyakeon]

hmm..
To export access data to excel sheet you can Use SysTools Access To Excel Conversion.
This software is designed with perfect technologies and algorithms to export access table to excel sheet in easy manner and support all version of Microsoft access.

 

[Moses76]

I tried it and it worked like a Charm... Thank you all for helping me with this I really do appreciate you and this forum very much....
Below is what I tested

Function test()

DoCmd.TransferSpreadsheet acExport, , "90JJ AR by Rej", "C:\Documents and Settings\bconner\Desktop\Access to Excel.xls", True

DoCmd.TransferSpreadsheet acExport, , "90JJJ AR by FSC", "C:\Documents and Settings\bconner\Desktop\Access to Excel.xls", True
End Function

In this case which one is the name of the individual tab / sheet . I am assuming Access to Excel.xls is the name of the workbook ?