Export multiple queries to multiple sheets in excel

Sorry Kit

I know you are trying to explain your problem but I can't actually follow what you are asking. Try to keep in mind that we cannot see your database and try to explain your problem with logic rather than in terms of code.

Mary

Mary,

Cool, I can do that, here goes. . . .

. . . I would like to be able to export query results to Excel Workbook(s) with the Workbook’s name of a “Manager’s” name and distinct tabs of the “Supervisor’s” name reporting to that Manager.

One of the ways I found to do this, via sites like this, is to have a separate query for each Supervisor along with a “DoCmd.TransferSpreadsheet”, for each. For example if there are two Managers one with five Supervisors and the other with ten, that would be fifteen queries and fifteen transfers.

Since the names change frequently, it doesn’t make too much sense to always create a query (for the name updates) and change a macro or a procedure every time this happens and it happens a frequently.

I was hopping for a way of being able to create “self-writing” queries that could be called by a “procedure” or macro and then perform the transfer, without me because this task really is not for me.

Shorthand example:

Thank you, I had been having a bit of fun with it, but just have to go on to the next task shortly.


Kit

Hi Kit

This is not my area of speciality. I'm going to ask some of the other experts to have a look at it.

Mary

How do you specify which tab to export to? I don't see that parameter in the TransferSpreadsheet method. I see a range parameter but the help file says that is only for importing spreadsheets.

When you're not afraid to use some VBA code, you can make the command flexible by generating a query dynamically and executing the "dynamic" command.

This will require some way to distinguish between which supervisors and employees are to be selected. When you want all it's quite easy and straight forward.

Sample:
Hope this gives some idea how to achieve this. Let me know when you get stuck.

Nic;o)

I thought it was ManagersName.xls and in each spreadsheet are multiple tabs for each supervisor for that manager?

Just gave a simple start, will leave the excel automation part to you Rabbit <LOL>

Nic;o)

Just gave a simple start, will leave the excel automation part to you Rabbit <LOL>

Nic;o)

I actually don't think I'll have to do that part even.
It sounds like the PO has a way to transfer the spreadsheet to a specific tab in a workbook.

Mary,

Cool, I can do that, here goes. . . .

. . . I would like to be able to export query results to Excel Workbook(s) with the Workbook’s name of a “Manager’s” name and distinct tabs of the “Supervisor’s” name reporting to that Manager.

One of the ways I found to do this, via sites like this, is to have a separate query for each Supervisor along with a “DoCmd.TransferSpreadsheet”, for each. For example if there are two Managers one with five Supervisors and the other with ten, that would be fifteen queries and fifteen transfers.

Since the names change frequently, it doesn’t make too much sense to always create a query (for the name updates) and change a macro or a procedure every time this happens and it happens a frequently.

I was hopping for a way of being able to create “self-writing” queries that could be called by a “procedure” or macro and then perform the transfer, without me because this task really is not for me.

Shorthand example:

For manager
For supervisor
Create query
Run query
Transfer text
Next
Next

Thank you, I had been having a bit of fun with it, but just have to go on to the next task shortly.


Kit

In the scenario which you have described, you would have to:

1. Create 2 Workbooks, one for each Manager.
2. In the 1st Workbook, create 5 Worksheets representing each Supervisor.
3. In the 2nd Workbook, create 10 Worksheets representing each Supervisor.
4. Transfer the data for each Supervisor to the appropriate Worksheet in the corresponding Workbook (Manager for that Supervisor).
5. This process would involve Automation Code to create the Workbooks and associated Worksheets as well as VBA code to Open Recordsets, generate the proper data for each Manager/Supervisor scenario, then transfer this data to the appropriate Worksheet.
6. You stated earlier that the code you listed was over your head. The approach I am outlining would be much more complex and confusing. My advice would be to stay with a variation of nico's approach.

If you create a query, on the fly (in code), for each supervisor (Direct Report or DR), then this query can be transferred to the file after any other has already been transferred.

My example code is included here but you will have to arrange for the queries to be set up with your required names first.
I used acSpreadsheetTypeExcel9 as it is the latest available to me at work. Your default should do you fine.
Office automation is not required for this task.

If you create a query, on the fly (in code), for each supervisor (Direct Report or DR), then this query can be transferred to the file after any other has already been transferred.

My example code is included here but you will have to arrange for the queries to be set up with your required names first.

Expand|Select|Wrap|Line Numbers

