Creating and Saving a new Excel file with different Worksheets from different Access Queries

[alfonsolans]

Hello to all:

Team hope you are doing great today and thanks fin advance for your support in this great community, I have a huge question:

I am trying to create an excel document (template or not) with different worksheets that will be feed from different Access Queries, I started creating the module on VBA, it runs but instead of creating me only one file with all the different worksheets. it is creating different excel files with the information on every worksheet but in 6 different files, how can I close each query on my module to input the information on the same excel sheet instead on 6 of them, here is my module below:


Sub exportQueryADODB()
Dim dbs As Database
Set dbs = CurrentDb

Set rsQuery = dbs.OpenRecordset("In Transit Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("In Transit").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Last Night Arrival Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("Last Night Arrival").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("On The Floor Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("On The Floor").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Assigned on a TR2 Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("Assigned TR2").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Dispatched Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("Dispatched").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Delivered Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("Delivered").Range("A2").CopyFromRecordset rsQuery

End Sub

 

[xenou]

Hi,
You can try this, but it is UNTESTED and might fail.
The main thing is you cannot be creating a new excel app and opening 6 new workbooks, which is what you are doing (you should be able to tell that just by reading your code).

Sub exportQueryADODB()
Dim dbs As Database
Set dbs = CurrentDb

Set rsQuery = dbs.OpenRecordset("In Transit Query Query")

Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Auto\Lean\Pallet Report\Pallet Report Complete.xlt")
targetWorkbook.Worksheets("In Transit").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Last Night Arrival Query Query")
targetWorkbook.Worksheets("Last Night Arrival").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("On The Floor Query")
targetWorkbook.Worksheets("On The Floor").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Assigned on a TR2 Query")
targetWorkbook.Worksheets("Assigned TR2").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Dispatched Query")
targetWorkbook.Worksheets("Dispatched").Range("A2").CopyFromRecordset rsQuery

Set rsQuery = dbs.OpenRecordset("Delivered Query Query")
targetWorkbook.Worksheets("Delivered").Range("A2").CopyFromRecordset rsQuery

End Sub

 

[alfonsolans]

Hello to all:

Awesome Xenou thanks a lot for your help it worked perfectly, another quick question if I want to add at the End of the Module to Save and Close the file, I just need to add the following at the bottom of the module before the End Sub right,

targetWorkbook.Save
targetWorkbook.Close

 

[alfonsolans]

Hello to all:

Awesome Xenou thanks a lot for your help it worked perfectly, another quick question if I want to add at the End of the Module to Save and Close the file, I just need to add the following at the bottom of the module before the End Sub right,

targetWorkbook.Save
targetWorkbook.Close

 

[alfonsolans]

Also I am trying to create a Macro to Run the Code, how can I setup or how I should do to get or to build the Function Name when I am building the Macro in Access,

Again thanks a lot for your help appreciate it.

 

[xenou]

Hi,
yes, you should save and close the workbook:

targetWorkbook.Save
targetWorkbook.Close

Also you should close the app if you aren't using it anymore:

excelApp.Quit

I don't use macros so I don't know much about them offhand. I would guess you can create a macro that "Runs a vba procedure or function" and provide the name of the procedure. Probably for that to work the procedure needs to be in a module (not in form code).

 

[alfonsolans]

Awesome thanks a million xenou your help was great, now one last question and I should let you go lol, after your help my Module looks like this:

Option Compare Database
Sub exportqueryexcel()
Dim dbs As Database
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("In Transit Query Query Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("In Transit").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
targetWorkbook.Close
excelApp.Quit
Set rsQuery = dbs.OpenRecordset("Last Night Arrival Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("Last Night Arrival").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
targetWorkbook.Close
excelApp.Quit
Set rsQuery = dbs.OpenRecordset("On The Floor Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("On The Floor").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
targetWorkbook.Close
excelApp.Quit
Set rsQuery = dbs.OpenRecordset("Assigned on a TR2 Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("Assigned TR2").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
targetWorkbook.Close
excelApp.Quit
Set rsQuery = dbs.OpenRecordset("Dispatched Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("Dispatched").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
targetWorkbook.Close
excelApp.Quit
Set rsQuery = dbs.OpenRecordset("Delivered Query Query")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
targetWorkbook.Worksheets("Delivered").Range("A2").CopyFromRecordset rsQuery
targetWorkbook.Save
End Sub


Now Ho can I do to Run it in Access, maybe on a form to have a chance for the operators to run it without opening the editor or any other file, can I just add it to a Macro in a button, don't know I have been trying to figure it out, but is asking me other things like the function name, module name and procedure name, I do input some of them but it just open the VBA Editor but does not run the module.

So sorry for so many questions but you are saving my week, again I really appreciate all of your support thanks

 

[stumac]

You shouldn't have to keep opening and closing the workbook, like xenou's example - you open it once, make the changes and then save it once (you are leaving it open in your final code). You should also close/clean up anything you leave open within your code.

to run from a form if you add a button and under properties got to event and within the line 'On click' select the ..., if you select code builder. You can either paste everything between the Sub() and End Sub() or if you have it saved in a module call this by entering 'Call exportqueryexcel'

 Sub exportqueryexcel()
 
 Dim dbs As Database
 Set dbs = CurrentDb
 Set rsQuery = dbs.OpenRecordset("In Transit Query Query Query Query")
 Set excelApp = CreateObject("Excel.application", "")
 excelApp.Visible = True
 
 Set targetworkbook = excelApp.workbooks.Open("M:\Data Access\Lean\Pallet Report\Pallet Report Complete.xlsx")
 targetworkbook.Worksheets("In Transit").Range("A2").CopyFromRecordset rsQuery
 
 Set rsQuery = dbs.OpenRecordset("Last Night Arrival Query Query")
 targetworkbook.Worksheets("Last Night Arrival").Range("A2").CopyFromRecordset rsQuery

 Set rsQuery = dbs.OpenRecordset("On The Floor Query")
 targetworkbook.Worksheets("On The Floor").Range("A2").CopyFromRecordset rsQuery
 
 Set rsQuery = dbs.OpenRecordset("Assigned on a TR2 Query")
 targetworkbook.Worksheets("Assigned TR2").Range("A2").CopyFromRecordset rsQuery
 
Set rsQuery = dbs.OpenRecordset("Dispatched Query")
 targetworkbook.Worksheets("Dispatched").Range("A2").CopyFromRecordset rsQuery
 
 Set rsQuery = dbs.OpenRecordset("Delivered Query Query")
 targetworkbook.Worksheets("Delivered").Range("A2").CopyFromRecordset rsQuery
 targetworkbook.Save
 
cleanup:
 Set rsQuery = Nothing
 Set targetworkbook = Nothing
 Set excelApp = Nothing
 Set dbs = Nothing
  
 End Sub