SharePoint Search and Metadata

BrightWork Reporter and Excel – Import Web Query

February 12, 2013 by

BrightWork Reporter has an ‘Export data to Web Query’ option in BrightWork Reporter that creates a raw export of data that can be very useful once tidied up in Excel. It has the major benefit of exporting all data and being easy to update i.e. click  ‘Refresh All’ on the DATA tab in Excel and it will update the imported data from the original source BrightWork Reporter web part.

Of course, there is still the option to import data into Excel from the web, which does the same thing but can result in a cleaner set of data more easily. The problem in past (I seem to remember) was that this did not work well if the Excel file was stored in a SharePoint library (but don’t quote me on that!).

Anyway, I was playing again with the Excel 2013 import from web again today and was very pleasantly surprised at how well it works with BrightWork Reporters new ribbon actions.

Here is a nice simple process that allows you to pull information from a BrightWork Reporter list item report into Excel and they allows you to save the file in a SharePoint document library from where you can re-edit it, ‘Refresh All’ data and save and close the file to update the report.

Here are the steps:

1. Open Excel and select DATA – From Web

2. This opens the Excel – New Web Query dialog into which you enter the URL of the page you want to pull the report information from

3. On this page format the report using the BrightWork Reporter Ribbon so it contains the columns and format you need in Excel. This can be the Shared Report OR the Personal Report (which I am using here) and then click on the arrow to highlight the report data to import in Excel

4. Click on Import and the data is read in and can be reported upon in Excel

5. Finally, to re-use this Excel report and its formatting, save the file to a SharePoint library (or your local drive) and to refresh the report open it in Excel and select DATA – Refresh All to update the file with the latest data pulled from BrightWork Reporter

Fintan Manning
Latest posts by Fintan Manning (see all)