2

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?

Community
  • 1
  • 1
w.jacob.ward
  • 23
  • 1
  • 5
  • 1
    When you say you want to write a VBA diff program, do you mean a diff program that compares two pieces of VBA? Or do you mean a diff program written in VBA that compares two files of any kind? I'm guessing it's the first one; just checking. – Kevin Sep 05 '12 at 18:43
  • Yes, Kevin, your first guess was correct. I'm looking to write a Python script that will take two Excel files and compare the VBA within each of them. – w.jacob.ward Sep 05 '12 at 19:49

2 Answers2

6

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.)

Community
  • 1
  • 1
Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
  • Just FYI, this was **extremely** useful for me, even 3.5 years later. Same with the link to Pearson's stuff. So a huge Thank You is in order. – dthor Mar 03 '16 at 01:04
0

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

Chris Spicer
  • 2,144
  • 1
  • 13
  • 22