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:
- Using a Do…While loop to
PUSH data from Access to Excel
- 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 .<
|