Export Data To Excel

November 17, 2005

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:

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

Final Thoughts

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.

» See All Articles by Columnist Danny J. Lesandrini