Export queries to specific tab of excel file

dana7476
dana7476 used Ask the Experts™
on
Hi,

I would like to export (transfer, copy or the like) multiple access queries to excel.  All the queries should be exported to the same file but on different tabs of the spreadsheets.  The names of the tabs should be dynamically created based on the name of the query in access.  I am by no means an expert, but I can create a module to export the queries and create the tabs of a specific sheet, but it is hardcoded, and I would prefer to only have to enter in the name of the file (FILE.xls) and have the queries exported and the sheets named dynamically.  Does anyone have any suggestions of how I can do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:


try to assign the file and sheet names to a text box
or an inputbox

objexcel.Sheets("Sheet1").Name = sTextBoxText  objexcel.ActiveWorkbook.SaveAs ("C:\default path" & sTextBoxText & ".xls")

Author

Commented:
Keaton,

Thanks for your response.  What I would like to do is dynamically create the worksheet name based on the name of the query.  So for example, in your code


objexcel.Sheets("Sheet1").Name = sTextBoxText  objexcel.ActiveWorkbook.SaveAs ("C:\default path" & sTextBoxText & ".xls")

Sheet 1 would not be defined, rather it would read the query name that has already been assigned in access and use this name as the tab name in excel.  Any other suggestions?

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Per recommendation, points refunded and question closed.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial