excel - How to pass Variable from Python to VBA Sub -

i trying call vba sub python code convert excel files in specified folder xls xlsm format.

i can use following code when not using variable in vba , works well.

python code:

import os import win32com.client  xl=win32com.client.dispatch("excel.application") xl.workbooks.open(filename="c:\users\name\documents\personal.xlsb", readonly=1) xl.application.run("personal.xlsb!module1.xlstoxlsmfinal" xl.application.quit() # comment out if excel script closes del xl 

vba code:

public sub xlstoxlsmfinal()  ' goes through sub folders of specified folder , created xlsm(macro enabled version) of xls documents       dim fso, ofolder, osubfolder, ofile, queue collection      set fso = createobject("scripting.filesystemobject")     set queue = new collection     path = "c:\users\name\documents\monthly reports\16.06 reports\agent reports"     queue.add fso.getfolder(path)      while queue.count > 0         set ofolder = queue(1)         queue.remove 1 'dequeue         '...insert folder processing code here...         each osubfolder in ofolder.subfolders             queue.add osubfolder 'enqueue         next osubfolder         each ofile in ofolder.files                 if right(ofile, 4) <> "xlsm" , right(ofile, 3) <> "pdf"                 workbooks.open filename:=ofile                 activeworkbook.saveas filename:=ofile & "m", fileformat:=xlopenxmlworkbookmacroenabled, createbackup:=false                 activeworkbook.close     end if         next ofile     loop 

however, unable call function when try pass variable python vba. below have tried far.

python code variable:

import os import win32com.client  datev = """16.06 """ xl=win32com.client.dispatch("excel.application") xl.workbooks.open(filename="c:\users\name\documents\personal.xlsb", readonly=1) xl.application.run("personal.xlsb!module1.xlstoxlsmfinal(" + datev + ")") ##    xl.application.save() # if want save uncomment line , change delete ", readonly=1" part open function. xl.application.quit() # comment out if excel script closes del xl 

vba code variable:

public sub xlstoxlsmfinal(datev string)  ' goes through sub folders of specified folder , created xlsm(macro enabled version) of xls documents       dim fso, ofolder, osubfolder, ofile, queue collection      set fso = createobject("scripting.filesystemobject")     set queue = new collection     path = "c:\users\name\documents\monthly reports\" & datev & "reports\agent reports"     queue.add fso.getfolder(path)      while queue.count > 0         set ofolder = queue(1)         queue.remove 1 'dequeue         '...insert folder processing code here...         each osubfolder in ofolder.subfolders             queue.add osubfolder 'enqueue         next osubfolder         each ofile in ofolder.files                 if right(ofile, 4) <> "xlsm" , right(ofile, 3) <> "pdf"                 workbooks.open filename:=ofile                 activeworkbook.saveas filename:=ofile & "m", fileformat:=xlopenxmlworkbookmacroenabled, createbackup:=false                 activeworkbook.close     end if         next ofile     loop 

when run in python error message:

pywintypes.com_error: (-2147352567, 'exception occurred.', (0, u'microsoft excel', u"cannot run macro 'personal.xlsb!module1.xlstoxlsmfinal(16.06 )'. macro may not available in workbook or macros may disabled.", u'xlmain11.chm', 0, -2146827284), none) 

would know how succesfully pass python variable vba sub?

thanks!

per tim williams suggestion read last section of rondebruin.nl/win/s9/win001.htm , formulated python code

    import os     import win32com.client      datev = """16.06 """     xl=win32com.client.dispatch("excel.application")     xl.workbooks.open(filename="c:\users\name\documents\personal.xlsb", readonly=1)     xl.application.run("personal.xlsb!module1.xlstoxlsmfinal", datev)     xl.application.quit() # comment out if excel script closes     del xl 

the material difference changing line:

xl.application.run("personal.xlsb!module1.xlstoxlsmfinal(" + datev + ")") 

to:

xl.application.run("personal.xlsb!module1.xlstoxlsmfinal", datev) 

now code works perfectly!