Setting WindowState in Excel

Hi all,

Looking for some more help with trying to work with Excel in Rhino8. I have quite a few older python scripts that utilized Excel that I’m now troubleshooting to work with Rhino8. I have successfully been able to get the Excel object, open a file, retrieve data but having trouble with a line of code that is used to set the WindowState. This previously worked in earlier versions of Rhino.

Please refer to this post for the help I received in opening Excel:
https://discourse.mcneel.com/t/connect-to-excel-using-vb-component-in-rhino-8/173471

Below is a block of code that works up to the last line where it crashes. (#Set Excel Size) I’m running this in the Rhino Python Editor. Any help would be greatly appreciated.

If someone has code that runs in Rhino 8 using Excel I would appreciate seeing it. Thanks.

Eric

#! python 2
import System
progType = System.Type.GetTypeFromProgID("Excel.Application")
excelApp = System.Activator.CreateInstance(progType)
excelApp.Visible = True

#get path to XL_Utils in SEE Rhinoscript
import os
user = os.environ["USERPROFILE"]
path = user + "/SEE RhinoScript/XL_Utils/bin/Release/net48/Utils.dll"
print (path)

import clr
clr.AddReferenceToFileAndPath(path)

from Utils import *

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

#Set Excel Size
excel.WindowState = excel.XlWindowState.xlNormal

Hi @ebunn3, it seems you’re experiencing all the same problems i’ve reported in the other thread you’ve linked to. If you access the Com object like this:

#! python 2
import System
progType = System.Type.GetTypeFromProgID("Excel.Application")
excelApp = System.Activator.CreateInstance(progType)
excelApp.Visible = True

it should open your Excel already, there should be no need for the rest of your code, so you get the same object for excelApp and excel. Therefore i’ve asked in the other thread if above code works and if it opens Excel on your system.

As i’ve reported on the other thread, you cannot access types like it worked in the past using Rhino 7, you need to use int values instead, eg. to set XlWindowState.xlNormal, you would do this:

# Set WindowState based on int values:
# https://learn.microsoft.com/en-us/office/vba/api/excel.xlwindowstate
excelApp.WindowState = -4143

If the purpose of above code line is to bring Excel to front after launching it and this does nothing you would actually have to do this:

excelApp.WindowState = -4140 # XlWindowState.xlMinimized
excelApp.WindowState = -4143 # XlWindowState.xlNormal

There are other ways to do all these things, but i would first like to get an answer, does above code open your Excel and are you able to open a file with it, eg. using below code line:

workBook = excelApp.WorkBooks.Add(file_path)

btw. to avoid crashes and get meaningful errors, put everything into try except blocks and print the exceptions.

_
c.

Clement,

Thank you again for all of the help. The block of code at the top of your reply does in fact open Excel:

It makes sense now that the other code (Marshal2.GetActiveObject(“Excel.Application”)) is for getting a running instance of Excel.

Going back and re-reading the last posting I do see where you mention using integers for setting properties like Window State. I changed it to what you recommended and that worked.

Any other input would be greatly appreciated. I have a lot of code that I developed and distributed 5 or 7 years ago that was working until the upgrade to Rhino8 and then everyone’s macros began to crash and the tools became unusable.

Thanks again.

Eric

Hi @ebunn3,

ok great, so then let’s try something more fancy. Please note that I’ve tried below code only with an Excel version which is not from Office 365 but from Office Pro 2021. The main difference is that we’re trying to import the whole type library and import the Excel namespace. First do a simple check:

#! python 2

import clr
import System
import rhinoscriptsyntax as rs

try:
    guid = System.Guid("00020813-0000-0000-C000-000000000046")
    clr.AddReferenceToTypeLibrary(guid)
except Exception:
    msg = "Excel TypeLib not found. Is Excel installed ?"
    rs.MessageBox(msg, 0+64, "TypeLib Error")
    assert False, msg

import Excel

If this runs without any error, you can put this below to test it, eg. choose an empty Excel file when it asks for an *.xlsx file:

def DoSomething():
    
    file = rs.OpenFileName("Open Excel file", "Excel File (*.xlsx)|*.xlsx||")
    if not file: return

    excelApp = Excel.Application()
    workBook = excelApp.WorkBooks.Add(file)
    workSheet = excelApp.ActiveSheet
    workSheet.Cells[1, "A"].Value = "Hello World"
    workSheet.Cells[1, "B"].Value2 = 123
    excelApp.ActiveWindow.Activate()
    excelApp.Visible = True
    
    # release com objects
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

DoSomething()

If you can run the check above once, you’ll notice something very useful after typing a dot after the Excel statement, it autocompletes in the _EditPythonScript editor with IronPython:

ExcelTypeLibAutocomplete

And instead of using constants (int values) for types, you can access the type names by typing them as shown in the video. Note that i am releasing the com objects at the end of my example script. This is required if you close Excel at the end without having an orphaned instance of it in the task manager.

i hope this helps, if it doesn’t work it could be that the guid of they type library on your system is different.

_
c.

1 Like

Clement,

Everything ran just fine on my machine with Office 365. I was able to access the Excel namespace as well. Really awesome!! One little funny thing though. The last 3 lines in DoSomething:

If I just run the file everything appears to work normally. If I step through the file it crashes at the end while executing this code?

Message: COM object that has been separated from its underlying RCW cannot be used.

Traceback:
  line 20, in DoSomething, "C:\Users\ebunn\OneDrive\Documents\Current Work\RhinoScript\Python Routines\Example Code\Excel\Clement Work Around to Open Excel in R8 - version 2.py"
  line 42, in <module>, "C:\Users\ebunn\OneDrive\Documents\Current Work\RhinoScript\Python Routines\Example Code\Excel\Clement Work Around to Open Excel in R8 - version 2.py"

Eric

Clement,

One more question just so I can wrap my head around this. How did you determine the guid number for Excel? I’ve Googled it and came up with nothing. Hope you don’t mind revealing your secrets.

    guid = System.Guid("00020813-0000-0000-C000-000000000046")
    clr.AddReferenceToTypeLibrary(guid)

Eric

Hi @ebunn3, that is good, then you probably don’t need it. It means your com object has been disposed automatically. If i leave it out on my Systems, i get into trouble once i close Excel, it still seems to run in the taskmanager. To make sure you can try closing Excel, either manually or via code. I recommend putting it into a try except block which passes silently. More info about it can be found here

I’ve just tried various Excel related guids i’ve found in my registry until it worked :wink:

Not at all, hope this is all useful to everyone using Rhino and Excel.
_
c.

Clement,

Thanks again for sharing!

All good stuff. I can’t thankyou enough.

Eric