Export Access Query to a copy of Excel Template, then run Excel macro

I have the following code (see below) in MS Access that dumps data into an Excel Template.  Once the data is exported into the Excel Template, I want a Macro that is in the Excel Template to run, and then do a SaveAS and save the finished report (spreadsheet) to a .xls format and not overwrite the Excel Template.

How can I get the Excel macro in the Template run after it dumps the Access data (Query) into the Template?

I have this line of code in there to run the Excel macro:

xlObj.Run "'" & xlWB.Name & "'!macQtrlyAssocReport"

It is working to a certain extent.  The problem is the spreadsheet is not showing up even though I have this code there "xlObj.Visible = True".

Thanks,

gdunn59

Private Sub cmdAssocErrorRpt_Click()
On Error GoTo cmdAssocErrorRpt_Click
Dim strOutputToPath As String
Dim xlObj As Object
Dim xlWB As Object
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim stDocNameAssocErrors As String
Dim Template As String
Dim TemplateFileC As String

DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.Hourglass True

'Network Drive Path
strOutputToPath = "\\Wiw2pwpfle001\data\QA Database\Employee Audit Scorecard System\Reports\Operational_Reports\ErrorDetailReport_By" & "" & "ALL ERRORS" & "_" & Format(Now(), "mm-dd-yyyy") & ".xls"

''Template on C Drive
TemplateFileC = "C:\Documents and Settings\ab56446\Desktop\Adam Mallord\Audit Database\New_Version_2013 (WIP)\FINAL Template for Quarterly_Assoc_Error_Report\Quarterly_Assoc_Error_Report.xlsm"

''Remove Report if process is run more than once daily on C Drive
'If Dir(strOutputToPathCDrive) <> "" Then Kill strOutputToPathCDrive

'Remove Report if process is run more than once daily
If Dir(strOutputToPath) <> "" Then Kill strOutputToPath

stDocNameAssocErrors = "qryQtrly_Assoc_Errors (for Report)"

Set xlObj = CreateObject("excel.application")
'xlObj.Workbooks.Open TemplateFileC
Set xlWB = xlObj.Workbooks.Add(TemplateFileC)


Set qdf = CurrentDb.QueryDefs(stDocNameAssocErrors)
qdf.Parameters("[Forms]![frmReports]![cboCategSelect]") = [Forms]![frmReports]![cboCategSelect]
qdf.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
qdf.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]

Set rs = qdf.OpenRecordset
With xlObj
  .Sheets("Detail").Range("A2").CopyFromRecordset rs
End With

xlObj.Run "'" & xlWB.Name & "'!macQtrlyAssocReport"

xlObj.Application.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs strOutputToPath, CreateBackup:=False
'xlObj.ActiveWorkbook.SaveAs strOutputToPathCDrive, CreateBackup:=False
xlObj.Application.DisplayAlerts = True

DoCmd.Hourglass False
xlObj.Visible = True
xlObj.Worksheets("Detail").Select
xlObj.Range("A2").Select

DoCmd.SetWarnings True
DoCmd.Echo True

Exit_cmdAssocErrorRpt_Click:
    Exit Sub

cmdAssocErrorRpt_Click:
If Err.Number = 2501 Then
    'no action required - ignore the error - because opening of report was cancelled
Else
    MsgBox Err.Description
End If

End Sub

Select all Open in new window