1

so I'm trying to make my life a bit easier by have Python + Excel automate some of the things I need to do for reports. I have created an Excel workbook (macro enabled) that's just dedicated to macros. For example, when I press ctrl+R, it removes any spaces and parenthesis's that I do not want for reports. My question is how can open the macro workbook then open the other reports that I need and use the macro.

This is my current code:

import xlwings as xw
wb = xw.books.open(r'macrofile.xlsb', r'reportthatneedswork.csv')
macroname = wb.macro('MacroName')
macroname()

It is only opening the macro workbook but not the other and does not do anything.

FinestRyeBread
  • 132
  • 9
  • Does this answer your question? [Running an Excel macro via Python?](https://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python) – itprorh66 Dec 04 '21 at 22:25
  • @itprorh66 it doesn't. I'm trying to do this within xlwings if possible – FinestRyeBread Dec 05 '21 at 15:47
  • Does this [How do I call an Excel macro from Python using xlwings?](https://stackoverflow.com/questions/30308455/how-do-i-call-an-excel-macro-from-python-using-xlwings) answer your question? – itprorh66 Dec 05 '21 at 20:01
  • @itprorh66 it helps a little, I'm still struggling on reading another sheet/book and calling that macro. – FinestRyeBread Dec 05 '21 at 22:36

1 Answers1

2

If you plan on using this frequently I would rewrite the macro code in python+xlwings. This removes the issue of running things from other workbooks and will let you run it on multiple files at once by just adding looping logic:

macro.py

import os
import sys
import xlwings as xw
from os import listdir
from os.path import isfile, join

def python_macro(wb_path, app):
    #Optionally verify that files are all xls*, csv, or directory files

    wb = app.books.open(wb_path)
    sheet = wb.sheets.active
    
    #Rewrite macro in python+xlwings below
    print(sheet.range('A1').value)


with xw.App() as app:    
    for arg in sys.argv[1:]:
        path = join(os.getcwd(), arg)
            
        if os.path.isdir(path):
            #Optionally process further subdirectories
                
            files = [f for f in listdir(path) if isfile(join(path, f))]

            for file in files:
                python_macro(file, app)

        elif os.path.isfile(path):
            python_macro(path, app)

Save the file in the same directory as the report files and running it would look like this:

python macro.py reportthatneedswork.csv anotherreport.csv report-sub-dir

From what I could find, there isn't really a way to execute external workbook macros with xlwings. The closest thing I found was using win32com to extract and move over the vb script then you could use xlwing to open it an run the macro. However, this is particularly overkill for your scenario. It will be much easier to deal with if you just rewrite the macro in python.

Read VBA from workbook and then 'inject' it into another sheet.

moonman4
  • 308
  • 3
  • 12