ExportToExcel_Automation (advanced) |
Post Reply |
Author | |
Roger Carlson
Microsoft MVP Joined: 20 Feb 2008 Status: Offline Points: 261 |
Post Options
Thanks(0)
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:
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: |
|
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |