here is an example to extract values from controls (OLE Objects)
python code (compatible all engines)
import clr
import sys
import System
from System import Array
from System.Collections.Generic import List, Dictionary, IDictionary
clr.AddReference("System.Reflection")
from System.Reflection import BindingFlags
from System.Runtime.InteropServices import Marshal
clr.AddReference("System.Core")
clr.ImportExtensions(System.Linq)
xls_filePath = IN[0]
xls_SheetName = IN[1]
dict_values = {}
systemType = System.Type.GetTypeFromProgID("Excel.Application", True)
try:
ex = System.Activator.CreateInstance(systemType)
except:
methodCreate = next((m for m in clr.GetClrType(System.Activator)\
.GetMethods() if "CreateInstance(System.Type)" in m.ToString()), None)
ex = methodCreate.Invoke(None, (systemType, ))
ex.Visible = False
workbooks = ex.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty , None, ex, None)
workbook = workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod , None, workbooks, (xls_filePath, ))
worksheets = workbook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty , None, workbook, None)
#
ws = worksheets.GetType().InvokeMember("Item", BindingFlags.GetProperty , None, worksheets, (xls_SheetName,))
#
# get all ole ActiveX objects
ole_objects = ws.GetType().InvokeMember("OLEObjects", BindingFlags.InvokeMethod , None, ws, None) #sheet.OLEObjects
ole_objects_count = ole_objects.GetType().InvokeMember("Count", BindingFlags.GetProperty , None, ole_objects, None)
print(f"{ole_objects_count=}")
for i in range(ole_objects_count):
#print(i)
ole_object = ole_objects.GetType().InvokeMember("Item", BindingFlags.GetProperty , None, ole_objects, (i + 1,))
ole_name = ole_object.GetType().InvokeMember("Name", BindingFlags.GetProperty , None, ole_object, None)
prog_id = ole_object.GetType().InvokeMember("progID", BindingFlags.GetProperty , None, ole_object, None) # progID
embedded = ole_object.GetType().InvokeMember("Object", BindingFlags.GetProperty , None, ole_object, None) # ole.Object
name = embedded.GetType().InvokeMember("Name", BindingFlags.GetProperty , None, embedded, None)
value = embedded.GetType().InvokeMember("Value", BindingFlags.GetProperty , None, embedded, None) # ole.Value
dict_values[name]={"ole_name" : ole_name, "value" : value}
#
# get all ole Shape objects
ole_shapes = ws.GetType().InvokeMember("Shapes", BindingFlags.GetProperty , None, ws, None) #sheet.GetProperty
ole_shapes_count = ole_shapes.GetType().InvokeMember("Count", BindingFlags.GetProperty , None, ole_shapes, None)
print(f"{ole_shapes_count=}")
for i in range(ole_shapes_count):
#print(i)
ole_shape = ole_shapes.GetType().InvokeMember("Item", BindingFlags.InvokeMethod , None, ole_shapes, (i + 1,))
ole_name = ole_shape.GetType().InvokeMember("Name", BindingFlags.GetProperty , None, ole_shape, None)
ole_format = ole_shape.GetType().InvokeMember("OLEFormat", BindingFlags.GetProperty , None, ole_shape, None)
embedded = ole_format.GetType().InvokeMember("Object", BindingFlags.GetProperty , None, ole_format, None)
name = embedded.GetType().InvokeMember("Name", BindingFlags.GetProperty , None, embedded, None)
try:
value = embedded.GetType().InvokeMember("Value", BindingFlags.GetProperty , None, embedded, None) # ole.Value
dict_values[name]={"ole_name" : ole_name, "value" : value}
except :
pass
# close and dispose
workbooks.GetType().InvokeMember("Close", BindingFlags.InvokeMethod , None, workbooks, None)
Marshal.ReleaseComObject(workbooks)
ex.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod , None, ex, None)
Marshal.ReleaseComObject(ex)
OUT = dict_values
But I agree with @jacob.small that it would be preferable to write the values of the controls/forms in another Excel sheet (via a macro), and then read the results with OpenXML.
