How can I export VBA code to text from de modules?

Question

I'm trying to map all VBA codes I've in some excel in the office.

In my job, we have more than two hundred excel files, with a lot of macro in each. How can I extract the code text of some module?

I've see something like

workbook = excel.Workbooks.Open("{}{}.xlsm".format(path, file), True, True)
for i in workbook.VBProject.VBComponents:
    print(i.Name)

Return

Plan1
Plan2
Main
Plan5
Plan3
Plan4
Sheet1

How can I get the VBA code in these Modules?

The solution could be in VBA or in Python

You could try checking out this [link](http://www.cpearson.com/excel/vbe.aspx) — Mistella, Oct 21 '19 at 16:09
http://www.appspro.com/Utilities/CodeCleaner.htm has a function to export all of your modules — Tim Williams, Oct 21 '19 at 16:11
Something like this should help. Did it 2 years ago, on Excel 2013 - https://www.vitoshacademy.com/vba-listing-all-procedures-in-all-modules/ — Vityata, Oct 21 '19 at 16:16

BruceWayne · Answer 1 · 2019-10-21T17:20:21.637

2

Perhaps this is close to what you are looking for?

Sub GEN_USE_Export_all_modules_from_Project()
' https://www.ozgrid.com/forum/forum/help-forums/excel-general/60787-export-all-modules-in-current-project
     ' reference to extensibility library
Dim tday As String
tday = Date
Dim objMyProj As VBProject
Dim objVBComp As VBComponent
Dim destFolder as String

destFolder = "C:\Users\WHATEVER\Documents\Business\EXCEL NOTES\"
Set objMyProj = Application.VBE.ActiveVBProject

tday = WorksheetFunction.Substitute(tday, "/", "-")

For Each objVBComp In objMyProj.VBComponents
    If objVBComp.Type = vbext_ct_StdModule Then
        objVBComp.Export destFolder & tday & " - " & objVBComp.name & ".bas"
    End If
Next
MsgBox ("Saved to " & destFolder)
End Sub

This will loop through your VBAProject (where this macro itself is stored), and will save the modules in a .bas file which you can open and go through.

Edit: You can replace .bas with .txt and it works, FYI.

edited Oct 21 '19 at 17:20

answered Oct 21 '19 at 16:20

BruceWayne

22,923
15
65
110

But how can I get the text your code? Because a export the file module. – de_python Oct 21 '19 at 17:15
@user9438751 - Maybe I just misunderstood your question. If put put the above code in a VBA module, and run it, it'll export all the modules in that project, and save as `.bas` (or `.txt` if you replace that in the `objVBComp.Export "C:\...` line) to the folder you specify. – BruceWayne Oct 21 '19 at 17:19
That's great, but how do you get the text version of the Form's Designer file like you can in VB6 and VB.NET? Also, btw, I know this Q is Excel but since we're talking VBA, how about text version of Access Report's Designer? – Tom Nov 07 '21 at 16:37

score 0 · Accepted Answer · answered Oct 21 '19 at 17:12

I've found a solution:

import win32com.client
import pandas as pd

excel = win32com.client.Dispatch("Excel.Application")
workbook = excel.Workbooks.Open("{}{}.xlsm".format(path, file), True, True)

dict_modules = {}
for i in workbook.VBProject.VBComponents:
    name = i.name
    lines = workbook.VBProject.VBComponents(name).CodeModule.CountOfLines

    # To jump empty modules
    if lines == 0:
        pass
    else:
        text = workbook.VBProject.VBComponents(name).CodeModule.Lines(1,lines)
        dict_modules[name] = [text]

df = pd.DataFrame(dict_modules)

The DataFrame are returned with the modules name like the head of the table. To look each text I found

# To get the full text
module_name = df["module_name"][0]
#To get by line
module_text_by_line = module_name.splitlines()

Thanks who tried help me.

That's great, but how do you get the text version of the Form's Designer file like you can in VB6 and VB.NET? Also, btw, I know this Q is Excel but since we're talking VBA, how about text version of an Access Report's Designer? — Tom, Nov 07 '21 at 16:55

How can I export VBA code to text from de modules?

2 Answers2