Connect to Excel using VB Component in Rhino 8

[Thomas_Siegl]

Hi everyone

I have made several scripts depending on comfortable data exchange with excel during the past few years. Up to rhino 7 I was using the following method to connect to an open and active excel application inside grasshoppers vb component:

  ' Get the active Excel application and workbook
  Dim excelApp As Object = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
  Dim activeWorkbook As Object = excelApp.ActiveWorkbook

  ' Get the worksheetA by name
  Dim worksheetA As Object = Nothing

  For Each sheet As Object In activeWorkbook.Sheets
      If sheet.Name = "Name" Then
            worksheetA = sheet
            Exit For
      End If
  Next

  ' If the worksheetA doesn't exist, create a new one
  If worksheetA Is Nothing Then
      worksheetA = activeWorkbook.Sheets.Add(, 
      activeWorkbook.Sheets(activeWorkbook.Sheets.Count), Type.Missing, Type.Missing)
      worksheetA.Name = "Name"
  End If

  ' Select the worksheetA
  worksheetA.Select()

Now that I updated to rhino 8 “System.Runtime.InteropServices.Marshal.GetActiveObject” does not work any more because Rhino 8 is using .net 7.

I have now tried for two days to find a workaround but did not have any success. Connecting to an open Excel application seems quite impossible

Choosing the workbook file in a file dialogue would be fine as well. I managed to get at least as far as choosing one and also tried to dynamically load “Microsoft.Office.Interop.Excel” to open the file:

  Dim excelAssembly As System.Reflection.Assembly
  excelAssembly = System.Reflection.Assembly.Load("Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")

  If R Then
      Dim openFileDialog As New OpenFileDialog()

      ' Set the initial directory to the directory of the active Rhino document (3dm file)
      openFileDialog.InitialDirectory = System.IO.Path.GetDirectoryName(Rhino.RhinoDoc.ActiveDoc.Path)

      ' Set the filter options for Excel files and All Files
      openFileDialog.Filter = "Excel-Files (*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xls;*.xla;*.xlt;*.xlm;*.xlw)|*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xls;*.xla;*.xlt;*.xlm;*.xlw|All Files (*.*)|*.*"

      If openFileDialog.ShowDialog() = DialogResult.OK Then
          ' Get the selected file path
          Dim selectedFilePath As String = openFileDialog.FileName

          ' Now, you can use the dynamically loaded excelAssembly to work with Excel
          Dim excelType As Type = excelAssembly.GetType("Microsoft.Office.Interop.Excel.Application")
          Dim excelApp As Object = Activator.CreateInstance(excelType)

          ' Open the Excel file
          Dim workbooks As Object = excelApp.Workbooks

          ' Open the Excel file using the Add method of the workbooks collection
          Dim workbook As Object = workbooks.Add(selectedFilePath)

      End If
  End If

However the line starting with “Dim excelType As Type” always returns “null” and I can’t seem to find any other approach.

Is there any conveniant (or at least doable) way to still propperly connect to Excel in Rhino 8 using VB (without forcing it to use the old .net framework at startup)?
I also tried pretty much any 3rd party plugin component but none of them reliably writes to already open instances or lets you choose the file in a dialogue you can trigger with a boolean button.

I’d really appreciate any hint.
Thanks in advance!

[clement]

@Thomas_Siegl

Now that I updated to rhino 8 “System.Runtime.InteropServices.Marshal.GetActiveObject” does not work any more because Rhino 8 is using .net 7.

I have now tried for two days to find a workaround but did not have any success. Connecting to an open Excel application seems quite impossible

Hi @Thomas_Siegl, did you solve this ? I have the same issue.

_
c.

[scottd (Scott Davidson)]

This is not a solutions. But be aware that Microsoft has ended development of VB. It looks like Python is the next evolution. So as we look forward it will be important to see.

[clement]

Hi @scottd, i actually don’t use VB, it’s just the same problem i have using IronPython and R8 in it’s default configuration using NETCore.

