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

Answer 1

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.