Export from multiple Access Queries to Multiple Workbooks and Worksheets

[TSchaff]

I have a DB that culminates into 4 export queries. I would like to create a macro to export the 4 queries into one workbook per Business Unit. There are 10+ Business Units. A single Business Unit workbook would contain the output of from each of the export queries for the respective Business Unit.

Help!

 

[ranman256]

create a form ,on it is a listbox of all business units.
the code will cycle thru the list, and export the query based on the bus.unit

the query uses the listbox, lstUnit , to pull data, ie:
select * from table where [busUnit]=forms!myForm!lstUnit

sub btnMake_click()
dim i as integer
dim sUnit as string 
dim vFile


for i = 0 to lstUnit.listcount - 1
   sUnit = lstUnit.ItemData(i)   'get next item in list
   lstUnit = sUnit              'set list to that item

   vFile = "c:\temp\" & sUnit & ".xlsx"

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitPayments", vFile, True,"Payments"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitChrgs", vFile, True,"Charges"

next
end sub

 

[TSchaff]

No sure I fully understand how this works or how to implement for my specific tables and queries?