There are many ways to export data from an Access database to an Excel spreadsheet. These include:
- Manual Methods
- Cut and Paste
- Manual Export
- Saved Exports
- TransferSpreadsheet Methods
- Macros
- VBA
- Office Automation Methods (this article)
- Do…While
- CopyFromRecordSet
In Part 1, I discussed various manual methods. In Part 2, I discussed TranferSpreadsheet. This time I’ll look at Office Automation, which gives you maximum control over automated exports.
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:
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:
'First I need to define some variables. Ordinarily, I would define all my variables here, but in this case, I’m going to define them as I go for clarity.
Dim RowVal As Integer
Dim ColVal As Integer
Dim conPath As String
' Find the path where the database resides
conPath = CurrentProject.Path
‘ Define and Open Database Object
Dim db As DAO.Database
Set db = CurrentDb
' Define and Open Recordset Object based on a query in my database called “TheDataQuery”. I could, of course be any saved query.
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("TheDataQuery")
' Define and Open Excel as an Excel Object. This is just opening Excel itself without a workbook.
Dim objXLApp As Excel.Application
Set objXLApp = Excel.Application
' Create and Open an Excel "Workbook" object. The workbook (that is Excel file) must exist in the place given in the .Open statement.
Dim objXLBook As Excel.Workbook
Set objXLBook = objXLApp.Workbooks.Open _
(conPath & "\Export_ForNext.xlsx")
' Create and Open a "Worksheet" object. The tab, in this case named TheDataSheet, must also already exist.
Dim objResultsSheet As Excel.Worksheet
Set objResultsSheet = objXLBook.Worksheets("TheDataSheet")
' Create a "Range" object. This is a named range in Excel which defines a certain group of cells. The actual values in the Range argument depends on the particular spreadsheet, but it should be at least as wide, and longer than the anticipated recordset.
Dim rng As Excel.Range
Set rng = objResultsSheet.Range("A2:F45")
' ****EXPORT CODE GOES HERE ****
' Save and close spreadsheet
objXLBook.Save
objXLBook.Close
' Lastly, I’ll destroy all the objects I created.
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
So far, I haven’t actually done anything useful. I’ve only created and destroyed a bunch of objects. The real work happens in the EXPORT CODE GOES HERE section. The code will vary depending on the method you use and the whether I want to over-write the data or append the data to the end of the existing data.
Do…While Method
I’ll start with the Do…While method. This method “pushes” the data from Access into Excel. It does this by opening and walking through a recordset in Access and setting specified cells in the Excel sheet.
Overwrite Data
(Enter the following into the ****EXPORT CODE GOES HERE ****)
' **************************************
' Set beginning row and column numbers
RowVal = 2
ColVal = 1
' Delete any existing data
rng.ClearContents
' Write data from Access query to Spreadsheet
Do While Not rs.EOF
objResultsSheet.Range(Cells(RowVal, ColVal), _
Cells(RowVal, ColVal)) = rs!phys_id
objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
Cells(RowVal, ColVal + 1)) = rs!spec_code
objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
Cells(RowVal, ColVal + 2)) = rs!Export_Date
objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
Cells(RowVal, ColVal + 3)) = rs!Drug1
objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
Cells(RowVal, ColVal + 4)) = rs!Drug2
objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
Cells(RowVal, ColVal + 5)) = rs!Drug3
RowVal = RowVal + 1
rs.MoveNext
Loop
' **************************************
Append Data
(Enter the following into the ****EXPORT CODE GOES HERE ****)
' **************************************
' Set beginning row and column numbers
RowVal = 2
ColVal = 1
' Find the last row of data in the Excel sheet
Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
RowVal = RowVal + 1
Loop
' Write data from Access query to Spreadsheet
Do While Not rs.EOF
objResultsSheet.Range(Cells(RowVal, ColVal), _
Cells(RowVal, ColVal)) = rs!phys_id
objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
Cells(RowVal, ColVal + 1)) = rs!spec_code
objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
Cells(RowVal, ColVal + 2)) = rs!Export_Date
objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
Cells(RowVal, ColVal + 3)) = rs!Drug1
objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
Cells(RowVal, ColVal + 4)) = rs!Drug2
objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
Cells(RowVal, ColVal + 5)) = rs!Drug3
RowVal = RowVal + 1
rs.MoveNext
Loop
' **************************************
CopyFromRecordSet Method
The CopyFromRecordSet method is not an Access function. It’s actually a method built into Excel. However, because I’m using Office Automation, I can use this Excel method to pull data from Access to Excel. Using this method, you don’t need to worry about row position or column position.
Recall that above, I defined a recordset object based on the query “TheDataQuery”,
Set rs = db.OpenRecordset("TheDataQuery")
and I also created a range object
Set rng = objResultsSheet.Range("A2:F45")
The CopyFromRecordSet method allows me to drop the defined recordset (rs) into the defined range (rng). It requires an argument for maximum rows and columns (45 and 10 below). I usually just define an area bigger than I could possibly need.
Overwrite Data
(Enter the following into the ****EXPORT CODE GOES HERE ****)
' **************************************
' Set beginning row and column numbers
RowVal = 45
ColVal = 10
' Delete any existing data
rng.ClearContents
' Write data from Recordset to Spreadsheet range
Call rng.CopyFromRecordset(rs, RowVal, ColVal)
' **************************************
Append Data
(Enter the following into the ****EXPORT CODE GOES HERE ****)
' **************************************
' Set beginning row and column numbers
RowVal = 1
ColVal = 6
' Find the last row of data
Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
RowVal = RowVal + 1
Loop
' Create a "Range" object
Set rng = objResultsSheet.Range(Cells(RowVal, 1), Cells(RowVal, 1))
' Write data from Recordset to Spreadsheet range
Call rng.CopyFromRecordset(rs, RowVal, ColVal)
' **************************************
You can find this code all pulled together in a sample database from my website:
Download Sample:
No comments:
Post a Comment