mgm1986
06-15-2017, 08:09 AM
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
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