Excel background process not closing after running python script

Developers
,
1

Hi

I feel like I have tried all combinations of the internets many suggestions for my problem, so now I am hoping that any of the masterminds in here might be able to help me.

My problem is closing all of the excel processes after working on an excel sheet.
I can see that the main problems that other people have are:

  • Not closing the workbooks properly
  • Not releasing all COM objects

In the version I have now, I have saved all intermediate excel-calls as variables in order to be able to release them, but still I am left with Excel.exe processes.

I’ll just post my script for now - I hope that someone might have a suggestion.
I kept some of the alternative methods I’ve tried in as text.

import clr
clr.AddReferenceByName(‘Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’)
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

def tolist(obj1):
if isinstance(obj1,list):
return obj1
else:
return [obj1]

path = IN[0]
macroName = IN[1]
solverPath = IN[2]
data = tolist(IN[3])
incell = IN[4]
outcell_start = IN[5]
outcell_end = IN[6]
sheetname = IN[7]

out_list=

for i in range(len(data)):
ex = Excel.ApplicationClass()
ex.Visible = False
ex.DisplayAlerts = False

sol = Excel.ApplicationClass()
sol.Visible = False
sol.DisplayAlerts = False

#solAddIn=ex.AddIns(“Solver Add-in”)
#solAddIn.Installed = True
ex.AddIns(“Solver Add-in”).Installed = True

workbooks= ex.Workbooks
workbook = workbooks.Open(path)

sol_workbooks=sol.Workbooks
solver = sol_workbooks.Open(solverPath)

worksheets=workbook.Worksheets
ws = worksheets(sheetname)

out_range=ws.Range[outcell_start,outcell_end]
out_bf=out_range.Value2

in_range=ws.Range[incell]
if in_range.Value2 == data[i]:
#Effectively → Do nothing
out_af=out_bf

else:
in_range.Value2 = data[i]

  ex.Run(macroName)

solver.Close(False)
#sol_workbooks.Close()
workbook.Close(True)
#workbooks.Close()

sol.Application.Quit()

#Marshal.ReleaseComObject(solAddIn)
Marshal.ReleaseComObject(in_range)
Marshal.ReleaseComObject(out_range)
Marshal.ReleaseComObject(worksheets)
Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(sol_workbooks)
Marshal.ReleaseComObject(solver)
Marshal.ReleaseComObject(workbooks)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(sol)

#First Session is closed

#A new session is started

workbooks= ex.Workbooks
workbook = workbooks.Open(path)

worksheets=workbook.Worksheets
ws = worksheets(sheetname)

out_range=ws.Range[outcell_start,outcell_end]
out_af=out_range.Value2

out_list.append(out_af)

workbook.Close(True)
#workbooks.Close(True)

ex.Application.Quit()

Marshal.ReleaseComObject(out_range)
Marshal.ReleaseComObject(worksheets)
Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(workbooks)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(ex)

OUT=out_list

I saw that Konrad wrote in this post https://forum.dynamobim.com/t/excel-memory-issues/5566/2 that ex.Application.Quit() is only necessary, when you havn’t closed the workbooks properly - But since I have apparantly not managed to do that, I have kept it in :wink:

I should add that the script actually works, though only 4-5 of the lines performing any real actions.

2

The reason that I close and open the same workbook is that the outputs will otherwise be read before my excel macro has stopped running.