How do I make an Excel RefreshAll wait to close until finished?

Question

I'm trying to refresh an Excel file with the following Python Script:

xl = Dispatch('Excel.Application')
workbook = xl.Workbooks.open('\\path\to\workbook.xlsx')
xl.Visible = True
workbook.RefreshAll()
xl.Quit()

However, the background query (connected to SQL database) takes a while to refresh.

How do I prevent this spreadsheet from closing before the RefreshAll is finished?

http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba — Tim Williams, Dec 15 '15 at 18:46
I think this is a duplicate of this: http://stackoverflow.com/questions/11832628/how-to-have-python-wait-until-an-excel-macro-refresh-is-done — Ward W, Dec 16 '15 at 19:04

score 4 · Answer 1 · answered Jun 03 '19 at 21:27

CalculateUntilAsyncQueriesDone() will hold the program and wait until the refresh has completed.

xl = Dispatch('Excel.Application')
workbook = xl.Workbooks.open('\\path\to\workbook.xlsx')
xl.Visible = True
workbook.RefreshAll()
xl.CalculateUntilAsyncQueriesDone()
xl.Quit()

Ward W · Accepted Answer · 2015-12-17T02:10:48.467

For 'query' & pivot tables, you can do this You gotta loop through each sheet & query/pivot** table to set background refresh to False, since it's a property of the table itself (and not at the workbook level, say.)

**For pivot tables, the PivotTables in the Python module doesn't seem to be iterable, so instead I basically set a counter (adjust as needed) to look at the maximum (expected!) number of pivot tables per worksheet (I set it to 5). This stops once it finds no pivot table per the index number from 1 to 5. (This assumes the indexes are continuous from 1 on up, and you can't, say, have NO PivotTables(1) but have a PivotTables(2). If that's false, please correct my answer!

for sheet in workbook.Sheets:
    print(sheet.name)
    for table in sheet.QueryTables:
        print("Found a query table on %s called %s" % (sheet.name, table.name))
        table.BackgroundQuery = False # i.e.: disable background query, and therefore cause Excel to 'wait' until it's done refreshing
        if table.Refresh() == True:     # This both refreshes the table, AND if the Refresh() method returns True (i.e.: refreshes successfully), tells you so.
            print("Query table %s refreshed!" % table.name)
        else:
            print("Query table %s FAILED to refresh." % table.name)
    for i in range(1,5):
        try:
            print("Found pivot table #%s called %s" % (i,sheet.PivotTables(i).Name))
            if sheet.PivotTables(i).RefreshTable()==True:
                print("Refreshed pivot table %s" % sheet.PivotTables(i).Name)
            else:
                print("FAILED to refresh pivot table %s" % sheet.PivotTables(i).Name)
        except:
            print("No pivot table #%s found on sheet %s" % (i,sheet.Name))
            break  # Break out of the for loop, since there's no point in continuing the search for pivots, I believe (but could be wrong about that!  Try creating two pivots, then deleting the first.  Does the second become PivotTables(1), or stay as 2?)

Note: I haven't figured out how to disable background query refresh for the pivot tables... Not sure if that will be a problem for you. — Ward W, Dec 17 '15 at 02:17
currently it is not a problem, as I don't need a pivot table (for now, at least). I'll have to look into it eventually, I'm sure. Thanks again! If I do, I'll try your pivot table suggestion and see how that fares! — codycrossley, Dec 17 '15 at 16:51

How do I make an Excel RefreshAll wait to close until finished?

2 Answers2