Refresh External Data in a Excel Power View workbook hosted on Office 365

One of the cool features of Excel is that you can publish your workbook to different places like SharePoint or even Office 365 , the nice thing about publishing in Office 365 is that usually you can share it with people across the world and accessing it without connection to your corporate network.

Although there are plenty of interesting features one of the limitations is that “In a Excel workbook on Office 365, you can’t refresh data that comes from an external data source.” Power View In Excel in SharePoint Server or SharePoint online in Office 365, but I wanted my workbook to get refreshed daily.

I give it a thought and remembered that Excel supports automation in .NET via Interop (Overview of Classes and Interfaces in the Office Primary Interop Assemblies), then was just a matter to play with some of the classes available and create a command line application that open a Excel Workbook, refresh the data , save it and close it, the interesting part is that Excel Interop classes support any kind of location for your file including a Office 365 url.

I was surprised how simple the code is, first you need to add a reference to Microsoft.Office.Interop.Excel and then you can add this method to your code.

 private static void Refresh(string excelFilePath)
{
    Console.WriteLine("Starting the refresh of the excel workbook {0}", excelFilePath);
    Application excel = new Application();
    try
    {
        excel.Visible = false;
        excel.DisplayAlerts = false;
        WriteLine("Opening the workbook");
        Workbook wb = excel.Workbooks.Open(excelFilePath);
        WriteLine("WorkBook opened");
        WriteLine("Refreshing the workbook");
        wb.RefreshAll();
        WriteLine("WorkBook refreshed");
        WriteLine("Saving the WorkBook");
        wb.Save();
        WriteLine("WorkBook Saved");
        wb.Close();
    }
    finally
    {
        excel.Quit();
    }
}

You can call it with a local file path of a url for Office 365 and it will open the workbook, refresh the data and save it back.

It addresses my immediate needs but still have some limitations, you need to have Excel 2013 installed in the machine where you are running this code, and I have seen that if your saved your Excel spreadsheet with a Powe View selected (Excel remembers what is your active sheet and open it back when you open the file) the Excel UI shows up even with the Visible property is set to false.

 

This posting is provided "AS IS" with no warranties, and confers no rights

Comments

• Anonymous
  April 12, 2013
  Coolest and most helpful tool ever!! Thanks Jaime!

• Anonymous
  January 15, 2014
  Great job, this is exactly what I was looking for; except my workbook closes before it completes refreshing. Do you have any ideas on how to have it wait until the book is fully refreshed?

• Anonymous
  December 16, 2015
  And how difficult is this for Office 365 to add this code to their portal :(

• Anonymous
  December 17, 2015
  vpfaiz, what about using Power BI for your scenario, it support automatic refresh from plenty of data sources