How do I call an Excel VBA script using xlwings v0.10

Question

I used to use the info in this question to run a VBA script that does some basic formatting after I run my python code.

How do I call an Excel macro from Python using xlwings?

Specifically I used the first update.

from xlwings import Workbook, Application
wb = Workbook(...)
Application(wb).xl_app.Run("your_macro")

Now I'm using v0.10.0 of xlwings and this code no longer works.

When I try the suggested new code for v0.10.0:

wb.app.macro('your_macro')

Python returns an object:

<xlwings.main.Macro at 0x92d3198>

and my macro isn't run in Excel.

The documentation (http://docs.xlwings.org/en/stable/api.html#xlwings.App.macro) has an example that is a custom function but I have a script that does several things in Excel (formats the data I output from python, adds some formulas in the sheet, etc.) that I want to run.

I'm sure I'm missing something basic here.

Update Based on Felix Zumstein's suggestion, I tried:

import xlwings as xw
xlfile = 'model.xlsm'
wb = xw.Book(xlfile)
wb.macro('your_macro')

This returns the same thing as wb.app.macro('your_macro'):

<xlwings.main.Macro at 0x92d05888>

and no VBA script run inside Excel.

Felix Zumstein · Accepted Answer · 2016-11-07T20:14:48.653

3

You need to use Book.macro. As your link to the docs says, App.macro is only for macros that are not part of a workbook (i.e. addins). So use:

your_macro = wb.macro('your_macro')  # this maps the VBA code
your_macro()  # only this executes the VBA code

edited Nov 07 '16 at 20:14

answered Oct 15 '16 at 11:04

Felix Zumstein

6,737
1
30
62

I get the same result when I use wb.macro('your_macro). – Budd Oct 16 '16 at 20:24
You can send me a sample workbook (email is on GitHub) and I will look into it. – Felix Zumstein Oct 16 '16 at 21:04
I updated the sample to include the mapping and calling of the macro – Felix Zumstein Nov 07 '16 at 20:15

kiki270 · Answer 2 · 2021-09-20T14:19:56.730

I got issues when I updated xlwings to 0.9+ version. To run vba macro with xlwings, I used the code written below for running macros inside the personal workbook (PERSONAL.XLSB). The updated code no2 of Felix didn't work for me, for macro inside the personal workbook.

import xlwings as xw

wb = xw.Book(excel_file_path)
app = wb.app
# into brackets, the path of the macro
macro_vba = app.macro("'PERSONAL.XLSB'!my_macro") 
macro_vba()

Hope it will help.

score 0 · Answer 3 · edited May 23 '17 at 10:31

Maybe you are facing a similar situation as the one stated here: Possible bug? xlwings cannot run an Excel macro?

For some reason macros are not running properly when they don't recieve something as parameter. Even though such parameter is not used in the VBA macro, the parameter is needed.

Example: The following VBA and python code may not run:

Sub Test()
   Set ws = Worksheets("Hoja1")
   ws.Range("A1").Value = 10
End Sub

Python code:

import xlwings as xw
wb1 = xw.Book('Libro1.xlsm')    
macro=wb1.macro('Test')
print('done.')

So you have to do the proper changes.

VBA code:

Sub Test(number)
 Set ws = Worksheets("Hoja1")
 ws.Range("A1").Value = 10
End Sub

Python code:

import xlwings as xw
wb1 = xw.Book('Libro1.xlsm')    
macro=wb1.macro('Test')
macro(10)   #here value 10 is the dummy parameter sended to the vba macro
print('done.')

As per my other comment on the other question: that shouldn't be necessary. Please open an issue on GH to resolve it properly. — Felix Zumstein, Nov 07 '16 at 20:15

How do I call an Excel VBA script using xlwings v0.10

3 Answers3