Is there a way to reference the active workbook when calling a python program via vba in excel?

Question

I am trying to call a python script via a macro in excel. This script needs to reference the excel file it is being called from.

I know that this question offers help regarding how to call the script in vba: How to call python script on excel vba? However, my python script immediately tries to reference the excel docs path/name. Is there a way to assign a variable to these values? My python code (see below) needs a reference path/doc to pull the correct data from excel.

workbook_path = r'C:\Users\jrwaller\Documents\Automated Eve'
workbook_name = r'PMT Project 13.xlsx' 
import openpyxl
wb = openpyxl.load_workbook(fr'{workbook_path}\{workbook_name}')

Where the path and name could be changed from project to project. Is there a way to assign the workbook ordering the script to run as wb?

See: https://stackoverflow.com/q/1009860/1188513 – Mathieu Guindon Jul 24 '19 at 20:11 — Mathieu Guindon, Jul 24 '19 at 20:11

score 2 · Accepted Answer · answered Jul 24 '19 at 20:17

Pass them as command-line arguments.

from argparse import ArgumentParser
import openpyxl

parser = ArgumentParser()
parser.add_argument("-f", "--file", dest="filename",
                    help="the full path of the Excel workbook to work with", metavar="XLWB")    
args = parser.parse_args()
wb = openpyxl.load_workbook(args.filename)

Is there a way to reference the active workbook when calling a python program via vba in excel?

1 Answers1