Pandas: Writing Dataframe to an open Excel File

Question

I am trying to call this python script from VBA in Excel with the click of a button, it will read the data but it will not write if the Excel file is open.. Everything works fine when the file is closed, but to run it from an excel macro, the file needs to be open. Is this possible?

Error:

PermissionError: [Errno 13] Permission denied: 'SampleTest.xlsm'

import pandas as pd
from openpyxl import load_workbook

path = 'SampleTest.xlsm'

# Write to excel with Openpyxl
book = load_workbook(filename=path, read_only=False, keep_vba=True)
writer = pd.ExcelWriter(path, mode="a", engine="openpyxl", if_sheet_exists="replace")
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
dfMatches.to_excel(writer, "Matches", index=False)
writer.save()

Answer 1

No, this can't be done with openpyxl

What you want is xlwings. This module can run on live excel files - in fact, you can set it up to run python scripts directly from within Excel. This should do exactly what you want. The docs can be found here.

A quick example of how you may use xlwings (with an Excel file open).

import xlwings as xw 

xw.Range('A1').value = "I wrote to this cell from xlwings"