Solved VBA Help - Queries to new excel workbook

[GC1]

Hi All,

I am seeking some guidance on creating some VBA code to handle a process, I recently started to edit VBA and have had some fun creating a CRUD db. The next project is slightly different and if I have some assistance on the principles I can build what I need from there. I have trawled a number of posts (including Google) similar to mine but unable to relate.

In the sample DB, I have two simple select queries and I would like to understand the code needed to run each query and insert the data into a new single excel workbook with each query having its own tab. The output excel file is what I am trying to achieve.

Many Thanks

 

[ebs17]

DoCmd.TransferSpreadsheet method (Access)

Access VBA reference

learn.microsoft.com

 

[arnelgp]

first, you need to rename your Queries same as your Output excel worksheet (see the new query).
next, access will replace the "space" on your worksheet name to underscore (_), therefore
you need to open the workbook and rename it again (see ExportData code).

 

[GC1]

first, you need to rename your Queries same as your Output excel worksheet (see the new query).
next, access will replace the "space" on your worksheet name to underscore (_), therefore
you need to open the workbook and rename it again (see ExportData code).

Thank you arnelgp, much appreciated. This works perfectly.

 

[GC1]

Thanks everyone for your input, I now have a working solution.

 

[arnelgp]

Older versions of Excel will not allow spaces in sheet names so make sure,

the oldest i have are 2000 and 2003 and they both allow space on worksheet name.