How To Do An Access Export To Excel In VBA

In this post, we are going to do an Access export to Excel, and you’ll see the VBA we’ll work with.

This Excel automation will create a worksheet for each of our data points, and then enter the row items for that data point.

We are going to let Access do most of the querying and “heavy lifting”, because that’s what it’s pretty good at.

So rather than create a few small queries and trying to link them in Excel, we will do all the linking in one Access query and send that result over to Excel.

In this image you’ll see the data we will be exporting to Excel

In this image you are going to find out that the data for the query is coming from multiple tables. So the idea is to join all the table data in Access, and then send it to Excel

The query here is going to be called “qryUnresolvedExcel”, and its sole purpose is to gather the data necessary for the Excel export.

In the beginning the Excel workbook will only have one worksheet “Main”:

The comments on the code will help decipher what going on:

After we are done running our VBA process, we will have an Excel Workbook with multiple sheets:

You can do the same type of Access export to Excel VBA by recreating the above datasheet in a table, and then run the code above.

Let me know if you have questions, and remember that sharing is nice, and may help someone else.

Free! Subscribe To Our YouTube Channel!

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather
Tags: , , , , , , , , , , , , , , , ,


Call Erik Now