- Local time
- Today, 17:10
- Joined
- Feb 19, 2013
- Messages
- 16,601
I put this code together for a recent thread - might be useful for excel users. Main reason for creating it is that if trying to import a query recordset that uses the nz function, the normal import routines will fail because nz is an access application function and not available to MS query/data connection wizard which both use ADO/OLEDB. There may be other functions, but this was about nz. This code imports a DAO recordset
Change the bits in red to suit your requirement.
Code could be behind a button or some other event or just run from the macros option on the developer ribbon.
Code populates the current worksheet, you would need to modify it to use a different/named worksheet and will no doubt need additional code to clear previous data, reset ranges etc as well as
Change the bits in red to suit your requirement.
Code:
Public Sub importFromAccess()
Dim accessApp As Object
Dim rs As Object
Dim fld As Object
Dim i As Long
Dim j As Integer
Set accessApp = CreateObject("access.application")
accessApp.opencurrentdatabase ("[COLOR=red]C:\[/COLOR][COLOR=red]pathtodb[/COLOR][COLOR=red].accdb[/COLOR]")
Set rs = accessApp.currentdb.openrecordset("[COLOR=red]myQuery[/COLOR]")
if not rs.eof then
'set column headers
j = 0
For Each fld In rs.Fields
Cells(1, j + 1) = fld.Name
j = j + 1
Next fld
Set fld = Nothing
'populate with data
Cells(2,1).CopyFromRecordset rs
'alternative (might want to format cells as they are populated)
'i = 2
'While Not rs.EOF
' For j = 0 To rs.Fields.Count - 1
' Cells(i, j + 1) = rs.Fields(j)
' Next j
' i = i + 1
' rs.MoveNext
'Wend
end if
Set rs = Nothing
Set accessApp = Nothing
End SubCode populates the current worksheet, you would need to modify it to use a different/named worksheet and will no doubt need additional code to clear previous data, reset ranges etc as well as
Last edited:
