pythonexcelcomwin32comcomtypes

Pass dictionary to Excel macro using win32com and comtypes

I am using Python to unit test Excel macros. When testing the following macro I need to pass a dictionary object to it.

code in testDict.xlsm:

Function GetItemCount(dict As Variant) As Integer
    GetItemCount = dict.Count
End Function

My test code looks like this and is based on test_dict.py in the comtypes project:

test_comtypes_w_dict.py

from comtypes.client import CreateObject
import win32com.client as win32

d = CreateObject("Scripting.Dictionary", dynamic=True)

d.Add("Test", "An item")

filename = 'testDict.xlsm'
xl = win32.Dispatch("Excel.Application")
wb = xl.Workbooks.Open(filename)
count = xl.Application.Run(filename+'!Module1.GetItemCount', d)

assert count == 1

When running the test, TypeError is raised.

Traceback (most recent call last):
    File "C:\Users\[..]\test_comtypes_w_dict.py", line 11, in <module>
        count = xl.Application.Run(filename+'!Module1.GetItemCount', d)
    File "C:\Users\[..]\AppData\Local\Temp\gen_py\3.9\00020813-0000-0000-C000-000000000046x0x1x9.py", line 44654, in Run
        return self._ApplyTypes_(259, 1, (12, 0), ((12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)), 'Run', None,Macro
File "C:\Users\[..]\venv\lib\site-packages\win32com\client\__init__.py", line 467, in _ApplyTypes_
        self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args), TypeError: Objects for SAFEARRAYS must be sequences (of sequences), or a buffer object.

I have tried removing dynamic=True from the call to CreateObject. Then the test runs, but I get an exception in Excel:

Run-time error '424':

Object required

When debugging in the Excel VBA IDE and running TypeName(dict), the result is "Variant()".

How do I pass the dictionary to the macro in a way that it is properly recognized?

As a workaround I will attempt to generate the dictionary in a macro, return it to Python and pass it to the macro that I want to test. I would however like to avoid this convoluted method if possible.

Solution

  • The code above is mixing two different libraries for creating COM objects.

    Replace

    d = CreateObject("Scripting.Dictionary", dynamic=True)

    with

    d = win32.Dispatch('Scripting.Dictionary')

    and dispense with comtypes, unless it is needed for some other feature.