CODEWITHSUNDEEP
pythonexcelvba
Francisco Costa
Francisco Costa

Reputation: 141

Calling vba macro from python with unknown number of arguments

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:

Upvotes: 0

Views: 955

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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)

enter image description here

Upvotes: 4

Related Questions