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

DJSDev · Accepted Answer · 2017-09-23 04:12:57Z

5

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

edited Sep 23, 2017 at 4:12

answered Sep 23, 2017 at 1:23

DJSDev

1,01710 silver badges19 bronze badges

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

1 Comment

David Pesetsky Over a year ago

Hey, you use "Sheet1" to locate the macro. Is there a way to specify the actual sheet name? I tried Worksheets("name"). No dice.

Xukrao · Accepted Answer · 2017-09-24 14:44:30Z

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')()

answered Sep 24, 2017 at 14:44

Xukrao

8,6745 gold badges29 silver badges58 bronze badges

Comments

ASH · Accepted Answer · 2017-09-23 19:09:10Z

0

In addition, you can also use Windows Task Scheduler to fire the event. If a button-click-event fires the code, you can do exactly the same thing, by following the instructions in the link below.

https://www.sevenforums.com/tutorials/11949-elevated-program-shortcut-without-uac-prompt-create.html

Also, you will need a Workbook-open-event, so, something like this...

Private Sub Workbook_Open()
Msgbox "Welcome to ANALYSIS TABS"
End Sub

answered Sep 23, 2017 at 19:09

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Collectives™ on Stack Overflow

how to execute vba macro outside of excel

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related