Application will not Quit after running Docmd Outputto

[ted.martin]

Here are the facts:

I am using docmd.outputto to output a table in Excel. I have been asked to have the Excel application Open after the output so have set the Autostart parameter to True.

Everything works fine EXCEPT ....

Later; when I have finished work (and by now the Excel file has been closed) I use Docmd.Quit to close the access database BUT it freezes. If I use Application.Quit instead although that does indeed close access , it leaves the record-locking file open and I cannot re-open the access file as in Task manager MSACCESS is still running. Only when I "End process" can I reopen the file.

This is DEFINITELY something to do with the Excel output as if I revert to Autostart parameter to False, the 'Quit(s)' works fine.

Here is the Code and note the OutputTo is within compound IF statements.

'Now Output the Data
    Dim iCount As Integer
    iCount = DCount("[ReminderOption]", "Patients", "ReminderOption = TRUE")
        
    If iCount > 0 Then
        MsgBox iCount & " Patient Records for Reminders output", vbInformation, "Mail Merge data for " & UCase(strYesNo)
        
        Dim strType As String   ' for Log of Reminders
        
        If strYesNo = "Glasses" Then
            
            strType = strYesNo  ' for Log of Reminders
            
            If myYesNoResult("Letters Excel Select_Output_Format") = "Letters" Then
                Call Reminders("Glasses", "1st")
            Else
                DoCmd.OutputTo acOutputQuery, "QOR-Reminders GLASSES", "Excel97-Excel2003Workbook(*.xls)", _
                "c:\temp\1st_Reminders_Glasses_" & Format([Forms]![FPM-Dates].[txtStartDate], "ddmmyy") & "_" & _
                Format([Forms]![FPM-Dates].[txtEndDate], "ddmmyy") & ".xls", True
            End If
            
        Else
            strType = strYesNo  ' for Log of Reminders
        
            If myYesNoResult("Letters Excel Select_Output_Format") = "Letters" Then
                Call Reminders("Contact Lens", "1st")
            Else
                DoCmd.OutputTo acOutputQuery, "QOR-Reminders C-L", "Excel97-Excel2003Workbook(*.xls)", _
                "c:\temp\1st_Reminders_CL_" & Format([Forms]![FPM-Dates].[txtStartDate], "ddmmyy") & "_" & _
                Format([Forms]![FPM-Dates].[txtEndDate], "ddmmyy") & ".xls", False
            End If
            
        End If  ' Glasses
        
        DoEvents
        
    Else
        MsgBox "No Patients satisfy your Reminders Date range", vbExclamation, "Nothing Output"
    End If
    
    DoCmd.Close acForm, "FPM-Dates"   ' *****
      
    DoCmd.SetWarnings True

I have tried replacing the Query with an alternate and it still happens. Shortened the file name to test.xls and still access crashes on the Quit. Change the True to False and there are no problems.


Any thoughts would as always be appreciated.

 

[ted.martin]

code update.

 

[ted.martin]

SOLUTION FOUND - the code needs a DoEvents before the Docmd.Outputto line
...

But WHY?

 

[Rand]

sounds to me the doevents is just letting access foreget about the excel being open till windows terminates it.
you need to kill it
ex:
Dim objApp As Object Set objApp = CreateObject("Excel.Application") objApp.Visible = True objApp.displayalerts = False objApp.Workbooks.Open "c:\test.xls" Dim location As String location = objApp.ActiveWorkbook.FullName objApp.Quit DoCmd.OutputTo acOutputQuery, "QOR-Reminders GLASSES", acFormatXLS, location, True objApp.Workbooks.Open "c:\temp\1st_Reminders_Glasses_" & Format([Forms]![FPM-Dates].[txtStartDate], "ddmmyy") & "_" & _ Format([Forms]![FPM-Dates].[txtEndDate], "ddmmyy")
_& ".xls", True


i'm not an expert but maybe this helps
in newer versions of access and excel you need to in create the object then destroy it or access will wait for it to be destroyed cause these are com objects.

this is a function i use maybe it will work for you:
Public Function Send2ExcelSheet(strTQName As String, strSheetName As String)

' strTQName is the name of the table or query you want to send to Excel

' strSheetName is the name of the sheet you want to send it to
'call: RetVal = Send2ExcelSheet("currentweek", "sheet1")


Dim rst As DAO.Recordset

Dim ApXL As Object

Dim xlWBk As Object

Dim xlWSh As Object

Dim fld As DAO.Field

Dim strPath As String

Const xlCenter As Long = -4108

Const xlBottom As Long = -4107



On Error GoTo Err_Handler

strPath = "C:\CurrentWeek.xlsx"

Set rst = CurrentDb.OpenRecordset(strTQName)

Set ApXL = CreateObject("Excel.Application")

Set xlWBk = ApXL.Workbooks.Open(strPath)

ApXL.Visible = True



Set xlWSh = xlWBk.Worksheets(strSheetName)



xlWSh.Activate

xlWSh.Range("A5").Select



For Each fld In rst.Fields

ApXL.Activecell = fld.Name

ApXL.Activecell.Offset(0, 1).Select

Next

rst.MoveFirst



xlWSh.Range("A5").CopyFromRecordset rst

xlWSh.Range("1:1").Select

' This is included to show some of what you can do about formatting. You can comment out or delete

' any of this that you don't want to use in your own export.

With ApXL.Selection.Font

.Name = "Arial"

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

End With



ApXL.Selection.Font.Bold = True



With ApXL.Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.MergeCells = False

End With



' selects all of the cells

ApXL.ActiveSheet.Cells.Select



' does the "autofit" for all columns

ApXL.ActiveSheet.Cells.EntireColumn.AutoFit



' selects the first cell to unselect all cells

xlWSh.Range("A5").Select

rst.Close

Set rst = Nothing

Exit Function



Err_Handler:

DoCmd.SetWarnings True

MsgBox Err.Description, vbExclamation, Err.Number

Exit Function

End Function

Goodluck

 

[ted.martin]

Thanks; I tend to agree that Access complete its activity before the Excel sheet is opened. I have other Excel outputs but they are a simple Docmd.Outputto ... and these work fine. This problematic one is within compound IF statments and perhaps Access is not finalising these before the Output is run?

Either way, if a DoEvents solves the problem, then so be it. I discovered this by trial and error through introducing a simple Outputto line into the code at each section until I found the point at which the Quit crash occurred.

Something is clearly happening between the 3rd IF and the Docmd.Outputto.

Thanks for your help.