Export multiple queries to multiple sheets in excel

[Kit K]

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:

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

[MMcCarthy]

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

[Kit K]

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.Transfer Spreadsheet”, 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

[MMcCarthy]

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

[Rabbit]

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

[nico5038]

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:

function fncExcelWorkbooks
dim rsManager as DAO.recordset
dim rsSupervisor as DAO.recordset
dim qd as DAO.Querydef
' create a dummy query for dynamic use e.g. named qryDummy
set qd = currentdb.querydefs("qryDummy")

' get the manager
set rsManager = currentdb.openrecordset("select distinct manager from tblX")
if rsManager.EOF and rsManager.BOF then
  ' no managers found action
  exit function
endif
' for each manager we loop for the supervisors:
while not rsManager.EOF
   set rsSupervisor= currentdb.openrecordset("select distinct Supervisor from tblX where ManagerID=" & rsManager!ManagerID)
   if rsSupervisor.EOF and rsSupervisor.BOF then
     ' no supervisor found action = continue
  while not rsSupervisor.eof
     qd.SQL = "select your fields from tblX where ManagerID=" & rsManager!ManagerID & " AND SupervisorID=" & rsSupervisor!SupervisorID)
    ' here the filled query is used for writing to an excelsheet named to manager and supervisor
     DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "C:\" & rsManager!ManagerID &  "-" & rsSupervisor!SupervisorID & ".xls", True
   endif
     rsSupervisor.movenext
  wend
  rsManager.movenext
wend
set rsManager = Nothing
set rsSupervisor = Nothing
end function

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

Nic;o)

[Rabbit]

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

[nico5038]

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

Nic;o)

[Rabbit]

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.

[ADezii]

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.Transfer Spreadsheet”, 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.

[NeoPa]

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.

Call DoCmd.TransferSpreadsheet(acExport,
                               acSpreadsheetTypeExcel9,
                               "tblReport",
                               "C:\Temp\TestXport.Xls",
                               TRUE)
Call DoCmd.TransferSpreadsheet(acExport,
                               acSpreadsheetTypeExcel9,
                               "qrySecurity",
                               "C:\Temp\TestXport.Xls",
                               TRUE)

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

[JConsulting]

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.

Call DoCmd.TransferSpreadsheet(acExport,
                               acSpreadsheetTypeExcel9,
                               "tblReport",
                               "C:\Temp\TestXport.Xls",
                               TRUE)
Call DoCmd.TransferSpreadsheet(acExport,
                               acSpreadsheetTypeExcel9,
                               "qrySecurity",
                               "C:\Temp\TestXport.Xls",
                               TRUE)

I used acSpreadsheetTy peExcel9 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

Function Export2Excel()
Dim objXLWb As Object
Dim objXLSheet As Object
Dim objXLCell As Object
Dim rs As DAO.Recordset
'Make DAO Recordset
Set rs = CurrentDb.OpenRecordset("qryLIST_EXPORT")
'Start Excel
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
'Open Worksheet & Activate
Set objXLWb = objXLApp.Workbooks.Add
Set objXLSheet = objXLWb.Worksheets(1)
'CopyRecordset to Excel File
objXLSheet.Range("A1").CopyFromRecordset rs '<---start at any range you want here
'Empty Recordset
Set rs = Nothing
End Function

[Kit K]

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

[NeoPa]

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

[accessjack]

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 transferspreads heet method. My code is as follows:

Function fncExcelWorkbooks()
Dim rsParentLvl3 As DAO.Recordset
Dim qd As DAO.QueryDef
' create a dummy query for dynamic use e.g. named qryDummy
Set qd = CurrentDb.QueryDefs("qryDummy")
' get the Parent
Set rsParentLvl3 = CurrentDb.OpenRecordset("select distinct myField from myTbl")
If rsParentLvl3.EOF And rsParentLvl3.BOF Then
  ' no parents found action
  Exit Function
End If
' for each parent we loop:
While Not rsParentLvl3.EOF
  qd.SQL = "select * from myTbl where ParentLvl3=" & rsParentLvl3!ParentLvl3
  DoCmd.TransferSpreadsheet 1, 8, "qryDummy", "D:\ParentExport.xls", True, rsParentLvl3!ParentLvl3
  
  rsParentLvl3.MoveNext
Wend
Set rsParentLvl3 = Nothing
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