Kiwi living in Bermuda. Homemade charcuterie, foraging, wine, cats and island life.

Category: Microsoft Office

Exporting Access tables to Excel

There are pre-built Access functions to export data to Excel, such as the DoCmd.TransferSpreadsheet or DoCmd.OutputTo method, but anything but the most basic reports will require you to customise the Excel output more than these functions will allow. This code shows you how to create an instance of Excel and export the contents of the table into a new Excel workbook.  You can then format and manipulate the contents within Excel.

Public Sub ExportTableToExcel(strTableName As String)
     Dim oApp     As Object
     Dim xlSH     As Object
     Dim xlWB     As Object
     Dim rs       As Recordset
     Dim i        As Long

     'Check to see if Excel is open and get a handle to the Application
     On Error Resume Next
     Set oApp = GetObject(, "Excel.Application")
     On Error GoTo errorHandler
     'If oApp has not been set then there is no instance of Excel open.
     'Late binding the Excel application prevents issues with
     'compatibility as users may have different versions of Excel installed.
     If oApp Is Nothing Then Set oApp = CreateObject("Excel.Application")

     Set xlWB = oApp.Workbooks.Add
     Set xlSH = xlWB.Sheets(1)
     Set rs = CurrentDb.TableDefs(strTableName).OpenRecordset
    'Add in the header field names, the CopyFromRecordset command only
    'copies the data.
     For i = 0 To rs.Fields.Count - 1
          xlSH.Cells(1, i + 1).Value = rs.Fields(i).Name
     Next i

    'Paste in the data.
    xlSH.Range("A2").CopyFromRecordset rs

    'Format the worksheet
    With xlSH
        .Rows(1).Cells.Font.Bold = True
        .Columns.ColumnWidth = 100
    End With

    On Error Resume Next
    'When the Excel.Application object is created it is hidden by default.
    'You need to make sure it is set to visible as if there are errors or the
    'User runs the macro a number of times, they can end up with multiple hidden
    'Instances of Excel running in the background, which will only show up when
    'They are force quit.
    oApp.Visible = True

    'Clear object variables and make sure recordset is closed
    Set rs = Nothing
    Set xlSH = Nothing
    Set xlWB = Nothing
    Set oApp = Nothing
Exit Sub
    MsgBox ("An error occured: " & Err.Number & " " + Err.Description), vbCritical, "Error"
    Resume exitRoutine
End Sub

Download Sample Access database with code to export tables to Excel

Excel VBA Snake Game

Another quick one…

Excel snake game

Excel snake game

 Download Excel Snake

Excel VBA Popup Calendar

Example of the popup calendar when used in a spreadsheet.
Example of the popup calendar when used inline on a spreadsheet.

The VBA editor does not have a calendar control available to it by default – however it is possible to use the MS Access calendar control.  This tutorial is on how to set up a calendar control that can be accessed via a UserForm, or inline from a spreadsheet.

Download the following for an example of how to use the control either to populate a cell on a worksheet, or a control on a form.


1. Setting up a reference to the MSCAL.ocx calendar control.

You will need to set up a reference to the MSCAL.ocx file.  Open the “References” dialog (Tools > References in the VBA editor).

Click “Browse” and select the MSCAL.OCX file (this should be located in the root directory of your Microsoft Office installation.

Click “Open”.  This will now appear in the “Available References” window as Microsoft Calendar Control xx.x.  Make sure the checkbox to the left is enabled.


2. Copy the Userform and Class modules to the workbook.

Copy the “PopupCalendar” class module and the “frmPopupCalendar” module into the target workbook (you can do this by dragging them to the target book in the VBA editor).


3. Add subroutines

Add the following function into a module – this is a will display the form, and return a date object.

Function showCalendar() As Date
Dim pcPopup As PopupCalendar
Dim dtDate As Date

‘create a new PopupCalendar object
Set pcPopup = New PopupCalendar
‘sets the date as today

‘This will show the PopupCalendar as a modal form, meaning that
‘execution of the function will not continue until the form is closed.

‘As the form will now be closed, we can access the date that was selected
dtDate = pcPopup.calDate
Set pcPopup = Nothing

‘return the date
showCalendar = dtDate
End Function

4. Add Controls and Event Handlers

Add a Command Button control to the spreadsheet (use the Control Toolbox, accessed via the ‘View > Toolbars > Control Toolbox’ menu item.

Right-click on the control and select the “View Code” popup menu item.

Example of a worksheet with a Command Button control added

This will take you to the code for the Click event of the command button object.  Enter the following code:

Private Sub CommandButton1_Click()
‘Make sure the text before the underscore is the same as the name of your Command Button otherwise the event will not fire.

Dim dtDate As Date
‘Calls the showCalendar function we added before
dtDate = showCalendar()

If dtDate <> 0 Then
‘Note this will be the range that is populated with the date
‘The format statement is used to format the date returned (default is mm/dd/yyyy)
Me.Range(“B6”).Text = Format(dtDate, “dd/mm/yyyy”)
‘As will this
Me.Range(“B6”).Text = vbNullString
End If
End Sub


That’s it.  You can download the example Excel file here: PopupCalendar.xls

Excel Game of Life

Made this over lunchtime yesterday… it’s not pretty, but just seemed to be an obvious idea.  Only tested in Excel 2003.

Download Life.xls

Excel VBA Countdown Timer


Countdown preview

Excel VBA countdown clock.  Compatible with Excel 2007.

© 2019

Theme by Anders NorenUp ↑