I have been trying to take a Pandas Dataframe in Python and append it to a certain worksheet and range in Excel. I currently have it working with this code.
file = "Test.xlsx"
book = load_workbook(file)
writer = pd.ExcelWriter(file, engine='openpyxl', mode='a')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
df.to_excel(writer, sheet_name='TestSheet', startrow=3, startcol=2, index=True, header=False)
writer.save()
This code works, but ideally, I would like to append the dataframe to a macro enabled excel file. Whenever, I try using a macro excel file, the program runs just fine without errors, but when I open the excel file, I am met with "Excel cannot open the file because the file format or file extension is not valid"
Does anyone know why the program seems to be corrupting my excel file, but only when macros are enabled?
