1

I was looking at How do I call an Excel macro from Python using xlwings?, and I understand it's not fully supported, but I Would like to know if there is a way to do this.

some like:

from xlwings import Workbook, Application
wb = Workbook(...)
Application(wb).xl_app.Run("your_macro("%Args%")")
Community
  • 1
  • 1
jvaldiviezo
  • 101
  • 9

2 Answers2

6

This can be done by doing what you propose. However please keep in mind that the this solution will not be cross-platform (Win/Mac). I´m on Windows so below has to be adjusted to appscript on Mac. http://docs.xlwings.org/en/stable/missing_features.html

The VBA script can be called by following:

linked_wb.xl_workbook.Application.Run("vba_script", variable_to_pass)

Example: Let´s say you have a list of strings that should be used in a Data Validation list in Excel

Python:

from xlwings import Workbook

linked_wb = Workbook.caller()

animals = ['cat', 'dog', 'snake', 'bird']
animal_list = ""
for animal in animals:
    animal_list += animal + "|"

linked_wb.xl_workbook.Application.Run("create_validation", animal_list)

Excel VBA:

Public Sub create_validation(validation_list)
    Dim validation_split() As String

    validation_split = Split(validation_list, "|")

    'The drop-down validation can only be created with 1-dimension array.
    'We get 1-D from the Split above
    With Sheet1.Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(validation_split, ",")
    End With
End Sub
Daniel Sjödin
  • 121
  • 4
0

Python Example:

import xlwings as xw

# wb_path = r"set_wb_path"
wb = xw.Book(wb_path)
app = wb.app
variable_to_pass = 'test'

macro = wb.macro(moduleName.macroName)
macro(variable_to_pass)

wb.app.quit()
#or
wb.close()

As long as your VBA function accepts a variable and you pass it the same type of variable (str,int,list) this will work.

Chaz Clark
  • 1