[Python] Excel Refresh using win32com

def excel_refresh_query_replace(path, query_list = [], old_str = '', replacement_str = '', index_update = False, max_bk_date = '', sub_list = []):

Application = Dispatch("Excel.Application")
#Application.DisplayAlerts = False


#opens the excel file
wb = Application.Workbooks.Open(path)

if wb.ReadOnly:
wb.Close(False)
try:
os.system("taskkill /f /im excel.exe")
time.sleep(5)
except:
pass
return True

#if variables passed in then replace in query string
if query_list != []:
for query_name in query_list:
#existing query is initialized
current_q = wb.Queries(f"{query_name}").Formula
#replace string requires the new string as well as the old string to be replaces
new_q = current_q.replace(old_str,replacement_str)
wb.Queries(f"{query_name}").Formula = new_q

#refresh
wb.RefreshAll()
Application.CalculateUntilAsyncQueriesDone()

if sub_list != []:
for sub_name in sub_list:
Application.Run("sub_name")

#Updates the Index tab if required
if index_update:
wb.Sheets("Index").Range("E5").Value = f"Last Updated: {datetime.now().strftime('%d/%m/%Y')}"

if max_bk_date != '':
wb.Sheets("Index").Range("E6").Value = f"Last Booking Date Updated: {max_bk_date}"

#Save & Close
wb.Close(True)

try:
os.system("taskkill /f /im excel.exe")
time.sleep(5)
except:
pass

return False


This function works completely fine but due to large data, when I pass multiple queries, it doesn't refresh all the files. I loop around 500 files to refresh and around 70 files doesn't refresh fully. I get "Data not completely downloaded" issue. It refreshes completely fine when I try refresh individual query manually. Any suggestion how-to refresh queries individually.

Tried wb.Queries(f"{query_name}").Refresh() but gives out the below error

wb.Queries(query_name).Refresh() File "<COMObject <unknown>>", line 2, in Refresh pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)```

ps: I am using a query connection from SQL server

Continue reading...