CODEWITHSUNDEEP
pythonvbaexcelpywin32
ryansin
ryansin

Reputation: 1817

Get code from module in Excel

I have a number of workbooks that have Macros which point to a particular SQL server using a connection string embedded in the code. We've migrated to a new SQL server so I need to go through these and alter the connection string to look at the new server in each of the Macros that explicitly mentions it.

Currently I'm able to list all of the modules in the workbook, however I'm unable to get the code from each module, just the name and type number. 

for vbc in wb.VBProject.VBComponents:
    print(vbc.Name + ": " + str(vbc.Type) + "\n" + str(vbc.CodeModule))

What property stores the code so that I can find and replace the server name? I've had a look through the VBA and pywin32 docs but can't find anything.

Upvotes: 1

Views: 1652

Answers (2)

Sam Ginrich
Sam Ginrich

Reputation: 1

Method Lines of property CodeModule has the signature

Function Lines(ByVal first as Integer, ByVal count as Integer) as String

first is in range 1...CodeModule.CountOfLines, index of the first row of the code section, you want to retrieve

count is in range 1...CodeModule.CountOfLines-first+1, number of lines of the section

The return value is a concatenation of the code lines of the section with separator vbNewLine.

Upvotes: 0

ryansin
ryansin

Reputation: 1817

Got it- there's a Lines method in the CodeModule object that allows you to take a selection based on a starting and ending line. Using this in conjunction with the CountOfLines property allows you to get the whole thing.

for vbc in wb.VBProject.VBComponents:
    print(vbc.Name + ":\n" + vbc.CodeModule.Lines(1, vbc.CodeModule.CountOfLines))

It's worth noting as well that the first line is line 1, not line 0 as that caught me out. The following will error vbc.CodeModule.Lines(0, vbc.CodeModule.CountOfLines - 1) because the index 0 is out of range.

Upvotes: 4

Related Questions