Do you know a way to access a running Excel instance using IronPython with R8 running under NETCore ?

Using Rhino 7, it was as easy as this:

import System
excelApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

But GetActiveObject does not exist anymore…

I have no way to switch to Python with this project as it needs to run in R7 and R8. I cannot switch R8 to NetFramework as this breaks other R8 PlugIns running on the client’s system.

This fails in R8 in it’s default config too:

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

Adding a direct reference to the location of the dll using clr.AddReferenceToFileAndPath makes the import possible, but then i cannot access Excel.ApplicationClass.
_
c.

[Thomas_Siegl]

Hi Clement

I only found a partial solution.
I add “Imports Microsoft.Office.Interop.Excel” to the code and also referenced “c:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll” and “c:\Windows\assembly\GAC_MSIL\office\15.0.0.0__71e9bce111e9429c\OFFICE.DLL” via “Manage Assemblies …” from the vb-component’s context menu. You have to unregister the Shfusion.dll shell extension first though, because otherwise windows won’t allow you to browse to these folder. Then I could at least open a fresh excel

xlApp = New Microsoft.Office.Interop.Excel.Application()

and open an existin file

xlWorkBook = xlApp.Workbooks.Open(excelFilePath)

I never managed to find a way to connect to an open and active Excel Workbook

[Bill_Cook]

@clement and @Thomas_Siegl

There is no System.Runtime.InteropServices.Marshal.GetActiveObject in .net 7

The workaround is here: excel - No definition found for GetActiveObject from System.Runtime.InteropServices.Marshal C# - Stack Overflow

Let us know if that doesn’t solve the issue.
Bill Cook /)

[clement]

Hi @Bill_Cook, thank you for the link. It has also been posted earlier here.

@Bill_Cook

Let us know if that doesn’t solve the issue.

My problem is, how to write this with IronPython ? Can you give an example ?

To summarize my findings, when Rhino 8 runs using NETFramework which can be set using _SetDotNetRuntime, this still works as in Rhino 7:

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

but if Rhino 8 runs in it’s default configuration using NETCore i get this error:

Message: Could not add reference to assembly Microsoft.Office.Interop.Excel

I am able to start a new Excel using this workaround when i run Rhino 8 in default mode (NETCore):

progType = System.Type.GetTypeFromProgID("Excel.Application")
excelApp = System.Activator.CreateInstance(progType)
excelApp.Visible = True

but using this type of binding, i do not have autocompletion nor do i get meaningful errors. The problem arises if you have to set special types like:

xlsheet.Visible = Excel.XlSheetVisibility.xlSheetVisible

So using Activator.CreateInstance is a workaround which forces me to use ints instead of types in many cases.

Next i’ve tried to directly add a reference to the dll which is found here:

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

using this code:

The import works, i get autocompletion but Excel somehow does not have Application nor ApplicationClass. If i try to access it like in Rhino 7:

import Microsoft.Office.Interop.Excel as Excel
excelApp = Excel.ApplicationClass()

i’m getting this Error:

Message: ‘Microsoft.Office.Interop.Excel’ object has no attribute ‘ApplicationClass’

I’m using Microsoft Office LTSC Profesional Plus 2021 on Windows 10. After trying out various things, i would say that R8, when run with default (NETCore) configuration, killed all my Excel libraries.

thank you,
c.

[clement]

@Thomas_Siegl

I only found a partial solution.

Hi @Thomas_Siegl, thank you for posting your partial solution. I can say that once i added a reference to the path of OFFICE.DLL i can access ApplicationClass and it appears in autocompletion:

@Thomas_Siegl

I never managed to find a way to connect to an open and active Excel Workbook

Same here, that’s the problem i am trying to solve.

thanks,
c.

[Bill_Cook]

Ahhhhh. OK. I am personally out of town for the next week but will try to look at this while I am gone and see what I can figure out.

