Pushing and Pulling Access around

In Import data to Access from Excel and My first Access macro, I posted some Access macros that push data into Access. Here is some code that does both.

Pulling from Access

This first procedure is basically just a wrapper for the DoCmd.TransferSpreadsheet method. I prefer to encapsulate this function so that I can call it from anywhere, instead of having to type DoCmd.TransferSpreadsheet … everywhere.

Function ImportWorksheetToTable(Optional transferType As AcDataTransferType = acImport, _
 Optional spreadsheetType As AcSpreadSheetType = acSpreadsheetTypeExcel8, _
 Optional tableName As String, Optional workbookName As String, _
 Optional HasFieldNames As Boolean = True)
' import Excel worksheet to a table

  DoCmd.TransferSpreadsheet transferType, spreadsheetType, tableName, workbookName, HasFieldNames

End Function

Hopefully this code is self-explanatory, but if you need to know how TransferSpreadsheet works, see the related MSDN article on the TransferSpreadsheet method.

Pushing from Access

If you're exporting a Recordset to Excel, you'll appreciate these next two procedures. The first function writes field names to Excel. You'll need it when writing from Access to Excel, as the CopyFromRecordset method in Excel doesn't copy the field headers.

It takes a DAO.Recordset object, and a Excel.Worksheet object (declared As Object for all you late-binders; hell, DAO.Recordset could be declared As Object as well, if you wanted late-bound DAO).

You'll need to have already created a DAO.Recordset object and an Excel.Worksheet object, which you'll pass to the function. The field names for the Recordset will be written across the top of the worksheet.

Function WriteFieldsToExcel(ByRef rs As DAO.Recordset, _
  ByRef xlwks As Object)
' takes recordset and writes fields to Excel worksheet

  Dim fld As DAO.Field
  Dim i As Long

  i = 1
  For Each fld In rs.Fields
    xlwks.Cells(1, i).Value = fld.Name
    i = i + 1
  Next fld

End Function

By the way, if you need create a Recordset, see Miscellaneous Access VBA Macros for a macro that can do just that.

Here's the second function. This one writes a Recordset to Excel. It takes the same parameters as the function above, so you'll probably use them in tandem to dump a Recordset into Excel from Access.

Function WriteRecordsetToExcel(ByRef rs As DAO.Recordset, _
  ByRef xlwks As Object)
' takes recordset and dumps it to Excel worksheet
  xlwks.Range("A2").CopyFromRecordset rs
End Function

Short, single-purpose, and sweet! Stay tuned for even more Access VBA samples.