Solved VBA Help - Queries to new excel workbook (1 Viewer)

[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)

Office VBA reference topic

learn.microsoft.com

 

[Pat Hartman]

When you do the TransferSpreadsheet for the first query using query1 as the TableName and exportfilename1.xlsx as the filename. The second TransferSpreadsheet will use query2 as the TableName and also go to exportfilename1. That his how you export two recordsets to one workbook. Each query ends up as a separate sheet in the workbook. Name your queries as you would like the worksheets labeled.

 

[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).

 

[Pat Hartman]

Apparently Access didn't get the memo that Excel now allows spaces (and who knows what else) in its sheet names. Older versions of Excel will not allow spaces in sheet names so make sure, if you intend to use them, that all your recipients have a new enough version of Excel to allow the names with spaces.

 

[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.