I am desparately trying to export a query recordset to an excel template without success. The query is has parmeters linked to form objects, which is where I might be going wrong but need some pointers.
So in my vba code , I need to set a recordset connection to the access query "qryHFAuditsDue1"
and then create instance of excel workbook from the template and paste recordset from cell range a2 down (using 'copyfromrecordset' ?)
I have attached my code which has been round the houses and am currently getting an invalid SQL Statement Error.
Public Sub AuditsDue1_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Excel.Application
'Dim oExcel As Object
Dim sFullPath As String
'Dim sPath As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set conn = CurrentProject.Connection
rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
' Create a new Excel instance
'Set oXL = CreateObject("Excel.Application")
Set oXL = New Excel.Application
' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0
' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:\Temp\AuditsDue.xlt"
' Open it
.Visible = True
.Cells(2, 1).CopyFromRecordset rst
Set rst = Nothing
Set oXL = Nothing
oXL.Visible = False