Access 2003 won't close properly after I run a function 1

[Dom606]

The code below is run from a command button on a form. It opens a query parameter form that I use to select the begin and end dates. The query results go to an excel spreadsheet. A second spreadsheet opens and links to the fields returned in the first spreadsheet. All of this works fine. However, when I try to exit Access it simply hangs and I have to go to task manager to kill the process. I think that I am not cleaning up as I should after the two spreadsheets are open. Suggestions?

Private Sub ReportToExcel_Click()
On Error GoTo Err_ReportToExcel_Click
    
    DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rptTimeTOExcel", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="\\bigguy\users\Dom_F\Access\TimeAndBilling\TestData.xls", _
    AutoStart:=True
    
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open "\\bigguy\users\Dom_F\Access\TimeAndBilling\time sheet03052011.xls", True, False
    Set xlApp = Nothing

   
Exit_ReportToExcel_Click:
    Exit Sub

Err_ReportToExcel_Click:
    MsgBox Err.Description
    Resume Exit_ReportToExcel_Click
    
End Sub

 

[kjv1611]

Try this:

Private Sub ReportToExcel_Click()
On Error GoTo Err_ReportToExcel_Click
    
    DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rptTimeTOExcel", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="\\bigguy\users\Dom_F\Access\TimeAndBilling\TestData.xls", _
    AutoStart:=True
    
    Dim xlApp As Excel.Application
    [highlight]Dim wb As Excel.Workbook[/highlight]

    Set xlApp = CreateObject("Excel.Application")
    [green]'xlApp.Visible = True - I don't think you need this part at all.[/green]
    
    [highlight]Set wb =[/highlight] xlApp.Workbooks.Open "\\bigguy\users\Dom_F\Access\TimeAndBilling\time sheet03052011.xls", True, False
  
Exit_ReportToExcel_Click:
[highlight]    If wb Is Nothing Then
    Else
       Set wb = Nothing
    End If

    If xlApp Is Nothing Then
    Else
       Set xlApp = Nothing
    End If[/highlight]

    Exit Sub

Err_ReportToExcel_Click:
    [highlight]MsgBox "An Error Has Occurred!" & vbCrLf & vbCrLf & _
           "Error Code = " & Err.Number & vbCrLf & _
           "Description = " & Err.Description _
           ,"Error!" ,vbCritical[/highlight]

    Resume Exit_ReportToExcel_Click
    
End Sub

Post back to let us know whether or not that fixed it.

You may have to edit the If/Else/End If statements, as I'm typing that from memory, and can't remember for sure how you check on the object existence... if it's wrong, I can dig and find it in my own code.

I highlighted my changes - unless I missed one or two.

 

[Dom606]

Hi kjv1611
I pasted your code and tried to compile it but get an syntax error on this line.

Set wb = xlApp.Workbooks.Open "\\bigguy\users\Dom_F\Access\TimeAndBilling\time sheet03052011.xls", True, False

 

[kjv1611]

Hmm, try taking off the options at the end.. comment them out, so it'd be like this:

Set wb = xlApp.Workbooks.Open "\\bigguy\users\Dom_F\Access\TimeAndBilling\time sheet03052011.xls"[GREEN]', True, False[/GREEN]

I'm quite honestly just guessing at the moment, b/c can't think for sure what the issue is on that one at the moment, and no time to troubleshoot on my end.. Sorry.

Post back with whether that fixes it or not..

 

[Dom606]

Thanks kjv1611,
Actually I tried that before I posted. It did not make any difference. Still get syntax error.
Dom

 

[kjv1611]

maybe you have to open it...

xlApp.Workbooks.open "..."

'then set the wb object to it?

Set wb = xlApp.ActiveWorkbook or
Set wb = xlApp.Workbooks("...")

...also, are you sure it's the Excel application instance that is causing the database to hang? Have you tried commenting out the whole portion with with the Excel code, and tested running after that?

 

[kjv1611]

--in my last question, I'm referring to your initial/original issue when you started the discussion/thread.

 

[Dom606]

Well, when I comment the section below of my original code, it works fine. If I include the code to open the second spreadsheet, Excell stays open in the task manager and Access hangs.

Private Sub ReportToExcel_Click()
On Error GoTo Err_ReportToExcel_Click

    DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rptTimeTOExcel", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="\\bigguy\users\Dom_F\Access\TimeAndBilling\TestData.xls", _
    AutoStart:=True

[COLOR=Green]'    Dim xlApp As Excel.Application
'    Set xlApp = CreateObject("Excel.Application")
'    xlApp.Visible = True
'    xlApp.Workbooks.Open "\\bigguy\users\Dom_F\Access\TimeAndBilling\time sheet03052011.xls"
'    Set xlApp = Nothing[/color]


Exit_ReportToExcel_Click:
    Exit Sub

Err_ReportToExcel_Click:
    MsgBox Err.Description
    Resume Exit_ReportToExcel_Click

End Sub

 

[kjv1611]

What about if you only comment out the xlApp.Visible = False line, have you tried that? I don't remember - I think I mentioned it earlier, unless I just forgot to.

 

[Dom606]

This is what I finally got to work. The AutoStart:=True was causing all the problems.
Dom

Private Sub ReportToExcel_Click()
On Error GoTo Err_ReportToExcel_Click
  

    DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rptTimeTOExcel", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="\\bigguy\users\Dom_F\Access\TimeAndBilling\Test.xls"

    Dim Document As String
    Dim Excel As String
  
    Document = "\\bigguy\users\Dom_F\Access\TimeAndBilling\Test.xls"
    Excel = "C:\Program Files (x86)\Microsoft Office\OFFICE11\Excel.exe"

    Shell """" & Excel & """ """ & Document

    Exit Sub

Err_ReportToExcel_Click:
    MsgBox Err.Description
   
End Sub

 

[kjv1611]

Go figure. Well, good to know, thanks for sharing!