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?
I can not use VBA in Access to close an Excel background process that appears under certain circumstances..
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 28.999999999999996%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETR%3C/text%3E%3C/svg%3E)
Terry Richter
1
Reputation point
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.
2 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
Michael Taylor 47,711 Reputation points2021-12-28T16:11:01.463+00:00 -
Terry Richter 1 Reputation point
2021-12-29T09:30:32.44+00:00 This code works fine, and closes the Excel process:
Private Sub Command0_Click()
Dim ExcelApp As Excel.ApplicationSet 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 = NothingEnd 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". -
Michael Taylor 47,711 Reputation points
2021-12-29T16:48:15.853+00:00 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
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 57.00000000000001%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMB%3C/text%3E%3C/svg%3E)
Mark Boucher 1 Reputation point2022-05-20T20:16:27.057+00:00 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 5in 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 waitTimein 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.