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