2

I am having a problem getting xlwings to run a macro from Python. Despite following the code from xlwings documentation, I cannot get xlwings to execute an Excel macro. For instance, in Excel workbook named "Book.xlsm":

' in Excel workbook Book.xlsm
Sub Test()
   Set ws = Worksheets("ABC")
   ws.Range("A1").Value = 10
End Sub

This macro runs OK within Excel. But when I try calling this module from Python, it fails:

# in Python
import xlwings

wb = xlwings.Book('C:\\Book.xlsm')
wb.macro('Test')
print('done.')

No error messages. The Python code just runs and ends, printing the message "done." but when I check the worksheet ABC, nothing is written. Please note I am able to connect to this workbook and change cell values using xlwings. I just cannot get it to run the Test macro.

Also note I have used a much older xlwings (prior to 0.7.0, I think) before and it runs my macros with no problems. I am using the 0.10.0 version now.

Community
  • 1
  • 1
cbsteh
  • 809
  • 6
  • 19
  • Are you saving the workbook after running the macro from Python? Is the macro in the "ABC" worksheet's namespace or in the namespace of the workbook? – cco Oct 23 '16 at 10:39
  • i.e. is it in a VBA module or not? – Felix Zumstein Oct 23 '16 at 13:37
  • Yes, the Test subroutine is in a VBA module. I hope I am right. I open up the VBA editor, then choose Insert Module under the workbook name. The Test subroutine is in the Module1, not in any worksheet. I tried using a non-existent subroutine name (like 'XYZ') in Python, such as: wb.macro('XYZ') but my code still runs and exits without any error messages. – cbsteh Oct 23 '16 at 14:48
  • @Chris your real issue is that `wb.macro` only maps the VBA function/macro, you need to call it seperatly: `test = wb.macro('Test')`, then call `test()` – Felix Zumstein Nov 07 '16 at 20:13
  • @FelixZumstein. Great, thank you. Was this mentioned in the documentation? I don't remember the documentation having us to call/execute a macro like this. – cbsteh Nov 18 '16 at 14:12

1 Answers1

1

Try the following:

In VBA:

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

In python: 

import xlwings as xw
wb1 = xw.Book('Libro1.xlsm')    
macro=wb1.macro('Test')
macro(10)
print('done.')
Armando
  • 44
  • 2
  • Can you explain your answer instead of just dumping code in here? – Robert Oct 25 '16 at 16:30
  • The code is so simple that is self-explanatory. Chris is right when he says that his macro does not run and does not print value 10 in cell 'A1'. What I want to say is that the macro runs but when it recieves the value as parameter. – Armando Oct 25 '16 at 16:54
  • @Armando. Works! The drawback is in my actual subroutine (which does not have any arguments), I have to add a dummy/unused argument, so that xlwings is able to execute my macro by passing some value as an argument. But this is a trivial matter in my application. Thanks again. – cbsteh Oct 26 '16 at 04:19
  • @Armando this should not be necessary. Can you please post a sample workbook in a GitHub issue so we can resolve this properly? thanks – Felix Zumstein Nov 07 '16 at 20:10
  • see: https://github.com/ZoomerAnalytics/xlwings/issues/580 – Felix Zumstein Mar 20 '19 at 19:04