Pushing and Pulling Access around
July 24, 2009 • JP • No Comments • Rate This Article
• Links to this article
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.
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.
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.
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.
Access, Automation ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: A function that returns Access version
Next Post: Checking Access properties using VBA
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].