troubleshooting Question

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

Avatar of gdunn59
gdunn59 asked on
Microsoft ApplicationsMicrosoft ExcelMicrosoft Access
3 Comments1 Solution2548 ViewsLast Modified:
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".



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

DoCmd.SetWarnings True
DoCmd.Echo True

    Exit Sub

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

End Sub

Open in new window


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros