How to have python wait until an Excel Macro/Refre

Question

I am using Python to run a macro in excel. and I want Python to close excel. The macro refreshes a data connection in excel which can be slow.

How do I have python wait until the refresh is done to close. This is what I am using, I need something before the xl.Quit that will wait until the refresh in macro is done????

 import win32com.client
 import os

 xl = win32com.client.DispatchEx("Excel.Application")
 wb = xl.workbooks.open("X:\Backoffice\Options Trading\BloombergRate.xlsm")
 xl.Visible = True
 xl.run("Refresh")
 xl.Quit()

Wait ways can I make this work?

Answer 1

You don't even have to set up a macro to run this. win32com has a native method to refresh all data connections.

import win32com.client
import os

xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open("X:\Backoffice\Options Trading\BloombergRate.xlsm")
xl.Visible = True
wb.RefreshAll()
xl.Quit()

Answer 2

Edit your Refresh macro to set QueryTable.BackgroundQuery property to False. This should cause the macro to block until it is done.