Print Page | Close Window

ExportToExcel_Automation (advanced)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=624
Printed Date: 16 Dec 2017 at 11:53am


Topic: ExportToExcel_Automation (advanced)
Posted By: Roger Carlson
Subject: ExportToExcel_Automation (advanced)
Date Posted: 23 May 2016 at 6:59am

There are many ways to export data from an Access database to an Excel spreadsheet. 

Office Automation

Office automation allows a developer to open and manipulate other MS Office applications in VBA code.  It allows the Access developer to use the specialized functions of those other programs, functions that would be hard to create natively.  I’ve used automation to create documents in Word, charts in Excel, and presentations in PowerPoint.  Sometimes all three from the same Access application.

Here are a few examples:

·  http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=396" rel="nofollow - - AutomatingWordFromAccess.mdb ( advanced)

·  http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=349" rel="nofollow - - ExportToExcelCharts.mdb ( intermediate )

·  http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=397" rel="nofollow - - AutomatingPowerpoint.mdb ( intermediate )

In this case, I want to export data from an Access database into specific formatted cells in an Excel spreadsheet. To do this, I need to use the Data Access Object model built into all Office apps.

Excel Object Library (VBA)

Through VBA, a developer can open and manipulate other Office applications. This is not limited to MS Office applications, but that’s another discussion. Each Office application has its own Object Model, each with its own Collections, Methods, and Properties. Once I reference that model in Access, I can use VBA to use that application’s Collections, Methods and Properties. Since I want to automate Excel, I need to use the Excel Object Library.

Setting References

In order to use an external object model, I need to first set a Reference to it. To do that, I need to open the Visual Basic Editor (VBE), which can be opened in Access on the Database Tools tab and choosing Visual Basic.

Once the VBE is open, choose Tools > References

The Reference List will look like this:

The first three checked references will automatically be there in a new Access database.  The fourth line WON’T.  You need to scroll down the list (to the “M”s) to choose Excel.  Check the box and click OK. Reopen the References, and it should look something like the above.

Depending on your Office version, the references may be named differently, there may be additional references, or they may be in a different order. It’s not important most of the time.  The important thing is to find and check the Excel reference.

Data Access Objects (DAO)

DAO is the object model used to open and manipulate databases in VBA.  DAO is not part of Access. Technically, it’s the object model for the Jet database engine, and it can be referenced in any Office application to manipulate data.  However, since Access uses the Jet database engine as its default, a reference is set to it whenever an Access database is created. Since I need to take data from an Access database and send it to an Excel worksheet, I need both object models.

Two Methods

There are two ways of using Office Automation that I want to discuss:

  1. Using a Do…While loop to PUSH data from Access to Excel
  2. Using the CopyFromRecordset to PULL data into Excel from Access

Regardless of which method I use, there is a basic framework of creating and instantiating objects I need to build.  This framework looks like this:...

For more, download the sample:

         uploads/5/ExportToExcel_Automation.zip" rel="nofollow - ExportToExcel_Automation.zip




-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window