We help IT Professionals succeed at work.

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

2,530 Views
Last Modified: 2013-07-28
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

Open in new window

Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
That's strange. Sure that the Excel window is on screen (coordinate wise)? Do you see it in the taskbar?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm accepting my own solution, because there were no solutions provided, and I was able to resolve it.

Thanks,
gdunn59
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.