CODEWITHSUNDEEP
pythonexcelvbawin32com
de_python
de_python

Reputation: 107

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

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

Upvotes: 0

Views: 3632

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

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.

Upvotes: 2

de_python
de_python

Reputation: 107

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.

Upvotes: 0

Related Questions