dana7476
asked on
Export queries to specific tab of excel file
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?
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?
ASKER
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.Sa veAs ("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?
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").
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?
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try to assign the file and sheet names to a text box
or an inputbox
objexcel.Sheets("Sheet1").