Calling vba macro from python with unknown number of arguments

Question

The goal is to have a reusable python function which calls using win32.com a macro from VBA Excel, and do so using **kwargs, or other method that can pass unknown number of arguments.

No specific questions were using **kwargs and explicit win32.com, although some resembled questions were found, none have answers accepted, even if not exactly similar.
What follows are some resembled but not similar issues:

using python

def run_vba_macro(str_path, str_modulename, str_macroname, **kwargs):
    if os.path.exists(str_path):
        xl=win32com.client.DispatchEx("Excel.Application")
        wb=xl.Workbooks.Open(str_path, ReadOnly=0)
        xl.Visible = True
        if kwargs:
                xl.Application.Run(os.path.basename(str_path)+"!"+str_modulename+'.'+str_macroname,
                                          **kwargs)
        else:
              xl.Application.Run(os.path.basename(str_path)
                                          +"!"+str_modulename
                                          +'.'+str_macroname)
        wb.Close(SaveChanges=0)
        xl.Application.Quit()
        del xl

#example
kwargs={'str_file':r'blablab'}
run_vba_macro(r'D:\arch_v14.xlsm',
              str_modulename="Module1",
              str_macroname='macro1',
              **kwargs)
#other example
kwargs={'arg1':1,'arg2':2}
run_vba_macro(r'D:\arch_v14.xlsm',
              str_modulename="Module1",
              str_macroname='macro_other',
              **kwargs)

with VBA

Sub macro1(ParamArray args() as Variant)
    MsgBox("success the str_file argument was passed as =" & args(0))
End Sub

Sub macro_other(ParamArray args() as Variant)
    MsgBox("success the arguments have passed as =" & str(args(0)) & " and " & str(args(1)))
End Sub

Expected result is a MessageBox in VBAs:

success the str_file argument was passed as = blablab
success the arguments have passed as = 1 and 2

David Zemens · Accepted Answer · 2019-10-01 16:34:31Z

4

in python, kwargs is a dict, so it is not something that can be passed over COM. Pass the kwargs.values as an *args array, like so:

params_for_excel = list(kwargs.values())

xl.Application.Run(os.path.basename(str_path)+"!"+str_modulename+'.'+str_macroname,
                                      *params_for_excel)

answered Oct 1, 2019 at 16:34

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

Francisco Costa Over a year ago

very good i think this answers well. Remember to fix the starred expression: should be *params_for_excel, cheers

David Zemens Over a year ago

@FranciscoCosta that doesn't seem necessary when I run it, but if you'd like to elaborate on why you think that's required, please do so and I'll consider the edit :)

Francisco Costa Over a year ago

Hmm interesting, so ParamArray can work with python positional args, *args. Sending tuples (ie *params_for_excel, works also) do you think there is any difference in using one or another? Ie passing a tuple or passing positional args into ParamArray VBA arguments.

David Zemens Over a year ago

I don't think any difference -- *args is a tuple. Adding the comma does not change that. *params_for_excel is a tuple whether there is a trailing comma.

Francisco Costa Over a year ago

Can you give a link for that ? Since *args does not return a tuple accordingly because a=*[1,2], returns tuple, whereas if you do not use the trailing commas you cannot do it. By intuition *args is not a tuple, it is like an "abstract sequence" of arguments, although not a Python sequence, * is used to designate the arguments are positional, which has meaning when used in functions to specify positional arguments. Unlike, the *[1,2], which is a tuple, here *is used to unpack the list and convert it into a tuple.

|

Collectives™ on Stack Overflow

Calling vba macro from python with unknown number of arguments

1 Answer 1

23 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

23 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related