Wait for Workbook.RefreshAll() (C#)

Question

I want to loop through a directory (using C#) and Refresh all Excel sheets in there. I use:

Workbook.RefreshAll();

How can I wait for the Workbook.RefreshAll() statement to finish?

The problem is: I open FileA, then Workbook.RefreshAll() and then open FileB - Refresh for FileA is terminated. The only solution I found is to call something like

System.Threading.Thread.Sleep(20000);

which I found very unlovely...

Does someone know a better way to wait?

possible duplicate of [Excel vba refresh wait](http://stackoverflow.com/questions/8925403/excel-vba-refresh-wait) — Richard Morgan, Sep 13 '13 at 17:21

Frank · Answer 1 · 2016-05-31T06:46:17.480

4

Updated 31.05.2016...

Thanks for your help. I found the following solution:

foreach (MSExcel.WorkbookConnection cnn in wb.Connections)
{
    if (cnn.Type.ToString() == "xlConnectionTypeODBC")
    {
        cnn.ODBCConnection.BackgroundQuery = false;
    }
    else
    {
        cnn.OLEDBConnection.BackgroundQuery = false;
    }
}

Frank

edited May 31 '16 at 06:46

answered Sep 16 '13 at 07:53

Frank

147
1
9

You are updating every workbook connection once for every sheet, the outer loop is pointless. – May 30 '16 at 11:30
This always returns error: System.ArgumentException: 'The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))' – OptimusPrime Sep 02 '19 at 19:29

Babulal · Answer 2 · 2021-05-17T18:33:25.400

3

The below code will work perfectly when your excel has to run background query to pull data from SQL Server/MS Access and it will wait for the query is successful.

public void RefreshSheet(Excel.Application Excel, Excel.Workbook WB)
{
    WB.RefreshAll();
    Excel.Application.CalculateUntilAsyncQueriesDone();
    WB.save();
}

Here WB is the workbook and Excel is the Excel Application.

edited May 17 '21 at 18:33

answered Mar 09 '18 at 11:11

Babulal

349
3
11

score 2 · Answer 3 · answered Sep 13 '13 at 14:50

If you go through your Workbook and set the the BackgroundQuery property of all of your QueryTables to false, RefreshAll should finish before moving on.

foreach (Worksheet worksheet in workbook.Worksheets)
{
    foreach (QueryTable table in worksheet.QueryTables)
        table.BackgroundQuery = false;
}

//This will finish executing before moving on
workbook.RefreshAll();

score 1 · Answer 4 · answered Sep 13 '13 at 14:38

1

Maybe you can query for the State:

Application.CalculationState =xldone

For Documentation look here: http://msdn.microsoft.com/en-us/library/office/bb220901(v=office.12).aspx

answered Sep 13 '13 at 14:38

Christian Sauer

10,351
10
53
85

Wait for Workbook.RefreshAll() (C#)

4 Answers4

Linked