Hello All,
Whenever I try to run the “Read.Excel” node in dynamo it opens the excel file along with it. Is there a way to stop opening the excel file?
Try using Bumblebee instead.
2 Likes
Thanks @Konrad_K_Sobon I use Bumblebee for my personal use, but when i have to give the visual code to others who doesn’t have the package i either should ask them to install it or use the default nodes in dynamo. In the latter case I came across this issue so i was wondering why.
@Raja did you ever fine a solution to this? i am having same issue with my team not always having bumblebee installed - so using the built in read / write excel would be be easier for sharing scripts.
@Atkins14 sorry for such a late reply, there isnt any solution for me untill now.
i have put together a bit of python code which closes the active workbook, you should be able to copy and paste the below code in. It uses an if statement as a trigger. Its a bit messy as you need to use this everytime a spreadsheet is opened. Ideally, would want this to run right at the end and close specific excel files. Hope it helps.
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
clr.AddReferenceByName(‘Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’)
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import ApplicationClass
from System.Runtime.InteropServices import Marshal
if “Completed” in IN[0]:
ex = Excel.ApplicationClass()
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.Workbooks.Close()
xlApp.Quit()
OUT = “Success”
3 Likes
Hi @Atkins14,
Took so long for me to return to this forum. many thanks for stitching the code, with my basic python knowledge I have edited yours a bit with True or False statement , here below.
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
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import ApplicationClass
from System.Runtime.InteropServices import Marshal
true_false = IN[0]
def excel_close(_bool):
if _bool:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.Workbooks.Close()
xlApp.Quit()
Check = "Success"
else:
Check = "Set the boolean to True"
return Check
OUT = excel_close(true_false)
Thanks once again for this working solution.
7 Likes
https://stackoverflow.com/questions/25289149/how-can-i-manipulate-excel-data-in-python
import clr
clr.AddReferenceByName(‘Microsoft.Office.Interop.Excel, Version=11.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’) from Microsoft.Office.
Interop import Excel
Does anyone have the excel 2016 PublicKeyToken?
1 Like
1 Like
Next question @Raja : ) Where is the Windows Assembly folder?
Hi @Ron_Allen, the link seems to be prefixed with http so it didnt work in the last post. Its there in the C:\Windows\assembly folder.
1 Like
Awesome Raja! Thank you!
1 Like
My question is, why does excel open in the first place? Does it need to be open? I saw a couple of times that when I tried to run my graph and it started but never finished. I ended up closing Dynamo and that is when I saw that Excel was open. It (excel) appeared to stall Dynamo when it (excel) was open. I also had Dynamo stall when I was creating worksets, I had the workset dialog box open so I could see it change. Closed the Dialog box and it finished creating the worksets.
Excel does not need to be open. Somewhere in the implementation of the Excel.ReadFromFile node, the Excel.ApplicationClass has its Visible property set to true. It makes sense why this would be the case for the WriteToFile node, as it displays the file that was created, but it isn’t necessary for the ReadFromFile node to function. Regarding the stalling while creating worksets, Dynamo was probably not able to modify the document until the dialog box was closed, however I think this is separate from your issue with Excel preventing Dynamo from executing.
1 Like
Thank you. I was able to get my graph to run successfully (with the exception of Excel opening)
it maybe that I am not on the most current dynamo version because I am still in Revit 2018.3 so I tend to only load the packages that do not give me the warning about them being created in a newer version than I am running. I will find myself in these forums a lot more now that I am involved with our companies standardization group.
I have only been at this for about a week (12 hours) lol
David
Oh and my issue wasn’t that excel was open. Not sure what cause the initial failure to complete (probably that I had the worksets dialog box open) I just wish that if excel has to be open to import the data that it would not open on top everything. I can minimize it and continue my selection and successfully complete the task. I will re-post my issue as a new thread and try to get all the correct versions of what I am using noted.
Thanks again for the help.
I just found out what you can also do is node to code the Data.ImportExcel node (do this by selecting the Data.ImportExcel node, right clicking in free space away from the node and then selecting node to code)
Then just changing the last argument on the method to false. This stops Excel from opening at all.
I know this is already solved but I reckon this is abit cleaner.
Cheers, Pete
I tried placing your script at the end, it doesnt seem to close the excel. ANything Im doing wrong from my side?
@technitutors
To close an open excel-file via python use this:
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
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import ApplicationClass
from System.Runtime.InteropServices import Marshal
has_run = IN[0]
true_false = IN[1]
def excel_close(_bool, has_run):
if _bool == False and has_run == True:
xlApp = Marshal.GetActiveObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.Workbooks.Close()
xlApp.Quit()
Check = "Success"
else:
Check = "Set the boolean to True"
return Check
OUT = excel_close(true_false, has_run)
The variable has_run tells if the excel-file is ready to be closed. true_false tells the code if it should even run or not.
1 Like
@Raja for posting this code. It really helps me alot.