Hey guys,
This video will show how to execute an Excel VBA macro from Python. In my folder, I have a python file & an excel
workbook. On the left side of the screen is my python
file, and on the right, you can see the Excel workbook. I have opened already the visual basic editor
to show you the macros I want to execute. Within module 1, I have two macros. The first one is a very simple Messagebox—all
it does is writing some text into cell A1 and displaying a message box. The second macro is very similar. But this macro also takes an argument as input. VBA will then reflect this input in the message
box. To execute the macros from Python, we will
be using the xlwings library. You can install the library by typing' pip
install xlwings' in your command prompt or terminal. Once done, let's have a look a the python
file. On top of the script, I have imported xlwings
as xw. Next, I am opening up the workbook by providing
the workbook's path. In my case, the excel file is in the same
directory as the Python script. That is why I only need to type the workbook's
name here. However, you could, of course, write the full
path to your excel file instead. To execute the macro, you can take the instance
of the workbook and run the macro method. Here, I have specified the Module & Macro
Name. So, 'Module 1' and 'SimpleMsgBox'. With that in place, I can go ahead and execute
Macro1. In my text editor, I am using a shortcut key
to run the python file. As a result, we can see our text in cell A1
and additionally, we have got our message box. The second example is very similar. The only difference is that I will provide
our input when I am executing the macro. So, Ahoy will be the input and should be displayed
in the message box. Let me go ahead and run the script one more
time. As expected, the content in cell 1 got overwritten,
and we can see the message box with our input. I think in many cases, you would like to save
& close the workbook after you are running the macro. That's is why I have also included it. So, let me go ahead and run the code one more
time. After confirming the first message box, the
second macro got executed, the workbook has been saved and closed. But, you will still see the Excel window. You might not expect this behaviour, but whenever
you open up a workbook with xlwings, it will also create an instance of Excel. Just by closing the workbook, we are not quitting
the excel instance. You can quit the excel instance by typing
'wb.app.quit'. However, if you have already any other workbooks
open, those might get closed as well. And this is probably not what you want. So, therefore, you could include the following
if-else condition. If this is the only workbook open, I will
quit the excel instance. Otherwise, I will close the workbook. With that in place, I will execute our code
one more time. As before, we will get our message boxes and
once done, the file will be saved & closed correctly. Ok, guys, that's it for this tutorial. As always, if you have any questions, please
let me know in the comments. Thanks for watching and see you next time.
