如何使用 Python 列出所有 Excel 宏名称 [英] how to list all Excel Macro Names using Python

问题描述

我正在开发这个应用程序，它可以加载 Excel 工作表并允许用户运行宏和标记单元格.我在尝试从 Excel 工作表中读取宏时遇到了问题.我正在使用 win32com 模块进行所有 excel 访问.到目前为止，它对我来说很好.但是我正在努力尝试创建将为我提供所有宏的代码.我有相应的 VBA 代码，它确实为我提供了宏，但到目前为止，我尝试将其转换为 python 没有成功.我想使用 Python 代码读取所有宏.这是 VBA 代码:

I am developing this app that loads an excel sheet and allows users to run macros and tag cells. I ran in to a problem trying to read Macros from the Excel sheet. I am using win32com module for all the excel access. So far it has been good to me. But am struggling trying to create code that will provide me with all the macros. I have the corresponding VBA code that does provide with me the macros but I have so far been unsuccessful trying to translate it in to python. I want to read all the Macros using the Python code. heres the VBA code:

Sub ListMacros()

Const vbext_pk_Proc = 0
Dim VBComp As Object
Dim VBCodeMod As Object
Dim oListsheet As Object
Dim StartLine As Long
Dim ProcName As String
Dim iCount As Integer

Application.ScreenUpdating = False
On Error Resume Next
Set oListsheet = ActiveWorkbook.Worksheets.Add
iCount = 1
oListsheet.Range("A1").Value = "Macro"

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
oListsheet.[a1].Offset(iCount, 0).Value = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iCount = iCount + 1

StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Set VBCodeMod = Nothing
Next VBComp

Application.ScreenUpdating = True
End Sub

我之前没有任何 VBA 经验.我正在为此而苦苦挣扎.非常感谢有关如何解决此问题的任何建议.谢谢

I do not have any prior experience in VBA. I am struggling with this. Any suggestions as to how to approach this problem will be very much Appreciated.Thanks

这是我目前所拥有的！！

Heres what I have so far!!

# RunInExcel.py - opens named Excel file fName, returns the sheets, modules
import os, os.path, win32com.client

def run_macro(fName, path=os.getcwd()):
    print(path)
    fName = os.path.join(path, fName)
    print(fName)
    xlApp = win32com.client.Dispatch("Excel.Application")
    fTest = xlApp.Workbooks.Open(fName)
    for i in fTest.VBProject.VBComponents:
        print i.Name 

if __name__ == "__main__":
    run_macro("Testing1.xlsm")

但我需要宏的名称.感谢您的帮助！

But I need the name of the Macros. Thank you for the help!

推荐答案

不幸的是，我认为您无法直接使用 VBA 获取宏名称.相反，您扫描代码模块寻找 Sub xxx 等

Unfortunately I don't think you can get the macro names directly using VBA. Instead you scan the code module looking for Sub xxx, etc.

def run_macro(fName, path=os.getcwd()):
    print(path)
    fName = os.path.join(path, fName)
    print(fName)
    xlApp = win32com.client.Dispatch("Excel.Application")
    fTest = xlApp.Workbooks.Open(fName)

    for i in fTest.VBProject.VBComponents:
        print i.Name

        num_lines = i.CodeModule.CountOfLines

        for j in range(1, num_lines+1):

            if 'Sub' in i.CodeModule.Lines(j, 1) and not 'End Sub' in i.CodeModule.Lines(j, 1):
                print i.CodeModule.Lines(j, 1)

    xlApp.Application.Quit()
    del xlApp

if __name__ == "__main__":
    run_macro("Testing1.xlsm")

请注意，您需要确保正确退出脚本，否则 Excel 将继续运行(检查 taskmgr).

Note that you need to make sure you exit the script properly or you will leave Excel running (check taskmgr).

这篇关于如何使用 Python 列出所有 Excel 宏名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！