Accessing Microsoft Excel

[Cey42]

I am trying to use the scripting to read data from Excel. My problem is I cannot even get basic access to Excel. Here is what I have tried

Code: Select all

import clr
clr.AddReference("Microsoft.Office.Interop.Excel")

That fails with "Could not add reference to assembly Microsoft.Office.Interop.Excel"

Then I tried using openpyxl

Code: Select all

import sys
sys.path.append('C:\\Program Files (x86)\\Microsoft Visual Studio\\Shared\Python37_64\\Lib\\site-packages')
import openpyxl
workbook = openpyxl.Workbook()

That gives the following error "AttributeError: 'ColorDescriptor' object has no attribute 'to_tree'"

Any ideas of what I am doing wrong or other things to try?

Thanks,
Cey

[admin]

Hi,

not something I have tried before from IronPython...

My first thought for the first issue is that clr.AddReference() may be expecting the assembly to already be loaded, so may fail if the interop assembly is not loaded. Maybe you need to point it to the dll on disk? There is some info here : https://ironpython.net/documentation/dotnet/dotnet.html

cherees,

Robin

[Cey42]

I have made some progress on this but now have run into another issue. I am trying to connect to the current running instance of Excel. I do not want to create a new instance. Here is the current code

Code: Select all

import sys
import clr
clr.AddReferenceToFileAndPath(r"C:\Program Files (x86)\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll")
import Microsoft.Office.Interop.Excel as Excel
from System.Runtime.InteropServices import Marshal
Excel = Marshal.GetActiveObject("Excel.Application")

I now get the error: AttributeError: 'type' object has no attribute 'GetActiveObject'

From searching around, this seems to be because the IronPython SharpCap is using .NetCore vs .NetFramework which has GetActiveObject. I have tried other solutions but run into the same issue.

Not sure if the above info gives any ideas of what else I can try.

[admin]

Hmm,

how about something like this : https://stackoverflow.com/a/65496277/653430

The solution there may not be possible easily in pure IronPython, but could easily be put into a small C# DLL that is then used from IronPython.

cheers,

Robin

[Cey42]

After more searching, I finally found a way to do this. Here is the code for reference

Code: Select all

import clr
clr.AddReferenceToFileAndPath(r"C:\Program Files (x86)\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll")
import Microsoft.Office.Interop.Excel as Excel
from Microsoft.Office.Interop.Excel import Application
from System.Runtime.InteropServices import Marshal

excel_app = Marshal.BindToMoniker("C:\\temp\\test.xlsx")

wb = excel_app.Application.ActiveWorkbook
sh = wb.ActiveSheet

...

excel_app  = None