Read Excel file node Opens Excel file


#1

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?


#2

Try using Bumblebee instead.


#3

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.


#4

@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.


#5

@Atkins14 sorry for such a late reply, there isnt any solution for me untill now.


#6

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”


How to close Excel files after creation?
#7

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.


#8

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?


#9

You could find it in the Primary Interop Assemblies like this

in the C:\Windows\assembly folder.


#10

Next question @Raja : ) Where is the Windows Assembly folder?


#11

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.


#12

Awesome Raja! Thank you!