Export Queries to Same Excel File, but Different WorkSheets

Hey everybody.

I have Access set up using VBA to run a bunch of queries automatically, and then export the results into Excel. The only problem is, I want each query to be exported to the same Excel File, but different worksheet.

What happens now is that a new Excel file is opened for each query (with the same name, as read-only) and then the data is imported into the correct worksheet number. I just need to figure out a way to have it all pasted into the same file!

The code that is responsible for this part is here I believe:

Expand|Select|Wrap|Line Numbers

1. WorksheetToPlace = "Sheet" & WorksheetNumber
2.     Set App = CreateObject("Excel.Application") 'This opens excel application windows
3.     App.Visible = True
4.     Set File = App.Workbooks.Open("C:\Sessions\NumberOfSessions.xls") 'This opens your particular excel file (or workbook)
5.     Set ws = File.Worksheets(WorksheetToPlace) 'This opens your sheet that you want to write to
6.     ws.Activate

I am using other people's code to make my application, and just modifying components. Using Access/Excel 2003

Thanks for your time!

Go to macro in Access.Create a new macro. Click on the drop down for "TransferSpreadsheets" under Actions. Fill in the information like: Transfer Type, Spreadsheet Type, query/ Table name, etc. You can export all of your queries into one excel worksheet.

I don't see how you can do it with out VBA code.

The post above I do not belive will work. I could be wrong. I hope it is right and it will solve all you problems.
I think you can only export one table or query at a time and you will still end up with Multiple workbooks.

------

I don't know if this is the route you want to go, but it may help.

I run the SQL from Excel and update muiltple formated worksheets daily.

You could try this:
1. In Excel go to the Data Menu / Import External Data / Import data.

2. Find you DB, Connect to it.

3. You can select you query right there from the list.

OR (very optional)
if after you select one (table or query) you can "Edit Query"
under "Command Text" you can Select SQL instead of TABLE and just paste youe SQL code there.

NOTE: you might want ot check out the "Properties" button to see options. Mostly post import formating.

Either way it ends up in Excel and you can format it and make it look nice.

In the future you can right click on the data and select "Refresh Data" (also under the Data Menu).

I added a Refresh All button to my Menu.
(Tool/Custonize/ In the Command Box select Data and Scroll down you will see it and drag it to you toolbar)

I don't know I like this way. You can format how ever you want and it keeps em from having hundreds of queries in my DB.

Good Luck

-- Boxcar

I forgot you are using VBA to run the queries automatically.

So I doubt my way is how you want to go. You may want to consider it.

Sorry :(

-- Box

Hi Chris Gilpin,

Have you found a way to solve your initial problem?
i.e. Exporting a bunch of queries into 1 Excel file with a "tab" for each query?

I have tried the solution that "eskelies" offered and have found that there are 2 issues:

- you require the excel spreadsheet to exist already
- the file just appends the extra tabs

Please advise.

regards
Jane