0

I have an Excel sheet which has a data connection with PowerQuery. Usually, I used to click "Refresh All" to update the data. I want to automate this and wrote a Python script to do that.

However, in Excel I found under Connection Properties an option: "Refresh while opening". Now when I open the sheet, the Refresh automatically triggers and data is updated.

From Python code, I open this sheet and have put a sleep command so that it waits for 30 seconds till the sheet automatically refreshes. After that I am calling "Save As" to save the updated sheet with a different name.

I am using below code:

import win32com.client
import time

# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")

# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open("test.xlsx")

# Optional, e.g. if you want to debug
# xlapp.Visible = True

time.sleep(30)
wb.SaveAs("New.xlsx")

# Quit
xlapp.Quit()

I noticed, that even after "Save As", the data values are not updated. If I directly open Excel, without Python script, the Refresh works and then I manually click Save. It updates. But with above code, it is not saving the updated version.

Please advise.

PV8
  • 5,799
  • 7
  • 43
  • 87
RKh
  • 13,818
  • 46
  • 152
  • 265
  • Which data connection does the excel sheet have? – PV8 Jun 19 '19 at 06:27
  • Power Query which connects to a SAP database. It works fine when I open sheet directly and I manually save. From python, I am not able to save the updated sheet. – RKh Jun 19 '19 at 06:29
  • probably the problem is then, that python does not really open the file, maybe you could execute the python code, while opening your folder with the excel and also make ~$ files visible, if this file does not pop-up for 30 seconds you now that it was not 'opened' – PV8 Jun 19 '19 at 06:38
  • It opens. I tried "visible = true" and the refresh starts. – RKh Jun 19 '19 at 06:45
  • so it works now? – PV8 Jun 19 '19 at 06:46
  • No even after adding "Refresh All" in Python, it did not work – RKh Jun 19 '19 at 09:34

1 Answers1

0

Did you try this, I think in your above coce the wb.RefreshAll()command is missing: Refresh Excel External Data with Python

import win32com.client
import time 
# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")

# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open("test.xlsx")

# Optional, e.g. if you want to debug
# xlapp.Visible = True

# Refresh all data connections.
wb.RefreshAll()
time.sleep(30)
wb.Save()

# Quit
xlapp.Quit()
PV8
  • 5,799
  • 7
  • 43
  • 87
  • In the code I provided in question, I have enabled Auto-Refresh as the sheet opens hence not used "Refresh All". – RKh Jun 19 '19 at 06:25