1. Call DoCmd.TransferSpreadsheet(acExport,
2.                                acSpreadsheetTypeExcel9,
3.                                "tblReport",
4.                                "C:\Temp\TestXport.Xls",
5.                                TRUE)
6. Call DoCmd.TransferSpreadsheet(acExport,
7.                                acSpreadsheetTypeExcel9,
8.                                "qrySecurity",
9.                                "C:\Temp\TestXport.Xls",
10.                                TRUE)

I used acSpreadsheetTypeExcel9 as it is the latest available to me at work. Your default should do you fine.
Office automation is not required for this task.

As a follow-on to Nico's post, adding the excel automation to copy from the recordset to a specific sheet would work nicely. Here's an example.
J

Thank you all,

This is the kind of dialog I was looking for and received, thank you. I’ll roll up my sleeves, what is left of them and try it out. I’ll let you know, but it will be awhile. I just knew there had to be a way and that’s pretty cool!

Thanks again,

Kit

No problem Kit.
I hope it all works for you nicely :)

I have modified the above code to export my access table to one excel workbook with multiple tabs; by adding the range parameter to the transferspreadsheet method. My code is as follows:

______________________
I have attached this code to a macro; however, when I run the macro it prompts me for the worksheet names (A pop up shows up that says "enter parameter value" and displays the appropriate Parent value to be entered. One prompt for each distinct "parent". If I type in the parent names, the export works great, but I have like 20 distinct parents in my table and it's cumbersome to type them all out every time I run the macro. Can anyone analyze this code and let me know if there is a way to automate this step.

Thanks,
AccessJack

Typically, we try to discourage members from posting questions in somebody else's thread, but as this does fall very close to the original question I'll allow it.
(IE Stamp of approval.)

MODERATOR.

Your problem comes from using the same QueryDef (query name being the pertinent point) for each query. As it names the worksheet after the QueryDef name, this will only work correctly for the first one.

I know you typically don't allow someone to ask a question on someone else's thread, however I found this thread a few days ago and it was exactly like something I'm working on. I modified the code for my queries and fields, but I can't get it to work. I'm familiar with VBA, but I'm not an expert. The part about creating a "dummy query" is throwing me off. Am I supposed to create and actual query and replace "qryDummy" with my query name, or leave the code exactly as it is? Also, on the DoCmd.TransferSpreadsheet part of the code, where exactly am I supposed to tell it to name the workbook to the Sales Region ID and the worksheets to the corresponding Territory #?

I did create my own thread on this topic, but I didn't get any "real" help. http://bytes.com/forum/thread848541.html. "I am trying to export data to Excel from a query in Access. There is a unique "Sales Region ID" and various "Territory#'s" for each Region. I would like one excel workbook for each region, and a worksheet for each Territory # assigned to that region. "


Any assistance would be greatly appreciated. If you need more information, please let me know. I really need to get this process going....

Below is my code at it's current state. It's halting at the "DoCmd.TransferSpreadsheet" part.
------------------------------------------------------------------------------------------------------------------

I did create my own thread on this topic, but I didn't get any "real" help.

How can you expect further help when you haven't even had the manners to respond to what is there.

I tried to help but as I don't have mind-reading powers there is little I can do without feedback.

PS. I would be very careful about criticising the (voluntary) help here. Most of our members are civilised and take a fairly dim view of such comments.

I apologize if I've insulted anyone, that's definitely the last thing I meant to do. I am new to forums. You are completely right, I should not have said I didn't receive any "real" help without at least posting additional questions to my thread. I only replied to this thread because I was hoping whoever originally wrote the code in the thread above, could explain it to me. I've done my best at tweaking the code, but as a less experienced VBA programmer, I can't seem to figure it out on my own. I really appreciate the volunteers that help on this site. I've greatly benefited from reading a lot of the threads. Again, I apologize.

Response accepted in full.

I have put in a link to this thread from the other to enable you to continue in there. I would suggest that would be a better idea than continuing in here, but clearly any references between the two are now easily available.

Please bear in mind that we have many members who post with a large variety of experience. Experts will often not know immediately how to pitch any answers. Think of a someone dealing with pupils from an infants school to students at university. Until we get some communication going it's hard to know who we're dealing with.

...
I only replied to this thread because I was hoping whoever originally wrote the code in the thread above, could explain it to me. I've done my best at tweaking the code, but as a less experienced VBA programmer, I can't seem to figure it out on my own.
...

If you're referring to the code posted by Nico in post #6 then I'm sure he is still subscribed to the thread, and may offer some assistance if he knows it is he that you are referring to.

Let's see if he notices this.