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.