xlsx file extension not valid after saving with openpyxl and keep_vba=true. Need to preserve pivot functioning. How?

976 views

Skip to first unread message

Jeevan Patnaik

unread,

Sep 20, 2015, 2:08:45 PM9/20/15

to openpyxl-users

Question copy pasted directly from stack overflow:

In the environment, we have an excel file, which includes rawdata in one sheet and pivot table and charts in another sheet.

I need to append rows every day to raw data automatically using a python job. I am not sure, but there may be some VB Script running on the front end which will refresh the pivot tables.

I used openpyxl and by following its online documentation, I was able to append rows and save the workbook. I used keep_vba=true while loading the workbook to keep the VBA modules inside to enable pivoting. But after saving the workbook, the xlsx is not being opened anymore using MS office and saying the format or the extension is not valid. I can see the data using python but with office, its not working anymore. If I don't use keep_vba=true, then pivoting is not working, only the previous values are present (ofcourse as I understood, as VBA script is needed for pivoting).

Could you explain me what's happening? I am new to python and don't know its concepts much.

How can I fix this in openpyxl or is there any better alternative other than openpyxl. Data connections in MS office is not an option for me.

As I understood, xlsx may need special modules to save the VB script to save in the same way as it may be saved using MS office. If it is, then what is the purpose of keep_vba=true ?

I would be grateful if you could explain in more detail. I would love to know.

As I have very short time to complete this task, I am looking for a quick answer here, instead of going through all the concepts.

Thankyou!

John Bovey

unread,

Sep 21, 2015, 9:38:21 AM9/21/15

to openpyx...@googlegroups.com

The reason why excel says that the extension is invalid is that excel files containing vba need the extension .xlsm rather than .xlsx. This is generally the case for saved files that were loaded with keep_vba=true. I'm afraid that I don't know anything about pivot table support, but, if they are not supported natively by openpyxl, you may be able to create your own macro to generate a pivot table and have it run automatically when the workbook is loaded by excel.

John Bovey