skip to Main Content

A great way to extract and expose ta from SharePoint is by using Microsoft Excel.
Of course, not by doing a simple copy-paste, but by using a Excel query.

Even though Excel’s query editor is not that great looking (like the one from Microsoft Access), still is functional and very helpful.
I am going to use a query in Excel to extract only a part of a SharePoint list data.

What are the steps ?

  • Open the SharePoint list and from the List tab click on Export to Excel command button.

How to use Microsoft Excel to query a SharePoint list ?

  • Save the Microsoft Excel Web Query file.
  • If you want to see that query file content, open it using Notepad.

How to use Microsoft Excel to query a SharePoint list ?

  •  Double click on the Microsoft Excel Web Query file to open it using Microsoft Excel.

A new Excel file opens and displays the SharePoint list data.
The biggest disadvantage of that query file is that you can’t edit it and add custom SQL code.

Still, you can create your own query, with a custom SQL code.
As you can see in the bellow image, that workbook is connected to the SharePoint list through that Microsoft Excel Web Query file.

How to use Microsoft Excel to query a SharePoint list ?

Save the above Excel file and then open a blank workbook.
From the Data tab click on the From Other Sources button and then click on the From Microsoft Query option.

How to use Microsoft Excel to query a SharePoint list ?

  • make sure that the Use the Query wizard  to create/edit queries is unchecked.
  • click on the New Data Source option (form 1) and then click on the OK button.
  • in form 2:
    • add a name for the new source (step 1);
    • select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) step 2
    • click on the Connect command button (step 3);
  • in form 3 click on the Select Workbook command button;
  • in form 4 select your workbook.

How to use Microsoft Excel to query a SharePoint list ?

Returning to form 2, we have to choose a default table (or sheet) – step 4.

How to use Microsoft Excel to query a SharePoint list ?

Now we have a new source.
To add a custom SQL code, just click on the OK command button.

How to use Microsoft Excel to query a SharePoint list ?

In the query editor I will enable the Criteria pane (from the View menu) because I want to see certain products that have a custom price.

More than that I want to see only some column: Categorie, Produs and Pret.

Criteria:

  • Product category (Categorie = LCD 22);
  • Custom Price (Pret >=400)

How to use Microsoft Excel to query a SharePoint list ?

 If I want to see how the SQL code looks like, I will click on the SQL button from the toolbar.

How to use Microsoft Excel to query a SharePoint list ?

To add the query result in a sheet, just close the query editor.
Now, the Import Data form is displayed.
Choose a cell to put the data in and the click the Properties command button.

How to use Microsoft Excel to query a SharePoint list ?

In this form we have to check the first and the third option, so that the data updates in the background and when the file open.

How to use Microsoft Excel to query a SharePoint list ?

After we confirm all the options, the data will be imported in the sheet.
Now we can create custom reports and charts, based on this data. How to use Microsoft Excel to query a SharePoint list ?

If you want others to use this method, just export the query from the query editor.
Then, all they have to do is to double click on the query and the data is imported.

To edit the query you can use Notepad or the query editor.

How to use Microsoft Excel to query a SharePoint list ?

After some tests, it seems that the data is not refreshing.
Why ? Because the first Excel workbook (the one created after running the Web Query) wasn’t updated either.
So, update the first workbook and then the second one.

To avoid the above procedure, into the first workbook (create by the Web Query) we must export the second query and import it into the first workbook.
Now, we have only one workbook that contains the Web Query and the ODBC Query.

How to use Microsoft Excel to query a SharePoint list ?

Cosmin Tătaru

Pasionat de blogging și tehnologie încă din 2009, Cosmin este aici să vă ajute să vă creșteți vânzările și productivitatea punând tehnologia la treabă pentru dumneavoastră.

Mergeți sus

Dacă derulezi pagina sau dacă navighezi pe acest site, îți dai acordul să folosim cookies. Cookie consent --> vezi detalii

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close