469,934 Members | 2,776 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

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!
Jul 31 '07 #1
4 10033
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.
Jul 31 '07 #2
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
Jul 31 '07 #3
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
Jul 31 '07 #4
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
Aug 23 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Anthony Cuttitta Jr. | last post: by
6 posts views Thread by Elena | last post: by
7 posts views Thread by Vanessa | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.