Hi all,
Currently we are migrating our scripts from IronPython2 to CPython3.
I want to run the scripts, and once the run is completed I want the excel files close automatically. Please see the figure below.
When I run it I get the following error: “AttributeError : module ‘clr’ has no attribute ‘AddReferenceByName’ [’ File “”, line 13, in \n’]”
from System.IO import Directory
import re
import time
import sys
sys.path.append('C:\Program Files (x86)\IronPython 2.7\Lib')
import subprocess
import os
import errno
import csv
import clr
from System import Environment
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
true_false = IN[0]
Bestand = IN[1]
for a in range(len(Bestand)):
def excel_close(_bool):
if _bool:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
#xlApp.Workbooks("Excel openen en sluiten.xlsx")
Tesluiten = xlApp.Workbooks(Bestand[a])
#xlApp.Workbooks(Bestand)
#xlApp.Workbooks.Close()
Tesluiten.Close()
#xlApp.Quit()
Check = "Success"
else:
Check = "Set the boolean to True"
return Check
OUT = excel_close(true_false)
Could someone help me to solve the issue with the script?
Thank you in advance.
Best regards,
Stefany Salguero
Hello,
I can’t see your inputs but does this work?
from System.IO import Directory
import re
import time
import sys
#sys.path.append('C:\Program Files (x86)\IronPython 2.7\Lib')
import subprocess
import os
import errno
import csv
import clr
from System import Environment
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
true_false = IN[0]
Bestand = IN[1]
for a in range(len(Bestand)):
def excel_close(_bool):
if _bool:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
#xlApp.Workbooks("Excel openen en sluiten.xlsx")
Tesluiten = xlApp.Workbooks(Bestand[a])
#xlApp.Workbooks(Bestand)
#xlApp.Workbooks.Close()
Tesluiten.Close()
#xlApp.Quit()
Check = "Success"
else:
Check = "Set the boolean to True"
return Check
OUT = excel_close(true_false)
Hi @jw.vanasselt,
Thank you for your reply. I have tried the code but it is still not working properly. The error that I get now is:
" TypeError : object is not callable [’ File “”, line 37, in \n’, ’ File “”, line 28, in excel_close\n’]"
from System.IO import Directory
import re
import time
import sys
#sys.path.append('C:\Program Files (x86)\IronPython 2.7\Lib')
import subprocess
import os
import errno
import csv
import clr
from System import Environment
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
true_false = IN[0]
Bestand = IN[1]
for a in range(len(Bestand)):
def excel_close(_bool):
if _bool:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
#xlApp.Workbooks("Excel openen en sluiten.xlsx")
Tesluiten = xlApp.Workbooks(Bestand[a])
#xlApp.Workbooks(Bestand)
#xlApp.Workbooks.Close()
Tesluiten.Close()
#xlApp.Quit()
Check = "Success"
else:
Check = "Set the boolean to True"
return Check
OUT = excel_close(true_false)
Excel openen en sluiten forum.dyn (39.1 KB)
Excel openen en sluiten.xlsx (14.4 KB)
Could you help me to check the script?
Thank you in advance.
Best regards,
Stefany
Something has change this works fine in IronPython2.
But I don’t know what it is…
Hi,
In IronPython2 the script works properly. The problem is that I want to change the script to CPython3, but I get the errors.
Best regards,
Stefany
Hi @jw.vanasselt
we know that is works with ironpython2, but as aknowledge by the program this won’t get any updates anymore.
that’s why we are changing to Cpython3.
@sanzoghenzo @ina or others, hope one of you have already found what to change?
Many thanks in advance
Hi there,
do you have the possibility to install an external python 3 environment?
If yes, it might be best if you switch to openpyxl or pandas to read/write directly to excel files without the need of Excel in the first place.
Of course you will need to replace the ImportExcel and ExportToExcel nodes with some custom script.
Anyway, the problem you have is that you can’t retrieve a workbook by its name; xlApp.Workbooks needs a number to access the n-th opened workbook.
You need to change the script to check if the opened workbook have a name that is in the given list.
Here is my take with plenty pf comments to help you understand what I did.
# No need for all that useless imports, keep it clean and simple
import clr
from System.Runtime.InteropServices import Marshal
clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
# the import from an external library must occur after you load it with clr.AddReference
from Microsoft.Office.Interop import Excel
# variables in python are in snake_case, in C# are camelCase; while PascalCase names are used for classes (both python and C#) and C# attributes.
# It seems like a style choice, but it helps to better understand the code along the way...
run = IN[0]
files = IN[1]
# The function made no sense here: defined in a loop, it keeps retrieving the excel application instead of reusing it... getting rid of it altogether!
# "short-circuiting" in the case that run is False
if not run:
OUT = "Set the boolean to True"
else:
# getting the excel app object only once
xlApp = Marshal.GetActiveObject("Excel.Application")
# no need to make it visible, you also gain some milliseconds
xlApp.DisplayAlerts = False
# loop through all the workbooks to check their names
for wb in xlApp.Workbooks:
if wb.name in files:
# we found a match, closing it
wb.Close()
OUT = "Success"
Forgive me if I sound harsh, but I see from your code that you lack some fundamentals in (python) programming. Copy-pasting your way through a problem will not give you the knowledge you need to solve the next one you encounter.
@sanzoghenzo
thanks for your reply.
We did follow your code, but unfortunately we have a _comobject error. see picture below.
ad[1]
you are not sounding hars. it’s true that our codes are not written from an IT point of view, i’m an engineer who teached myself to create python codes.
Thanks in advance
Gr Edward
Hello,
here is a solution with casting each object in the good interface
import sys
import clr
import System
clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
def excel_close(namefile):
xlApp = Excel.Application(Marshal.GetActiveObject("Excel.Application"))
allWorkbooks = Excel.Workbooks(xlApp.get_Workbooks())
lst_WkbsName = [Excel.Workbook(x).get_Name() for x in allWorkbooks]
if namefile in lst_WkbsName:
Tesluiten = Excel.Workbook(allWorkbooks.get_Item(namefile))
Tesluiten.Close()
xlApp.Quit()
OUT = [excel_close(v) for v in IN[0]]
the 2 engines each have advantages
1 Like
@c.poupin thanks for your reaction.
We used your script, it works but gives an error see picture below. What can couse this error.
Dynamo file is attached.
Excel openen en sluiten V0_3_RSA_2022.dyn (70.2 KB)
I try our script but I could not reproduce this error
Try this version
import sys
import clr
import System
clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
def excel_close(lst_namefile):
xlApp = Excel.Application(Marshal.GetActiveObject("Excel.Application"))
print(xlApp)
allWorkbooks = Excel.Workbooks(xlApp.get_Workbooks())
print([x for x in allWorkbooks])
lst_WkbsName = [Excel.Workbook(x).get_Name() for x in allWorkbooks]
for namefile in lst_namefile:
if namefile in lst_WkbsName:
Tesluiten = Excel.Workbook(allWorkbooks.get_Item(namefile))
Tesluiten.Close()
xlApp.Quit()
toList = lambda x : x if isinstance(x, list) else [x]
OUT = excel_close(toList(IN[0]))
