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

Gladysglagolitic

Yes, you can use Python to write a program in another language, most likely, any other language. – Musclebound

If you're going to fire up Excel to run the auto fit, you could just create the whole thing via automation. Then you won't have to do the dynamic code generation... – Heeler

even though this question is a bit dusty, maybe this might help: pyxll.com/docs/macros.html – Dermott

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.

Freshwater

I have an opportunity today to try and test the code. Thanks a lot for contributing. This is stepping me in the right direction. I have a lot of other ideas that this code may help with. – Gladysglagolitic

I'm wondering. I am using openpyxl throughout my code and I don't want to change that. I'm thinking that, I can run all my code and create the workbook and save it to disc. Is there any way I can grab that workbook at the end of my code and run your code using win32 and add in the subprocess at that point? I'm struggling with opening an existing excel sheet with win32 come instead of opening an instance using EnsureDispatch like you did above. – Gladysglagolitic

How to then "activate" (or "run") the above defined macro? – Adkins

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.

Legged