How to import SharePoint list to Excel by VBA?

Problem

Excel is a good tool for data analysis and report generation, we may use VBA to import data from database such as SQL Server. When we need to export SharePoint data to excel, the out-of-the-box method is to click the “Export” button in SharePoint ribbon, this is not automation.

Solution

By using the VBA script, the automation can be achieved.

Import SharePoint List to Excel by VBA

You need to modify 3 values before use:

SERVER: the site collection URL, do not input “/” at the end

LISTNAME: the GUID of the list, view it in “Modify View” > “Mobile”, replace “%2D” by “-”

VIEWNAME: the GUID of the view, view it in “Modify View” > “Mobile”, replace “%2D” by “-”

Result

Once you modify the 3 values, press Ctrl+q to start the process and the data will be imported to the worksheet “Data”.