Get code from module in Excel
<p>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. </p>

<p>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. </p>

<pre><code>for vbc in wb.VBProject.VBComponents:
    print(vbc.Name + ": " + str(vbc.Type) + "\n" + str(vbc.CodeModule))
</code></pre>

<p>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. </p>

<p>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.</p>

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

<p>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 <code>vbc.CodeModule.Lines(0, vbc.CodeModule.CountOfLines - 1)</code> because the index 0 is out of range.</p>

python excel vba excel-vba pywin32