Last month we explored the process of importing data from Microsoft Excel. This month, we look at how to get your data OUT of Access and back into Excel. While this may be as simple as right-clicking the table or query and choosing Export from the menu, it may also be complicated considerably by the need for custom formatting or the use of specific data templates. In the following article, we will demonstrate how to transfer your data into a prepared template using Excel Automation.
Download and Try it Yourself
Often, the best way to understand something is to experience it. As usual, this article comes with a download containing the code and I encourage you to unpack it and give the tool a spin. What you will see upon opening the MDB file is the following form.
Clicking the button initiates the code which performs the following actions:
1. Copies the template to a new output file
2. Creates an Excel object and opens the worksheet
3. Loads a recordset of data and loops through records
4. Appends data one field at a time to the spreadsheet
5. Closes, saves and cleans up objects
The process is simplified by the preparation done ahead of time with the Excel template. There you set your headers, determine which tab is to use and apply any formatting you wish. With a simple FileCopy command, a copy of this template is created, into which the data is added. This means, however, that the columns in the recordset need to match those in the template. While a mismatch will not break the data dump, it will result in an awkward output file.
Starting Point for Your Code
Below is the main text of code that accomplishes the Excel Automation. This example uses early binding, so a reference needs to be made to Microsoft Excel. Do this by selecting References from the Tools menu while working in any code module. As shown below, I am using Excel 11 (Office 2003) and this reference will be MISSING if you do not happen to have Excel 2003 installed on your computer.
However, this does not mean you cannot run the code. If you see a MISSING reference, simply unselect it, scroll down to the Microsoft Excel Object Library that is available to you and click the check box. Now try to run the code and you will see that it both compiles and behaves as expected.
The code below does a number of other useful things that are not directly important to the data export. Note the use of Application.SetOption to turn off error handling. This ensures that any errors cause the code to halt, which simplifies debugging. In addition, the code posts a message to the form through a label and the Repaint method is called to force the form image to update.
Pay special attention to the constants defined below. cTabTwo is used when setting the Worksheet object to the second tab and cStartRow and cStartColumn correspond to the cell location where the first piece of data is to be entered. By changing these values, you change where data is loaded. In some cases, I have used a table of worksheets and values for these start coordinates to table-drive the output based on the query or recordset being dumped.
Public Function ExportRequest() As String On Error GoTo err_Handler ' Excel object variables Dim appExcel As Excel.Application Dim wbk As Excel.Workbook Dim wks As Excel.Worksheet Dim sTemplate As String Dim sTempFile As String Dim sOutput As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim sSQL As String Dim lRecords As Long Dim iRow As Integer Dim iCol As Integer Dim iFld As Integer Const cTabTwo As Byte = 2 Const cStartRow As Byte = 4 Const cStartColumn As Byte = 3 DoCmd.Hourglass True ' set to break on all errors Application.SetOption "Error Trapping", 0 ' start with a clean file built from the template file sTemplate = CurrentProject.Path & "\SalesTemplate.xls" sOutput = CurrentProject.Path & "\SalesOutput.xls" If Dir(sOutput) <> "" Then Kill sOutput FileCopy sTemplate, sOutput ' Create the Excel Applicaiton, Workbook and Worksheet and Database object Set appExcel = Excel.Application Set wbk = appExcel.Workbooks.Open(sOutput) Set wks = appExcel.Worksheets(cTabTwo) sSQL = "select * from qrySales" Set dbs = CurrentDb Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) ' For this template, the data must be placed on the 4th row, third column. ' (these values are set to constants for easy future modifications) iCol = cStartColumn iRow = cStartRow If Not rst.BOF Then rst.MoveFirst Do Until rst.EOF iFld = 0 lRecords = lRecords + 1 Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls" Me.Repaint For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1) wks.Cells(iRow, iCol) = rst.Fields(iFld) If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy" End If wks.Cells(iRow, iCol).WrapText = False iFld = iFld + 1 Next wks.Rows(iRow).EntireRow.AutoFit iRow = iRow + 1 rst.MoveNext Loop ExportRequest = "Total of " & lRecords & " rows processed." Me.lblMsg.Caption = "Total of " & lRecords & " rows processed." exit_Here: ' Cleanup all objects (resume next on errors) On Error Resume Next Set wks = Nothing Set wbk = Nothing Set appExcel = Nothing Set rst = Nothing Set dbs = Nothing DoCmd.Hourglass False Exit Function err_Handler: ExportRequest = Err.Description Me.lblMsg.Caption = Err.Description Resume exit_Here End Function
While the cleanup code is last, it is most certainly not least. If you fail to unload a database object, it goes away when Access is closed. Not so, of an Excel Application object created from VBA. You must explicitly destroy all automation objects, closing and saving where appropriate. Otherwise, you will end up with rogue tasks, Excel tasks in this case, running in Task Manager. Interrupting the code in the middle of processing may also require that Access be closed and reopened, in order for the automation code to proceed correctly. This is unfortunate and so far, I have not been able to explain it, but suffice it to say that the cleanup is practically the most important part of deploying this process.
While loading data into cells, I performed two formatting tasks. The first is a conditional NumberFormat command that actually sets the Date format for any fields that contain the word "date" in their name. The second is a general cell format command that sets the WrapText property of the cell to false. Here is where you may add your own conditional formatting so that the output meets your needs. In some applications, I test the data against defined business rules and set the back-color accordingly to flag the entry as correct or false. This aids the users in locating important information that is much easier identified with code than with scanning eyes.
Excel is indeed a great product and will be with our users for the indefinite future. Getting Access to cleanly output relational data to Excel is a valuable trick you will use often.