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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.