Closing all Excel processes in python script

excel
python

#1

Like many other people, I am having trouble getting rid of all the excel processes when I run excel operations through a python script. Now I am down to one lingering process.

I did an earlier post, but I have since done some more testing, and I have manage to reduce the script.
Some of the things I tested can be seen in the old post here: Excel background process not closing after running python script

My current script is:

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
ex.ScreenUpdating = False

workbook = ex.Workbooks.Open(path)
ws = workbook.Worksheets(sheetname)

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

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

solver = sol.Workbooks.Open(solverPath)

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

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

else:
ws.Range[incell].Value2 = data[i]
ex.Run(macroName)

#The workbook and solver is closed
workbook.Close(True)

solver.Close(False)
sol.ScreenUpdating = True

sol.Quit()

Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(solver)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(sol)

#The workbook is reopened to collect output

workbook = ex.Workbooks.Open(path)
ws = workbook.Worksheets(sheetname)

out_af=ws.Range[outcell_start,outcell_end].Value2
out_list.append(out_af)

workbook.Close(True)
ex.ScreenUpdating = True

ex.Quit()

Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(ex)

OUT=out_list

My guess is that there is a COM object which I have not released, but I can’t figure out which.


#2

I don’t know if I dare say this, because so many times I’ve thought I solved the problem, only for lingering processes to appear the next time I test it - But for now it seems that I have gotten it to work.
The problem seems to be opening and closing excel applications multiple times in one script. So I have reworked the structure of the script, and I think I actually prefer it.

For some obscure reason though, when I loop it 4 times (and potentially divisible by 4), one process pops up.

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=[]
	

ex = Excel.ApplicationClass()
ex.Visible = False
ex.DisplayAlerts = False
ex.ScreenUpdating = False

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

solver = sol.Workbooks.Open(solverPath)


for i in range(len(data)):
	
	workbook = ex.Workbooks.Open(path)
	ws = workbook.Worksheets(sheetname)

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

	if ws.Range[incell].Value2 == data[i]:
		#Effectively -> Do nothing
		out_af=out_bf
	
	else:
		ws.Range[incell].Value2 = data[i]
		ex.Run(macroName)		
	
	workbook.Save()
	workbook.Close(False)
	
	#First Session is closed


	#A new session is started

	workbook_out = ex.Workbooks.Open(path)
	ws_out = workbook_out.Worksheets(sheetname)

	out_af=ws_out.Range[outcell_start,outcell_end].Value2
	out_list.append(out_af)

	workbook_out.Save()
	workbook_out.Close(False)
	
	
solver.Close(False)
sol.ScreenUpdating = True
sol.DisplayAlerts = True
	
ex.ScreenUpdating = True
ex.DisplayAlerts = True

sol.Quit()
ex.Quit()

Marshal.ReleaseComObject(ws)
Marshal.ReleaseComObject(workbook)	
Marshal.ReleaseComObject(ws_out)
Marshal.ReleaseComObject(workbook_out)
Marshal.ReleaseComObject(solver)
Marshal.ReleaseComObject(sol)	
Marshal.ReleaseComObject(ex)
	
	
	
OUT=out_list