how to execute vba macro outside of excel

Question

I have an excel spreadsheet with a massive amount of VBA and macros that include a button.

How do I execute VBA code in Excel (specifically to clicking the button and trigger its onclick event) outside excel from python (for example)?

Note: I'm willing to take answers in different languages like C++, C#, or Java; but, by far I would prefer them in python since it'll more smoothly connect with the remainder of my python applications.

Note 2: I may need to manipulate the excel spreadsheet with python using one of the python excel libraries available

Version Numbers:

Microsoft Excel Office 365 Version 1708 Build 8431.2079
python 2.7

Answer 1

You can use the win32com library to interact with COM Objects through Python. You can to install the win32com library with pip. That's how I did it at least. Essentially, you are not clicking the button on your worksheet, but instead calling the subroutine embedded in your VBA code to run on your worksheet.

Also, I used Python 3.6 for this, but I believe Python 2.7 should be compatible. Here is a basic outline.

Install Win32Com

Open up and command prompt and type:

pip install pypiwin32

Code

import win32com.client as win32

excel = win32.Dispatch("Excel.Application") # create an instance of Excel
#excel.Visible = 1 #Uncomment this to show Excel working through the code
book = excel.Workbooks.Open(Filename=r'C:\YourBookHere.xlsm')
excel.Run("YourBookHere.xlsm!Sheet1.MacroName") # This runs the macro that is on Sheet1
book.Save()
book.Close()
excel.Quit()

Hope this helps, this is my first post. Edit: Cleaned up things a bit

Answer 2

To expand upon dylan's excellent answer: you can also accomplish the same using the python xlwings package. In essence xlwings is a fancy wrapper around pywin32 that allows control of an excel application with even simpler syntax. The following python code should do the same as the code from dylan's answer:

import xlwings as xw

# Connect to existing workbook containing VBA macro
wb = xw.Book(r'C:\YourBookHere.xlsm')

# Run the VBA macro named 'MacroName'
wb.macro('MacroName')()