I have a Excel xlsm file with a few queries.
Currently I open it every day and click on the "Refresh All" command in the "Data" tab. I want this to be done automatically. I wrote a script in python (I'm a newbie in Python).
The problem is that after the data is refreshed and the Excel file has been saved, the refreshed data is not visible (I know that refreshing works because if I prevent to save and close the Excel file, the refreshed data is visible in the file )
The weird thing is that saving also works fine because when I try to modify cell B2 from 'config' it is changed...
Where is the problem?
import win32com.client
import datetime
from datetime import date, timedelta
yesterday = date.today() - timedelta(1)
office = win32com.client.Dispatch("Excel.Application")
wb = office.Workbooks.Open(r'\\server\CC_source.xlsm')
office.DisplayAlerts = True
office.Visible = True
wb.RefreshAll()
sh = wb.Worksheets("config")
sh.Cells(2,2).Value = wczoraj
wb.Close(True)
Maybe someone can recommend another script? For example, Powershell? PHP? Or an other library for Python3?