Remove vba modules using python?

Question

It seems win32api should be able to do this given the answer here and [.

I would like to remove all modules from an excel workbook (.xls) using python

observed the same behavior. – kapilddit Nov 15 '19 at 19:41 — kapilddit, Nov 15 '19 at 19:41

score 4 · Answer 1 · edited May 23 '17 at 11:51

Consider using the VBComponents collection available in the COM interface accessible with Python's win23com.client module. However, inside the particular workbook you need to first grant programmatic access to the VBA object library.

Also, you will need to conditionally set the type as this procedure cannot delete object modules including Sheets (Type = 100) and Workbooks (Type = 100). Only Standard Modules (Type = 1), Class Modules (Type = 2), UserForms (Type= 3), and other inserted components can be removed. Of course you can iterate through defined list of modules to remove. Try/Except statement is used to effectively close out the Excel.Application process whether script fails or not.

import win32com.client

# OPEN EXCEL APP AND WORKBOOK
xlApp = win32com.client.Dispatch("Excel.Application")
xlwb = xlApp.Workbooks.Open("C:\\Path\\To\\Workbook.xlsm")

# ITERATE THROUGH EACH VB COMPONENT (CLASS MODULE, STANDARD MODULE, USER FORMS)
try:    
    for i in xlwb.VBProject.VBComponents:        
        xlmodule = xlwb.VBProject.VBComponents(i.Name)
        if xlmodule.Type in [1, 2, 3]:            
            xlwb.VBProject.VBComponents.Remove(xlmodule)

except Exception as e:
    print(e)

finally:    
    # CLOSE AND SAVE AND UNINITIALIZE APP
    xlwb.Close(True)
    xlApp.Quit

    xlApp = None

Very interesting. I will be testing to day and accept when it works - it appears to be exactly what I wanted. — jason m, Jul 13 '16 at 12:42
Any luck in testing? Be sure to grant programmatic access in workbook. — Parfait, Jul 14 '16 at 01:54

score 1 · Answer 2 · answered Jul 08 '16 at 18:44

1

Actually simply reading in the file with xlrd, copying with xlutils, and spitting it out with xlwt will remove the VBA (and other stuff):

import xlrd, xlwt
from xlutils.copy import copy as xl_copy
rb = xlrd.open_workbook('/path/to/fin.xls')
wb = xl_copy(rb)
wb.save('/path/to/fou.xls')

answered Jul 08 '16 at 18:44

mechanical_meat

163,903
24
228
223

*"will remove [...] other stuff"* may not be desirable. At the very least, it should be documented, what it is. – IInspectable Jul 08 '16 at 19:21
Good point, @IInspectable! I will do some research to find out what else is removed. – mechanical_meat Jul 08 '16 at 19:22
Please see this question for a discussion about what else is removed http://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy – mechanical_meat Jul 08 '16 at 19:23
@bernie could you include information about preserving cells formatting parameter (`formatting_info=True` when calling `open_workbook method`) in your response? I'm reffering to http://xlrd.readthedocs.io/en/latest/api.html and your note in comment. – matandked Nov 29 '17 at 19:29

Remove vba modules using python?

2 Answers2

Linked