I have tried to find the answer online but only got confused. I am a windows user, have Python 2.7 and work with Excel 2010 files saved on sharepoint, trying to automate the data extraction. Basically, my solitaire-long program opens the files one by one, extract the data and saves them into a new xl file. Hitherto I have used xlwt and xlrd and everything was going pretty smoothly. But now I have encountered a file xlsm that contains the pivot tables that need to be refreshed every time . I have googled it up and found the code:Python: Refresh PivotTables in worksheet The problem is it does not work for me at all... I keep getting attribute errors like
AttributeError: .Open
I have noticed that the syntax differs substantially, too (wb.Sheets.Count versus wb.nsheets). With win32com I am not able even to iterate through the sheets of a workbook... I just do not have a clue what is a problem - the Python version, importing problem or whatever... The thing that I cannot find in xlrd/xlwt: ws.PivotTables(j).PivotCache().Refresh() - if I am not mistaken, the problem is that with xlrd/xlwt I am not actually opening Excel file, so probably it is not possible to refresh data using them... Alas, moving to win32com.client hasn't helped... Any suggestions or links? :) I am probably gonna add an automatic update (refresh) to a VBA code of the xl file in question, but I'd rather not change the files, but my code :)
Edit: I paste my code below, along with an error I keep getting: the below thing, copied from someone else's code, does not work, returning an AttributeError: Property 'Excel.Application.Visible' can not be set.:
import win32com.client
import os
xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open("//some_dir.xlsm")
xl.Visible = True
wb.RefreshAll()
xl.Quit()
Alas when I try to merge it into a program that uses also xlrd and xlwt it does not work...
from copy import deepcopy
from xlrd import open_workbook
from xlutils.copy import copy as copy
from xlwt import *
from datetime import *
#some definitions here...
rb = open_workbook('my_template.xlsx')
wb = copy(rb)
sheet_1 = wb.get_sheet(0)
sheet_2 = wb.get_sheet(1)
#now the code that evaluates the dates:
import datetime
ys = raw_input('year in yyyy format:\n')
ms = raw_input('month in mm format:\n')
ds = raw_input('day in dd format:\n')
#here the definitions concerning dates are called, all in xlwt and xlrd
#now other files are being opened and data extracted and written into sheet_1 and sheet_2
#now it's time for refreshments ;) - I wanted to 1. open and update the file with win32com, #close it and 2. with xlrd get the data from the updated file:
#refreshing all (preparing file)
import win32com.client
import os
xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open("some_dir.xlsm") #it is a shared file saved in intranet
xl.Visible = True
wb.RefreshAll()
xl.Quit()
print 'file refreshed'
file = open_workbook("some_dir.xlsm")
row_sheet1 = 176
row_sheet2 = 248
for sheet in file.sheets():
print sheet.name
#... the rest of the code ensues
The error again is: AttributeError: Property 'Excel.Application.Visible' can not be set.
I guess I am not supposed to use xlrd and win32com.client simultanously and my whole idea is just soooo wrooong? ;) But why does it not work "alone", I mean the first, short code?
