question

TerryRichter-6821 avatar image
0 Votes"
TerryRichter-6821 asked MarkBoucher-6205 rolled back

I can not use VBA in Access to close an Excel background process that appears under certain circumstances..

I want to edit an Excel file using VBA in Access. I can open, save and close the Excel file with no problem. But if, for example, I add a sheet, although it works, I am left with a background Excel process that should not be there. So long as that process remains I will get errors when rerunning my code. I am trying to upload a docx file to show clearly my problem but the Upload botton does not seem to do anything.

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

When you're using the Office API you're working with COM and COM needs to be properly cleaned up. Even then there is a delay in case you start using it again. It sounds like you might not be cleaning up your COM objects properly. Can you post the code you're using?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This code works fine, and closes the Excel process:
Private Sub Command0_Click()
Dim ExcelApp As Excel.Application

 Set ExcelApp = CreateObject("Excel.application")
 ExcelApp.Workbooks.Open ("ExcelTest.xlsb")
      
 With ExcelApp
      '.Workbooks(1).Sheets.Add after:=ActiveWorkbook.Sheets("sheet1")
      .Workbooks(1).Save
      .Workbooks(1).Close
      .Quit
 End With
 Set ExcelApp = Nothing

End Sub

But uncluding the Add line (or, I think, anything that changes the file content) creates the problem.
If I manually end the task and then rerun this code, with or without the Add, I get runtime error 462.
If I do not manually end the task and then rerun this code I get unexpected errors like "No such interface".

0 Votes 0 ·

But your code is not exception safe so if anything goes wrong between lines 2 and lines 7 Excel is left open and the file locked. I'm only guessing at this point that you might be getting an exception and that is causing it to not be closed properly. For example, when I run your code it throws an object reference error. There appears to be an issue with ActiveWorkbook. After fixing that then I get an error when attempting to save because the file is read only. But if there are no errors then the app is properly cleaned up.

Try this slightly updated code to be error safe.

Set ExcelApp = CreateObject("Excel.application")
 
 On Error GoTo Cleanup
 ExcelApp.Workbooks.Open ("ExcelTest.xlsb")
          
 Set Book = ExcelApp.Workbooks(1)
 With Book
      .Sheets.Add after:=.Sheets("sheet1")
      .Save
      .Close
 End With

Cleanup:
 ExcelApp.Quit
 Set ExcelApp = Nothing
 
 If Err.Number <> 0 Then
    Err.Raise Err.Number
 End If
0 Votes 0 ·
MarkBoucher-6205 avatar image
0 Votes"
MarkBoucher-6205 answered MarkBoucher-6205 rolled back

Using a .bat called .vbs script, I was calling more than one .xlsm file and running up to two VBA macros with each workbook opening. This was done via one .bat file that was started from Task Scheduler. So... task scheduler starts the .bat which starts a .vbs which opens an .xlsm file and runs a couple macros that refresh data in the .xlsm and export to multiple .pdfs in a loop, then open another .xlsm and run some macros in it too and export more .pdfs.

The EXCEL.EXE processes would stack up requiring manual or admin rights executed command window or .bat file "taskkill /F /IM excel.exe" commands. Nothing suggested in many forums seemed to get me out of the quagmire.

All this was done in the background except I still see a "Publishing..." window when the exports to .pdfs occurs. I'm living with that. These process run hourly 24/7.

This would not happen if I ran each macro manually. The EXCEL.EXE process would complete then disappear from Task Manager when I ran the macros or individual .vbs scripts.

Solution:

I put a

 timeout /s 5

in the .bat file between the .vbs calls and

  newHour = Hour(Now())
  newMinute = Minute(Now())
  newSecond = Second(Now()) + 5
  waitTime = TimeSerial(newHour, newMinute, newSecond)
  Application.Wait waitTime

in the macros between "big lifts" such as refreshing imported data or exporting to multiple .pdfs. All issues went away. (Crossing my fingers).

My theory is that one Windows background processes would not quite complete before the next macro or .vbs call was started and this somehow kept Excel from closing.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.