CODEWITHSUNDEEP
pythonexcelvba
w.jacob.ward
w.jacob.ward

Reputation: 33

Using Python to read VBA from an Excel spreadsheet

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?

Upvotes: 3

Views: 6741

Answers (2)

Chris Spicer
Chris Spicer

Reputation: 2194

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.

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

Chris

Upvotes: 0

Steven Rumbalski
Steven Rumbalski

Reputation: 45552

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

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.

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

Upvotes: 6

Related Questions