Use Python to Write VBA Script?

Question

This might be a bit of a stretch, but is there a possibility that a python script can be used to create VBA in MS Excel (or any other MS Office product that uses VBA) using pythonwin or any other module.

Where this idea came from was pythons openpyxl modules inability to do column autowidth. The script I have creates a workbook in memory and eventually saves it to disc. There are quite a few sheets and within each sheet, there are quite a few columns. I got to thinking....what if I just use python to import a VBA script (saved somewhere in notepad or something) into the VBA editor in excel and then run that script from python using pythonwin.

Something like:

Workbooks.worksheets.Columns("A:Z").EntireColumn.Autofit

Before you comment, yes I have seen lots of pythonic examples of how to work around auto adjusting columns in openpyxl, but I see some interesting opportunities that can be had utilizing the functionality you get from VBA that may not be available in python.

Anyways, I dug around the internet a bit and I didn't see anything that indicates i can, so i thought I'd ask.

Cheers, Mike

Answer 1

Yes, it is possible. You can start looking at how you can generate a VBA macro from VB on that Microsoft KB.

The Python code below is illustrating how you can do the same ; it is a basic port of the first half of the KB sample code:

import win32com.client as win32

import comtypes, comtypes.client

xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
ss = xl.Workbooks.Add()
sh = ss.ActiveSheet

xlmodule = ss.VBProject.VBComponents.Add(1)  # vbext_ct_StdModule

sCode = '''sub VBAMacro()
       msgbox "VBA Macro called"
      end sub'''

xlmodule.CodeModule.AddFromString(sCode)

You can look at the visible automated Excel macros, and you will see the VBAMacro defined above.

Answer 2

The top answer will only add the macro, if you actually want to execute it there is one more step.

import win32com.client as win32

xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
ss = xl.Workbooks.Add()
xlmodule = ss.VBProject.VBComponents.Add(1)
xlmodule.Name = 'testing123'
code = '''sub TestMacro()
    msgbox "Testing 1 2 3"
    end sub'''
xlmodule.CodeModule.AddFromString(code)
ss.Application.Run('testing123.TestMacro')

Adding a module name will help deconflict from any existing scripts.