I have a report that dumps a query into Excel and then I format it with some code.... Trying to prompt the user to save the report. However, sometimes the dialog gets trapped and you can't get to it. Is there a way to force the SaveAs Dialog to the front using a windows API or something? OR should I just kill the SaveAs portion.
This has happened to me before at another company...so it isn't just a one off.
Code:
Sub ExportToTemplate()
Dim rng As Range
Dim xl As Excel.Application
Dim xlWS As Worksheet
Dim xlFilePath As String
Dim xlSaveAsPath As Variant
Dim qdf As QueryDef
Dim db As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo ErrHandler
Set xl = New Excel.Application
Set db = CurrentDb()
'Define File String Variables
xlFilePath = "\\ns-linyvfs02\CDT_Reporting_v1.xlsx" ' The template file to dump data into....
xlSaveAsPath = xl.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & _
"(*.xlsm), *xlsm", Title:=strTitle, _
InitialFileName:="C:\Users\" & Environ("username") & "\Desktop\" & "CDT_Report_" & Format(Date, "MM_DD_YYYY"))
If xlSaveAsPath = False Then
MsgBox "Report Creation has been aborted!", vbCritical, strTitle
Exit Sub
End If
With xl
.Workbooks.Open xlFilePath 'Open Template Workbook
Set xlWS = .Sheets("Overview")
End With
With xlWS
Set qdf = db.QueryDefs("qryRptMonitoring") 'Set Query Definition
Set rst = qdf.OpenRecordset 'Open Recordset
Set rng = .Range("A1") 'Define range for export
rng.Offset(2, 0).CopyFromRecordset rst 'Copy RecordSet to Worksheet
rst.Close 'Close RecordSet
'Format Workbook code here....not relevant
End With
xl.ActiveWorkbook.SaveAs xlSaveAsPath 'Save the file <-----This dialog gets stuck behind other applications
xl.Visible = True 'Make Workbook Visible
Exit Sub
ErrHandler:
MsgBox "An error has occured. Please contact file administarator.", vbInformation, strTitle
End Sub