469,921 Members | 2,198 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Export multiple queries to multiple sheets in excel

4
I'm new here and will admit right off the bat, most of this is above my head, but I like Access and learning the ticks of the trade so to speak. Here is one and I’m sure there is a way, but I’m stuck.

Below is triggered when a button is clicked in a form:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet 1, 8, "Employee", "C:\Manager.xls", True
The “Employee” and “Manager” are replaced with the actual names, (A – X). I would like to avoid the maintenance of having to update the form’s procedure, which exports the results of a query (Employee) to an Excel Workbook (Manager) with a distinct worksheet (Employee) and then having to create a query each time there is a change with the “Employee”.

I have been able to write a simple query that will create the cmd above, but I don’t think a cut and paste will work anytime there is a change of names.

Thank you in advance,

Kit
Jun 19 '07 #1
21 26673
MMcCarthy
14,534 Expert Mod 8TB
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
Jun 20 '07 #2
Kit K
4
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:

Expand|Select|Wrap|Line Numbers
  1. For manager
  2.               For supervisor
  3.                        Create query 
  4.                        Run query
  5.                       Transfer text
  6.               Next
  7. 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
Jun 20 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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
Jun 20 '07 #4
Rabbit
12,516 Expert Mod 8TB
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.
Jun 20 '07 #5
nico5038
3,080 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. function fncExcelWorkbooks
  2. dim rsManager as DAO.recordset
  3. dim rsSupervisor as DAO.recordset
  4. dim qd as DAO.Querydef
  5. ' create a dummy query for dynamic use e.g. named qryDummy
  6. set qd = currentdb.querydefs("qryDummy")
  7.  
  8. ' get the manager
  9. set rsManager = currentdb.openrecordset("select distinct manager from tblX")
  10. if rsManager.EOF and rsManager.BOF then
  11.   ' no managers found action
  12.   exit function
  13. endif
  14. ' for each manager we loop for the supervisors:
  15. while not rsManager.EOF
  16.    set rsSupervisor= currentdb.openrecordset("select distinct Supervisor from tblX where ManagerID=" & rsManager!ManagerID)
  17.    if rsSupervisor.EOF and rsSupervisor.BOF then
  18.      ' no supervisor found action = continue
  19.   while not rsSupervisor.eof
  20.      qd.SQL = "select your fields from tblX where ManagerID=" & rsManager!ManagerID & " AND SupervisorID=" & rsSupervisor!SupervisorID)
  21.     ' here the filled query is used for writing to an excelsheet named to manager and supervisor
  22.      DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "C:\" & rsManager!ManagerID &  "-" & rsSupervisor!SupervisorID & ".xls", True
  23.    endif
  24.      rsSupervisor.movenext
  25.   wend
  26.   rsManager.movenext
  27. wend
  28. set rsManager = Nothing
  29. set rsSupervisor = Nothing
  30. end function
  31.  
Hope this gives some idea how to achieve this. Let me know when you get stuck.

Nic;o)
Jun 20 '07 #6
Rabbit
12,516 Expert Mod 8TB
I thought it was ManagersName.xls and in each spreadsheet are multiple tabs for each supervisor for that manager?
Jun 20 '07 #7
nico5038
3,080 Expert 2GB
Just gave a simple start, will leave the excel automation part to you Rabbit <LOL>

Nic;o)
Jun 20 '07 #8
Rabbit
12,516 Expert Mod 8TB
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.
Jun 20 '07 #9
ADezii
8,800 Expert 8TB
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.
Jun 20 '07 #10
NeoPa
32,231 Expert Mod 16PB
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.
Jun 21 '07 #11
JConsulting
603 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1. Function Export2Excel()
  2. Dim objXLWb As Object
  3. Dim objXLSheet As Object
  4. Dim objXLCell As Object
  5. Dim rs As DAO.Recordset
  6. 'Make DAO Recordset
  7. Set rs = CurrentDb.OpenRecordset("qryLIST_EXPORT")
  8. 'Start Excel
  9. Set objXLApp = CreateObject("Excel.Application")
  10. objXLApp.Visible = True
  11. 'Open Worksheet & Activate
  12. Set objXLWb = objXLApp.Workbooks.Add
  13. Set objXLSheet = objXLWb.Worksheets(1)
  14. 'CopyRecordset to Excel File
  15. objXLSheet.Range("A1").CopyFromRecordset rs '<---start at any range you want here
  16. 'Empty Recordset
  17. Set rs = Nothing
  18. End Function
  19.  
