What I am trying to accomplish to export data to an existing Excel spreadsheet an go to the end of the spreadsheet to append the records. I am currently manually clicking of the query, right click to export and then determining the storage location. I would like to automate this process.
Either by running the query or passing the sql statement and then executing it. the exporting to excel.
Do you have any suggestions on the best approach.
This is what I have so far, what am I doing wrong . it errors at the "Set RS = CurrentDb.QueryDefs(strSQL)"
I know I am missing something.
Public Function PivotExport()
Dim xlApp As Object, xlWb As Object, xlWs As Object, nFName As String, strSQL As String
Dim RS As ADODB.Recordset
nFName = "Pivot _" & Format(Date, "MMM") & "" & Format(Date, "YY")
strSQL = "SELECT SystemDate, Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message AS MessageType, tblDistinictBody_Temp.Status, Tarriff" & _
" FROM tblDistinictBody_Temp LEFT JOIN uniform_messages ON" & _
" tblDistinictBody_Temp.MessageType = uniform_messages.id" & _
" GROUP BY SystemDate, Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message," & _
" Status, tblDistinictBody_Temp.Tarriff"
Set RS = CurrentDb.QueryDefs(strSQL)
'insert code to populate a variable, rs, with your recordset
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("I:\Access Reports\MasterMessageSystem\Pivot Tables\ &""& nfname &" \ "& nfname")
Set xlWs = xlWb.Worksheets("qryDistinctMsg_Export")
xlWs.[a65536].End(-4162).Offset(1, 0).CopyFromRecordset RS
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function
thanks,
Karen