Excel Automation using Python win32

[anubhab89]

Dear Members,

I have a scenario where an excel workbook contains a data sheet and another sheet which has a pivot table derived from the data sheet.
Every month new data needs to be loaded into the data sheet (appended/freshly added) and the pivot tables refreshed.
So my idea was to read the existing excel structure to a pandas data frame, append the new information and dump it to the data sheet or write fresh information into the data sheet and then use pywin32 to refresh the pivot tables.
However, as soon as I modify the data sheet using pandas, the pivot table gets dismissed.
Request your kind suggestions and guidance based on your experience and expertise please.

 

[Richynero]

Why dont you use Power Query? Save the files down in a folder, then use Get Data from Folder. Do your data cleansing and then drop out in a table or, just drop out as a pivot tabe. No need to get fancy with the Python scripts. However, if you do want to explore Python and Excel have a look at the book Automate the Boring Stuff.