Jun 22 '07 #12
Kit K
4
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
Jun 22 '07 #13
NeoPa
32,231 Expert Mod 16PB
No problem Kit.
I hope it all works for you nicely :)
Jun 22 '07 #14
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:

Expand|Select|Wrap|Line Numbers
  1. Function fncExcelWorkbooks()
  2. Dim rsParentLvl3 As DAO.Recordset
  3. Dim qd As DAO.QueryDef
  4. ' create a dummy query for dynamic use e.g. named qryDummy
  5. Set qd = CurrentDb.QueryDefs("qryDummy")
  6. ' get the Parent
  7. Set rsParentLvl3 = CurrentDb.OpenRecordset("select distinct myField from myTbl")
  8. If rsParentLvl3.EOF And rsParentLvl3.BOF Then
  9.   ' no parents found action
  10.   Exit Function
  11. End If
  12. ' for each parent we loop:
  13. While Not rsParentLvl3.EOF
  14.   qd.SQL = "select * from myTbl where ParentLvl3=" & rsParentLvl3!ParentLvl3
  15.   DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "D:\ParentExport.xls", True, rsParentLvl3!ParentLvl3
  16.  
  17.   rsParentLvl3.MoveNext
  18. Wend
  19. Set rsParentLvl3 = Nothing
  20. End Function
______________________
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
Jun 26 '07 #15
NeoPa
32,231 Expert Mod 16PB
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.
Jun 28 '07 #16
NeoPa
32,231 Expert Mod 16PB
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.
Jun 28 '07 #17
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.
------------------------------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Function GenerateMedicareFacilityByRegionExcelReport()
  2.  
  3. Dim rsRegion As DAO.Recordset
  4. Dim rsTerritory_Number As DAO.Recordset
  5. Dim qd As DAO.QueryDef
  6.  
  7. 'Create a dummy query for dynamic use
  8. Set qd = CurrentDb.QueryDefs("qryDummy")
  9.  
  10. 'Get the Region
  11. Set rsRegion = CurrentDb.OpenRecordset("Select Distinct [Sales Region ID] From tblSalesRegions")
  12.  
  13. If rsRegion.EOF And rsRegion.BOF Then
  14.   'No Regions found action
  15.   Exit Function
  16. End If
  17.  
  18. 'For each Region we loop for the Territories:
  19. While Not rsRegion.EOF
  20.  
  21.    Set rsTerritory_Number = CurrentDb.OpenRecordset("Select Distinct [Territory#]" & "From tblTerritories " & "Where [Sales Region ID]='" & rsRegion![Sales Region ID] & "';")
  22.  
  23.    If rsTerritory_Number.EOF And rsTerritory_Number.BOF Then
  24.         'no Territory# found action = continue
  25.    End If
  26.  
  27.    While Not rsTerritory_Number.EOF
  28.         qd.SQL = "Select * From qryMedicareUnits_by_Facility_Patient_And_Region Where [Sales Region ID]=" & rsRegion![Sales Region ID] & " AND [Territory#]=" & rsTerritory_Number![Territory#]
  29.         'Here the filled query is used for writing to an excel worksheet named to Region and Territory#
  30.         DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "C:\" & rsRegion![Sales Region ID] & "-" & rsTerritory_Number!Territory# & ".xls", True
  31.  
  32.      rsTerritory_Number.MoveNext
  33.  
  34.   Wend
  35.  
  36.      rsRegion.MoveNext
  37.  
  38. Wend
  39.  
  40. Set rsRegion = Nothing
  41. Set rsTerritory_Number = Nothing
  42.  
  43. End Function
Oct 27 '08 #18
NeoPa
32,231 Expert Mod 16PB
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.
Oct 28 '08 #19
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.
Oct 28 '08 #20
NeoPa
32,231 Expert Mod 16PB
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.
Oct 28 '08 #21
NeoPa
32,231 Expert Mod 16PB
...
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.
Oct 28 '08 #22

Post your reply

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

Similar topics

3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.