Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - ExportToExcel_Automation (advanced)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

ExportToExcel_Automation (advanced)

 Post Reply Post Reply
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 20 Feb 2008
Status: Offline
Points: 261
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: ExportToExcel_Automation (advanced)
    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:

·  AutomatingWordFromAccess.mdb ( advanced)

·  ExportToExcelCharts.mdb ( intermediate )

·  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:

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.