Excel - Power query data refresh via Python

Question

I was trying to refresh a power query - external data in Excel via Python using following code

import win32com.client
import time
xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible = True
wb.RefreshAll()
wb.Save()
xl.Quit()

it works fine and serves the purpose, except, only for one time. The visibly open excel file closes but in task manager it is not being closed completely. Though I dont have much issue with that, except, when I try to run this for the next file, the 'Power Query' add-on stops loading, and I get error saying, The power query add-on need to be loaded in order to refresh this data. I have to go to task manager to close the excel.then, I have to go to options, (where power query add-on is already loaded), disable the add -on, close the excel, open again, re-load add-on .

Please has anyone ever encountered this type of issue, or how to get out of it. my whole intention is to refresh some files in excel , with external data on a daily basis by some type of automation.

Edit: Found the solution, add following line at the end of the code

import os
os.system("taskkill /f /im excel.exe")

Thanks

Marquet

Just found the solution, in case anyone else is having trouble.Put following line at the bottom to kill excel. import os os.system("taskkill /f /im excel.exe") – Marquet

Answer 1

In addition to the above, consider this.

IF the query property "Enable Refresh in the Background" ENABLED THEN wb.refreshAll() will immediately return.

There are ways to check the refresh status e.g. wb.Sheets.QueryTables.QueryTable.Refreshing but this doesn't work for Power Queries because wb.QueryTables returns null, even when called individually iterated over the sheets and querytables

Therefore, the only workaround for this is to DISABLE "Enable Refresh in the Background" in ALL the queries for refreshAll

enter image description here

Windham

Thx @Nic Johnson - it wasn't refreshing until I turned off thge background refresh – Mortensen

Answer 2

I know this is an old question, but I just came across it. You need to close the Excel Workbook object before you quit Excel. Oddly, when you quit the Excel object the Excel Workbook still remains in memory and operating -- it's a little confusing. You should replace the wb.Save() command with the wb.Close(True) command.

import win32com.client
import time
xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible = True
wb.RefreshAll()
wb.Close(True)
xl.Quit()

That said, keeping the os.system("taskkill /f /im excel.exe") as a backup is good as well. I've found the Close and Quit commands are not fool proof and can cause problems if you don't periodically kill the outstanding Excel tasks.

Buttonhook

I found I needed to disable background refresh as per additional advice/response posted by @Nic Johnson – Mortensen