I am currently working on a project that require me to write a pandas DataFrame to an EXISTING Macro Enabled Excel File (.xlsm), and then saved the file as a different (.xlsm) file.
EXISTING File: "Structure Report Template.xlsm", where the "Sheet1" contains a vba button. I want to write the DataFrame to "Sheet2".
I hope someone could help with the issue please, thank you in advance for your time.
I have tried to use [pd.to_excel], but it only supports '.xls' and 'xlsx' files. Using '.xlsm' extension will cause an error.
I also tried to create workbook object from openpyxl, and use _[workbook.add_vba_project('vbaProject.bin')]_, but it also causing the same error when I am trying to open the new excel file:
Excel cannot open the file 'myFilename.xlsm' because the file format or file extension is not valid. Verify > that the file has not been corrupted and that the file extension matches the format of the file.
import pandas as pd
import openpyxl
import shutil
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
template='Structure Report Template'
new_file_name='VGC Structure Report'
shutil.copy(template,new_file_name)
writer=pd.ExcelWriter(new_file_name+'.xlsm',engine='xlsxwriter')
vgc_endfile.to_excel(writer,sheet_name='Sheet2')
workbook=writer.book
workbook.filename=new_file_name+'.xlsm'
workbook.add_vba_project('vbaProject.bin')
writer.save()
wb=load_workbook(new_file_name+'.xlsm')
sheet2=wb['Sheet2']
for x in dataframe_to_rows(vgc_endfile):
sheet2.append(x)
wb.save(new_file_name+'.xlsm')
What I want to achieve:
- open the 'Structure Report Template.xlsm'
- write dataframe to 'Structure Report Template.xlsm' and "Sheet2"
- Save 'Structure Report Template.xlsm' as 'VGC Structure Report.xlsm'
