我可以使用以下代码当我没有在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()#如果您的excel脚本关闭,请注释 del xlVBA代码
Public Sub xlstoxlsmFinal() '通过指定文件夹的所有子文件夹,并创建任何xls文档的xlsm(启用宏的版本) Dim fso,oFolder,oSubfolder,oFile,queue作为集合 设置fso = CreateObject(Scripting.FileSystemObject)设置队列=新集合路径=C:\Users\Name\Documents\Monthly Reports\16.06报告\Agent Reports queue.Add fso.GetFolder(Path) 尽管queue.Count> 0 设置oFolder = queue(1) queue.Remove 1'dequeue '...在这里插入任何文件夹处理代码对于每个oSubfolder在oFolder.SubFolders queue.Add oSubfolder'enqueue 下一个oSubfolder 对于每个oFile在oFolder.Files 如果右(oFile,4)<> xlsm和右(oFile,3)<> pdf然后 Workbooks.Open文件名:= oFile ActiveWorkbook.SaveAs文件名:= oFile& m,FileFormat:= xlOpenXMLWorkbookMacroEnabled,CreateBackup:= False ActiveWorkbook.Close End If Next oFile Loop pre>但是,当我尝试将变量从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()#如果要保存,则取消注释此行并更改删除,ReadOnly = 1部分从开放功能。 xl.Application.Quit()#注释如果你的excel脚本关闭 del xl具有变量的VBA代码
Public Sub xlstoxlsmFinal(Datev As String) '遍历指定文件夹的所有子文件夹,并创建任何xls文档的xlsm(启用宏的版本) Dim fso,oFolder,oSubfolder, oFile,queue As Collection 设置fso = CreateObject(Scripting.FileSystemObject)设置队列=新集合路径=C:\Users\Name\ Documents \Monthly Reports\& Datev& Reports\Agent Reports queue.Add fso.GetFolder(Path) 尽管queue.Count> 0 设置oFolder = queue(1) queue.Remove 1'dequeue '...在这里插入任何文件夹处理代码对于每个oSubfolder在oFolder.SubFolders queue.Add oSubfolder'enqueue 下一个oSubfolder 对于每个oFile在oFolder.Files 如果右(oFile,4)<> xlsm和右(oFile,3)<> pdf然后 Workbooks.Open文件名:= oFile ActiveWorkbook.SaveAs文件名:= oFile& m,FileFormat:= xlOpenXMLWorkbookMacroEnabled,CreateBackup:= False ActiveWorkbook.Close End If Next oFile Loop pre>当我在python中运行时,我收到错误消息:
pywintypes_error:(-2147352567,异常发生,(0,u'Microsoft Excel',u无法运行宏PERSONAL.XLSB!Module1.xlstoxlsmFinal(16.06)。宏可能不可用在这个工作簿或所有宏可能被禁用。,u'xlmain11.chm',0,-2146827284),无)有没有人知道如何成功地将Python变量传递给VBA子?
谢谢!
解决方案Per Tim Williams建议我阅读了rondebruin.nl/win/s9/win001.htm的最后一节,并制定了python代码
import os import win32com.client Datev =16.06 xl = win32co m.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()#如果您的excel脚本关闭,请注释 del xl物料差异正在更改:
xl.Application.Run(PERSONAL.XLSB!Module1.xlstoxlsmFinal(+ Datev +))To:
xl.Application.Run(PERSONAL.XLSB!Module1.xlstoxlsmFinal Datev)现在代码工作完美!
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 xlVBA 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 LoopHowever, 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 xlVBA 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 LoopWhen I run this in python I get the error message:
pywintypes_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 xlThe 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!
更多推荐
如何将变量从Python传递给VBA Sub
发布评论