Calling vba macro from This Workbook using xlwings

Question

I am struggling to run vba macro activated through xlwings.

My macro is saved in ThisWorkbook area rather than a module. When I have used similar method with a macro saved in a Module it works ok.

PythonScript opens workbook and runs module. It then attempts to run another macro which is in different tab of the workbook.

import xlwings as xw 

wb = xw.books.open(‘myworkbook’)
ExcelMacro = wb.macro(‘1stmacro’)
ExcelMacro()

#1stmacro located in a Module. #This works.

ExcelMacro = wb.macro(‘ThisWorkbook.2ndmacro’)
ExcelMacro()

#2nd macro is located in ThisWorkbook rather than Module. Full name is given by ThisWorkbook.2ndMacro where 2nd macro takes form 2ndMacro_Click. The button that is used to activate this is contained in different tab to the one that the workbook opens on.

When I save the workbook to open where this button is located it works; when it is saved on different tab other than this it does not work.

Any help would be greatly appreciated!

Could you please test my solution and if it works, upvote and accept the answer? Thank you :) — Kamil
– Kamil, Commented Oct 28, 2020 at 5:33

Kamil · Accepted Answer · 2020-10-27 12:26:04Z

1

Another solution:

Assume you have saved spreadsheet named book1.xlsm in your Documents folder (you can adapt of course)

import xlwings as xw
spreadsheet_path = 'C:\\Users\your_user\\Documents\\book1.xlsm'

wb = xw.books.open(spreadsheet_path)
ExcelMacro = wb.macro('Sheet1.message1')
ExcelMacro()

ExcelMacro = wb.macro('Sheet2.message2')
ExcelMacro()

ExcelMacro = wb.macro('Module1.hello_from_module1')
ExcelMacro()

I got all macros working regardless which tab was open. You have to put entire spreadsheet_path.

Good luck 😊 Let me know if that still does not solve your issue.

answered Oct 27, 2020 at 12:26

Kamil

6965 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newtothis Over a year ago

again thank you. let me give some more information. the macro that is not working is one in which it is activated by a click event on the button in one tab. i have further investigated the code. it will only work properly if excel is physically on this tab (i cannot change this fact). so really all i need to do is within the python code physically move to the new tab but i am struggling to find how to do this despite it sounding a simple thing to do.

Kamil Over a year ago

maybe it is an excel version problem. I tested it on 2019 / 365. In both cases I got all macros invoked without switching the activated sheet. Are you sure you included full path to your workbook?

Kamil · Accepted Answer · 2020-10-26 11:47:12Z

0

Is it necessary to call the macro from This workbook rather than from any Module? If you can, it is always safer to use what works, rather than thinking about workarounds.

Since Workbooks with macros are anyhow saved with .xlsm or .xlsb extensions, there should be nothing preventing you inserting a module there.

alt+F11 , on the left top corner right click on "This workbook", then choose insert, Module.

Module will be created and you can either copy paste or even drag and drop your macro.

If that is not a possibility, let me now.

answered Oct 26, 2020 at 11:47

Kamil

6965 silver badges12 bronze badges

1 Comment

newtothis Over a year ago

thank you for response. unfortunately i have no ownership of said workbook so i cannot change where the macros are saved to. so structure of where macros are saved and ran from has to remain the same.

Collectives™ on Stack Overflow

Calling vba macro from This Workbook using xlwings

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related