Refresh power Query in Excel

Question

I have an excel application, that gets consolidated lists from SharePoint using power query. I have set the property to refresh on open of the excel application, to refresh the dataset. When I manually open the excel, it refreshes and i get the updated data. However, I am using this excel in my winform application to get the data. So in order to refresh the data, i am trying to open and close the excel from code.

But the data doesn't get refreshed, and it screws up the power query addin. When I manually open the excel sheet again, after running the code i get the error "The query did not run, or the database table could not be opened."

And the Power Query is not seen anymore on the ribbon. Below is the code i use to open and close the Excel application:

 Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;
        string workbookPath = @"C:\Test\Test.xlsx";
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);
        Excel.Sheets excelSheets = excelWorkbook.Worksheets;
        string currentSheet = "Sheet1";
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
        Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A1", "A2");
        excelWorkbook.Close(true, "Test.xlxs", null); 

Is this the best way to refresh the data?

Answer 1

If your Power Query/BI implementation does not allow usage of the Data Management Gateway (DMG) as suggested by a previous poster, you may want to explore a new piece of software called Power Update to set the update to a schedule.

Plus you may be able to run their Excel data-source update tasks from within your C# application if this is part of the requirement.

Answer 2

Two things to be aware of.

1. Connections in excel default to background (asynchronous) refresh, which can make your programmatic refresh code non-deterministic.
2. Power Query does polling to update the status of the query refresh. So if you close the workbook right after the refresh the status won't have a chance to update and next time you open the workbook the status will indicate an error.

In order to get a reliable refresh, you will need to set the connections to no longer background refresh, so the refresh blocks, and then also sleep for a short time after the refresh to let Power Query's polling discover that the refresh is complete.

I would change your script to this

Excel.Application excelApp = new Excel.Application();
    excelApp.Visible = true;
    string workbookPath = @"C:\Test\Test.xlsx";
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
            0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
            true, false, 0, true, false, false);
    excelWorkbook.RefreshAll();
    System.Threading.Thread.Sleep(2000);
    excelWorkbook.Close(true, "Test.xlxs", null); 

You could make this code even better by looping through the queries and setting them to not background refresh like this vba code does https://social.technet.microsoft.com/Forums/en-US/9cbdd731-d139-49ee-8f30-89de371ba74d/multiple-queries-run-one-after-another?forum=powerquery#df2947a7-585a-4f75-8337-e0ead5068254