I am trying to call a VBA sub from my Python code to convert all excel files in a specified folder from the xls to xlsm format.
I can use the following code when I am not using a variable in the VBA and it 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 this out if your excel script closes
del xl
VBA Code:
Public Sub xlstoxlsmFinal()
' goes through all the sub folders of a specified folder and created an xlsm(macro enabled version) of any xls documents
Dim fso, oFolder, oSubfolder, oFile, queue As 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)
Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
'...insert any folder processing code here...
For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder 'enqueue
Next oSubfolder
For Each oFile In oFolder.Files
If Right(oFile, 4) <> "xlsm" And Right(oFile, 3) <> "pdf" Then
Workbooks.Open Filename:=oFile
ActiveWorkbook.SaveAs Filename:=oFile & "m", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close
End If
Next oFile
Loop
However, I am unable to call the function when I try to pass a variable from python to VBA. Below is what I have tried so far.
Python code with 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 you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
xl.Application.Quit() # Comment this out if your excel script closes
del xl
VBA code with Variable:
Public Sub xlstoxlsmFinal(Datev As String)
' goes through all the sub folders of a specified folder and created an xlsm(macro enabled version) of any xls documents
Dim fso, oFolder, oSubfolder, oFile, queue As 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)
Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
'...insert any folder processing code here...
For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder 'enqueue
Next oSubfolder
For Each oFile In oFolder.Files
If Right(oFile, 4) <> "xlsm" And Right(oFile, 3) <> "pdf" Then
Workbooks.Open Filename:=oFile
ActiveWorkbook.SaveAs Filename:=oFile & "m", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close
End If
Next oFile
Loop
When I run this in python I get the error message:
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u"Cannot run the macro 'PERSONAL.XLSB!Module1.xlstoxlsmFinal(16.06 )'. The macro may not be available in this workbook or all macros may be disabled.", u'xlmain11.chm', 0, -2146827284), None)
Would anybody know how to succesfully pass a Python variable to a VBA sub?
Thanks!
Per Tim Williams suggestion I read the last section of rondebruin.nl/win/s9/win001.htm and formulated the 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 this out if your excel script closes
del xl
The material difference was changing the line:
xl.Application.Run("PERSONAL.XLSB!Module1.xlstoxlsmFinal(" + Datev + ")")
To:
xl.Application.Run("PERSONAL.XLSB!Module1.xlstoxlsmFinal", Datev)
Now the code works perfectly!
Go to Macro Security under the developer tag and choose the Enable all macros option
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With