cc: @ehsan.anvary @curtisw

[curtisw (Curtis Wensley)]

Hi @clement and @Thomas_Siegl,

One way to do this would be to create a separate .dll that you can use that implements GetActiveObject, then use clr.AddReference() to use it.

I’ve created a simple .dll that does it, which includes the compiled .dll (in bin/Release/net48):

Utils.zip (4.7 KB)

You can use it like so:

import clr
clr.AddReference("Utils")

from Utils import *

app = Marshal2.GetActiveObject("Excel.Application")

Hope this helps!

[clement]

Thank you @curtisw,

i’ve tested it and when i use clr.AddReferenceToFileAndPath it worked!

Is there a chance that this gets a fixed place in the Rhino 8 ecosystem? I would like to suggest that this useful function gets part of RhinoWindows which can be imported without a path using:

import clr
clr.AddReference("RhinoWindows")
import RhinoWindows

It would make it much easier to publish code which requires direct access to a COM object with Rhino 8 running under NETCore.

_
c.

[ebunn3]

Curtis,

Where do you install this .dll? Can you walk me through the proper installation of the zipped files?

Eric Bunn

[clement]

@ebunn3,

put the dll file in the same folder as your script. Note that this dll is only required if you want to connect to a running instance of Excel as this provides the Marshal2.GetActiveObject method.

_
c.

[curtisw (Curtis Wensley)]

@ebunn3

Where do you install this .dll? Can you walk me through the proper installation of the zipped files?

Hey @ebunn3, with EditPythonScript you would put it somewhere in the Tools > Options > Module Search Paths, or add a new path to where it lives.

With the ScriptEditor the module search paths only refer to python scripts not .NET assemblies, so you need to load the assembly manually by path. In IronPython 2 you can do this:

#! python 2
import clr
clr.AddReferenceToFileAndPath("Path\To\Utils.dll")

from Utils import *
app = Marshal2.GetActiveObject("Excel.Application")

For Python 3, it only has AddReference so you need to be a bit more tricky:

#! python3
from System.Reflection import Assembly
Assembly.LoadFile(r"Path\To\Utils.dll")

import clr
clr.AddReference ("Utils")

from Utils import *
app = Marshal2.GetActiveObject("Excel.Application")

@eirannejad added RH-85674 to support the same clr.AddReferenceToFileAndPath API in Python 3 in the future to make this a wee easier.

You could probably also figure out the path to the script to base it off of that to make it a bit more portable, if you need to share the script/dll with others.

Hope this helps!

[djordje]

Hi @curtisw ,
So the Utils.dll you attached can be used to obtain any active application’s COM object? Only only Excel’s?

[ebunn3]

This helps. Thank you. I’ll try it out.

Eric

[curtisw (Curtis Wensley)]

@djordje

So the Utils.dll you attached can be used to obtain any active application’s COM object? Only only Excel’s?

It can be used for any COM object, it is not limited to Excel and just is a re-implementation of Marshal.GetActiveObject() which is no longer available in .NET 7+

[ebunn3]

Curtis,

Some trouble. I can sucessfully get access to Marshal2 but it crashes when it reaches app = Marshal2.GetActiveObject(“Excel.Application”). The error message states: “Message: Operation unavailable (0x800401E3 (MK_E_UNAVAILABLE))”

I have Office 365 installed on my computer.

import os
user = os.environ["USERPROFILE"]
#C:\Users\ebunn\SEE RhinoScript
path = user + "/SEE RhinoScript/Utils/bin/Release/net48/Utils.dll"
print (path)

#! python 2
import clr
clr.AddReferenceToFileAndPath(path)

from Utils import *

app = Marshal2.GetActiveObject("Excel.Application")
print(app)

Thanks for the help.

Eric

[ebunn3]

Clement

You’re saying that Excel needs to be running in the background for this to work?

Eric

[clement]

Yes, that is what GetActiveObject is made for.

_
c.