2

I managed to create an Excel file and add VBA project to it using Xlsxwriter and I managed to write dataframes to an Excel using pandas to_excel method.

But I can't combine the two. It seems both methods write a fresh Excel file, so either I end up with a empty Excel fill with the VBA (Pandas to_excel first then Xlsxwriter's add_vba_project) or I end up with the data I want but no VBA (Xlsxwriter's add_vba_projet first and then Pandas to_excel)

Any thoughts on this? Obvious way around this would be to write evrything using Xlsxwriter somehow, but I already spent so much time getting the Pandas to_excel to work the way I wanted that I'd rather avoid that.

Once this gets solved I just need something that changes the filename from .xlsx to .xlsm, but that should be easy right.

Joka
  • 55
  • 9
  • Does this answer your question? [How to write/update data into cells of existing XLSX workbook using xlsxwriter in python](https://stackoverflow.com/questions/18849535/how-to-write-update-data-into-cells-of-existing-xlsx-workbook-using-xlsxwriter-i) – Cohan Jan 25 '20 at 20:41
  • See this answer to a similar question: https://stackoverflow.com/a/28170939/10238 – jmcnamara Jan 25 '20 at 21:03
  • I was a bit too early on my jubilation :) Got everything to work Except for when I try to open it. Excel says the file extension is not valid and that the file might be corrupt. I followed the instructions and change the file name as well as add the VBA bin. PS. I found it easier to get the vbaproject.bin by converting the macro file to a zip, opening and getting the .bin file from there. – Joka Jan 26 '20 at 19:59
  • Does it matter if the vba_project.bin is in the same folder as the Excel fil you're trying to create? The code works when I do it like the example you posted but my script saves the dataframe as an Excel with new name in a new folder (vba_project.bin is in same folder as the python script, different from the new folder created). For someone reason it doesn't work then. I will investigate, maybe theres some detail I'm missing in my code. – Joka Jan 28 '20 at 15:08

0 Answers0