Python: update pivot tables in worksheet

Question

I am creating a python script that will allow me to open an Excel 2010 worksheet and print it.

I got most of the way

import win32com.client office = win32com.client.Dispatch("Excel.Application") wb = office.Workbooks.Open(r"path\to\excel\file\to\print.xlsm") count = wb.Sheets.Count for i in range(count): ws = wb.Worksheets[i] pivotCount = ws.PivotTables().Count for j in range(1, pivotCount+1): #TODO code to refresh each pivot table ws.PrintOut() print "Worksheet: %s - has been sent to the printer" % (ws.Name) 

As you can see, I am still missing updating the pivot tables in the worksheet.

Update Code for VBA:

 ActiveSheet.PivotTables(1).PivotCache.Refresh 

I cannot break the code into python win32com syntax. The closest I got:

 wb.WorkSheets(5).PivotTables(1).PivotCache.Refresh 

which gives a <bound method CDispatch.Refresh of <COMObject PivotCache>> but does not result in such a worksheet.

Any help would be greatly appreciated!

Decision

In the end, I found the solution myself, but I am going to leave a message so that it helps all programmers with a similar problem.

 import win32com.client office = win32com.client.Dispatch("Excel.Application") wb = office.Workbooks.Open(r"path\to\excel\file\to\print.xlsm") count = wb.Sheets.Count for i in range(count): ws = wb.Worksheets[i] ws.Unprotect() # IF protected pivotCount = ws.PivotTables().Count for j in range(1, pivotCount+1): ws.PivotTables(j).PivotCache().Refresh() # Put protection back on ws.Protect(DrawingObjects=True, Contents=True, Scenarios=True, AllowUsingPivotTables=True) ws.PrintOut() print "Worksheet: %s - has been sent to the printer" % (ws.Name) 

Remember to vote if you want or have used the code.

Answer 1

I found my problem. I had protection on the worksheet ... Stupid for me ..