Access does not close Excel App

[John_G]

Hi -

I have an MS Access sub that opens an Excel file which acts as a template, adds data to the workbook sheets, formats the whole thing, and saves the file to a new name.

All that works perfectly, and the Excel file is just as it should be.


The problem is that Access refuses to let go of the Excel instance; it still shows as a running process in Task Manager. However, if I close the MS Access database, the Excel process closes too. Using Task Manager to kill the Excel instance is not really an option, because if I try to run the Access procedure again, (with different of the same data), I get errors that really are meaningless. (A typical one is that the remote host computer does not exist or in unavailable).

My (much abbreviated) code is below; I left out for the moment the parts that populate and format the spreadsheet:

Private Sub Export_Template_B2()
  On Error GoTo ErrProc
  '******************************
  '
  ' Variables for Exporting
  ' =======================
  '
  Dim xlapp As Excel.Application, SQL As String
  Dim xlBook As Excel.Workbook, xlSheet1 As Excel.Worksheet, xlSheet2 As Excel.Worksheet
  Dim xlRange1 As Excel.Range, xlRange2 As Excel.Range, xlRange3 As Excel.Range
  
  '******************************
  '
  '  Start the excel application,
  '  open the excel spreadsheet template,
  '  then save it with the new file name to avoid overwriting the template
  '
  ExportTemplate = "C:\MS_Access\Job Descriptions\Excel Spreadsheet Development\JBOS Spreadsheet Template B2.xls"
'  Set xlapp = CreateObject("excel.application")
  Set xlapp = New Excel.Application
  Set xlBook = xlapp.Workbooks.Open(ExportTemplate)
  xlBook.SaveAs ExportFileName
'
' Many lines of code to populate and format the spreadsheet - all work fine
'
CloseDown:
  xlBook.Save
  xlapp.Workbooks.Close
  xlapp.Quit
  Set xlapp = Nothing
  Set db = Nothing
  DoCmd.SetWarnings True
  DoCmd.Close , , acSaveNo
  Forms![main menu].SetFocus
  Exit Sub
ErrProc:
  '// Error Handling
  If Err.Number Then
    Process_Error CurrentForm, CurrentProcedure, Err.Number & " - " & Err.Description
  Else
    Process_Error CurrentForm, CurrentProcedure, Err.Number & " - " & Err.Description
  End If
  Resume CloseDown
  '// Terminate
  On Error Resume Next
  '// Clear Error
  Err.Clear
End Sub

Anyone have any ideas as to what the problem might be?

Thanks!

John

[pbaldy]

Most common cause is probably in your "all work fine" code. Happens if you fail to refer to one of the Excel variables, like instead of:

xlapp.DoSomething

you just put

DoSomething

That will start an undocumented instance of Excel, which your code will never clear.

[pbaldy]

I knew I remembered an Access Blog on it:

http://blogs.office.com/b/microsoft-...l-in-code.aspx

[John_G]

Thank you!!!

Your first post pointed me in the right direction, and between it and the advice provided at an older newsgroup here:

http://www.accessmonster.com/Uwe/For...m-Access-again

The answer was to fully qualify all references, and it now works fine and exits Excel properly.

I find it curious how the resulting Excel spreadsheets looked exactly as they should, and the code ran without error, even though there were hidden problems.

Thanks again.

John

[pbaldy]

Happy to help!