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.
By using the VBA script, the automation can be achieved.
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 “-”
Once you modify the 3 values, press Ctrl+q to start the process and the data will be imported to the worksheet “Data”.