I’m not sure if it’s just me, or if it is a phenomenon common to Access programmers in general, but I find myself constantly exporting data to Excel, regardless of who the client might be.  I get hired to create a database so that they are not relying on clunky attempts at database emulation in Excel – yet when it’s all said and done, there is nearly always a justifiable requirement for an output to an Excel spreadsheet.

In the next few installments, I’ll show how I use some handy user-defined functions to easily place a button on just about ANY form with a RecordSource to export the form’s data to a nicely formatted Excel spreadsheet with only a couple of lines as demonstrated below (plus any required validation and/or standard error trap code you may want to include).

Set rst = Me.RecordsetClone

Call ExportRSCloneToExcel(rst)

The next few blogs will review the following functions:

• GetExcel() – covered in this blog

• WriteFieldsToExcel() – cycles through a recordset’s field list and adds it to a spreadsheet

• FormatReportGeneric() – does some basic, generic formatting of an Excel table

• ExportRSCloneToExcel() – see explanation above

It all starts with the GetExcel function, a simple function that allows me to create an Excel spreadsheet with a single line like the following:

Set objXL = GetExcel(FileName:=”C:\MyPath\MyFile.xlsx”,CreateNew:=True)

This simple line of code creates a new Excel object and adds a workbook to it.  If the optional CreateNew argument is False or omitted, the workbook named in the FileName argument is opened.  If the file doesn’t exist, it simply raises a 1004 error to the calling procedure.

I use this often to open Excel workbooks so that I can retrieve and edit data from them.  In this series, we’ll assume we are creating new workbooks in each instance, but the code would work equally as well with existing workbooks to which you want to add data.

Let’s begin with the GetExcel() function.  Note that you'll have to set a reference to the Microsoft Excel xx Object Library (xx being your installed version) under Tools==>References in your VBA Project window:

Function GetExcel(Optional FileName As String, Optional CreateNew As Boolean = False) As Object

    Dim objXL As Object    

    On Error GoTo errHandler

   ' Create an Excel Object

    Set objXL = CreateObject("Excel.Application")

    If FileName = "" Or CreateNew Then ' For new workbooks, add one to the collection

        objXL.Workbooks.Add

    Else ' For existing workbooks, open the one by the name you passed

        objXL.Workbooks.Open FileName

    End If

    Set GetExcel = objXL ' Return the new workbook

    Exit Function

errHandler:

    Err.Raise Err.Number ' Any issues get passed back up to the calling procedure

End Function

The next installment will review the WriteFieldsToExcel() function.  

Until then, happy coding!