使用Python从Excel电子表格中读取VBA

I would like to write a VBA diff program in (preferably) Python. Is there a Python library that will allow me to read the VBA contained in an Excel spreadsheet?

我想在(最好)Python中编写一个VBA diff程序。是否有一个Python库可以让我读取Excel电子表格中包含的VBA?

2 个解决方案

#1

Here's some quick and dirty boilerplate to get you started. It uses the Excel COM object (a Windows only solution):

这里有一些快速而肮脏的样板文件可以帮助您入门。它使用Excel COM对象(仅限Windows的解决方案):

from win32com.client import Dispatch
wbpath = 'C:\\example.xlsm'
xl = Dispatch("Excel.Application")
xl.Visible = 1
wb = xl.Workbooks.Open(wbpath)
vbcode = wb.VBProject.VBComponents(1).CodeModule
print vbcode.Lines(1, vbcode.CountOfLines)

This prints the silly macro I recorded for this example:

这打印出我为此示例记录的愚蠢的宏:

Sub silly_macro()
'
' silly_macro Macro
'

'
    Range("B2").Select
End Sub

Note that Lines and VBComponents use 1-based indexing. VBComponents also supports indexing by module name. Also note that Excel requires backslashes in paths.

请注意,Lines和VBComponents使用基于1的索引。 VBComponents还支持按模块名称进行索引。另请注意,Excel需要路径中的反斜杠。

To dive deeper see Pearson's Programming The VBA Editor. (The above example was cobbled together from what I skimmed from there.)

深入了解Pearson的编程VBA编辑器。 (上面的例子是从我从那里撇去的东西拼凑而成的。)

#2

I have created an application that does this called VbaDiff. If you provide it two Excel files it will compare the VBA code in each. You can also run it from the command line, or use the version that comes with an API if you want to integrate it with your own programs.

我创建了一个名为VbaDiff的应用程序。如果您提供两个Excel文件,它将比较每个文件中的VBA代码。您也可以从命令行运行它,或者如果要将它与您自己的程序集成,请使用API附带的版本。

You can find out more at http://www.technicana.com/vbadiff-information.html

您可以在http://www.technicana.com/vbadiff-information.html上找到更多信息

Chris

#1