Solution for invisible "Save As" dialog in Access/Excel

Problem: While trying to programmatically export data from an MS Access database to Excel displaying Excel's "Save As" dialog using code like XL.Dialogs(xlDialogSaveAs).Show does not work correctly. This call might work in the VBA debug console, but in a production database the dialog (e.g. triggered by an event) would not appear on screen, although it would show up as process in task manager and furthermore block (freeze) the UI of your Access application. I originally observed this issue in Access 2007 and Windows Vista. At first I suspected threading or parent dialog's modality as causes, but that turned out wrong.

Solution: A hint for solving this problem comes from an observation that it ceases to occur once the Visual Basic editor has been opened. Obviously, this is not going to happen in a production database, but it can be emulated for the purpose of working around the described problem. Just add the following (pointless looking) code to your database's initialization for the later dialog invocation to respond as expected:

    Dim ref As Reference
    For Each ref In Application.References
        If ref.Name = "" Then
            ' nothing needs to be done, just loop
        End If
    Next