python - 如何将变量从 Python 传递到 VBA Sub

Question

我正在尝试从我的 Python 代码调用 VBA 子程序，以将指定文件夹中的所有 excel 文件从 xls 格式转换为 xlsm 格式。

当我不在 VBA 中使用变量时，我可以使用下面的代码并且它运行良好。

Python 代码:

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 代码:

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

但是，当我尝试将变量从 python 传递到 VBA 时，我无法调用该函数。以下是我到目前为止尝试过的内容。

带有变量的 Python 代码:

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 代码:

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

当我在 python 中运行它时，我收到错误消息:

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)

有人知道如何成功地将 Python 变量传递给 VBA 子程序吗？

谢谢!

Answer 1

根据 Tim Williams 的建议，我阅读了 rondebruin.nl/win/s9/win001.htm 的最后一部分并制定了 python 代码

    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

实质上的区别在于改变了线路:

xl.Application.Run("PERSONAL.XLSB!Module1.xlstoxlsmFinal(" + Datev + ")")

收件人:

xl.Application.Run("PERSONAL.XLSB!Module1.xlstoxlsmFinal", Datev)

现在代码可以完美运行了!