How to Use Microsoft Excel to Query a SharePoint List

A great way to extract and expose data from SharePoint is by using Microsoft Excel. Of course, not by doing a simple copy-paste, but by using an Excel query.
Editor's note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @tutorialeoffice
A great way to extract and expose data from SharePoint is by using Microsoft Excel. Of course, not by doing a simple copy-paste, but by using an Excel query.
Even though Excel’s query editor is not that great looking (like the one from Microsoft Access), it 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.

Here are the steps

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

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

• 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.

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.

• 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.

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

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

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)

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

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.

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.

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.

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.

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, in the first workbook (created 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.

Author :Alexandru Dionisie