MS Access to Excel SaveAs Dialog Trapped Behind Application Non-Responsive...

[mrmmickle1]

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.

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

[ranman256]

Before the saveAs, minimize Access,
Docmd.minimize
sAveAs

[mrmmickle1]

Thanks. Looks to be working! Appreciate the help.