Moving Query results to Excel

[mgm1986]

Hoping someone can help steer me straight. My goal is to output the results of a query to an Excel Template in a specified cell and then save the file as an XLSX with the suffix being some field value. I am able to export using the DoCmd.TransferSpreadsheet but this doesn't allow me to specify my template so I took a different approach. I am VERY new to programming so I have been scouring the web for help to get me to this point and now I am stuck.


This code works fine BUT I need to modify it so the results of a query get output to the Excel file:


Public Sub PrintQueryResults()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rosterSQL As String
Dim TERR_ID As String
Dim outputFileName As String
Dim qryDef As QueryDef
Dim mySheet As Object
Dim xlApp As Object
Dim strName As String

Set qryDef = CurrentDb.QueryDefs("Calls")
Set db = CurrentDb
rosterSQL = "SELECT distinct Territory_ID from TERR_CALL_MATTY"
Set rst = db.OpenRecordset(rosterSQL)

Do While Not rst.EOF

TERR_ID = rst!Territory_ID
' I want the results of this query to be exported to the Excel file
qryDef.SQL = "SELECT * FROM TERR_CALL_MATTY where Territory_ID = '" & TERR_ID & "'"
outputFileName = "C:\Users\joel.wean\Desktop\DEV\Test\TEST_" & TERR_ID & ""

''' *** TESTING THIS SECTION OF CODE

strName = "C:\Users\joel.wean\Desktop\DEV\Test\TEST.xlsx"
Set xlApp = CreateObject("Excel.Application")
Set mySheet = xlApp.Workbooks.Open("C:\Users\joel.wean\Desktop\DEV\test.xltx")
mySheet.SaveAs outputFileName
xlApp.Visible = False
Set mySheet = xlApp.Sheets("Test")
'mySheet.cells(2, 2).Value = "SUCCESS" --- THIS WORKS
mySheet.Application.ActiveWorkbook.Save
mySheet.Application.ActiveWorkbook.Close


''' ***
rst.MoveNext

Loop

End Sub

[mgm1986]

I got this to work, now I need to add an IF statement to use the blank .XLTX template unless the output already exists, then I want to use the existing file. Will post an update once I get there.