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?
-
1When 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– Kevin2012-09-05 18:43:26 +00:00Commented Sep 5, 2012 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– w.jacob.ward2012-09-05 19:49:01 +00:00Commented Sep 5, 2012 at 19:49
Add a comment
|
2 Answers
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.)
1 Comment
dthor
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.
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
2 Comments
Peter Mortensen
The link is broken: "404. This page could not be found" (as HTTPS).
Peter Mortensen
OK, the OP has left the building: "Last seen more than 5 years ago"
