Automate the export of SharePoint List data to Excel to accommodate historic ‘point-in-time’ reporting

Using Microsoft SharePoint as an IT Project Portfolio Management (PPM) and Governance tool has its pros and cons in medium- to large-sized organizations. But the focus of this article isn’t to debate using SharePoint, nor is it even focused on PPM and Governance. Instead, this article explains how I solved the problem of quickly producing historic ‘point-in-time’ reports from a SharePoint project portfolio management (PPM) solution for when the CIO asks questions like, “How many projects were ready for a gate review at the end of Q3 last year?” 

This solution can be applied to any SharePoint list to create daily, weekly, or monthly backups in Excel. Now almost any question regarding the ‘state’ of the dataset on a date in the past is a quick, ad-hoc pivot-table away from the answer.

Because when you’re in a business operations role reporting to the CIO, minutes matter!

I’ve structured this tutorial into four major steps:

  1. Create a personal SharePoint View that includes all List field columns
  2. Export the view to Excel, automatically creating the read-only connection
  3. Add custom Visual Basic for Application (VBS) code into the workbook
  4. Create a Visual Basic Scripting (VBS) text file that runs at PC startup, or desired intervals using Windows Task Scheduler

Create a personal SharePoint View that includes all List field columns

The first step is to create a personal SharePoint View (Standard View) that includes all list field columns. 

To create the view in SharePoint, navigate to the desired list’s ‘list’ tab, choose the ‘Create View’ icon, choose ‘Standard View’ as the view type, define a view name, and select (using the checkboxes) every column in the list. An optional step is to apply any desired sorting to the data. 

Export the view to Excel, automatically creating the read-only connection

The second step is to export the newly-created view to Excel using the .xlsm (macro-embedded) format as a workbook named ‘create-sharepoint-list-snapshot.xlsm’.

Within SharePoint, select the new view and then choose the ‘Export to Excel’ icon on the ribbon. When the file is saved, it will include a read-only connection to the SharePoint list that can be refreshed from SharePoint at any time by clicking the ‘Refresh All’ icon on the ‘Data’ tab ribbon in Excel. For this tutorial, use the ‘Save As’ option to save the file using the .xlsm (macro-embedded) Excel format somewhere on the PC or network.

Add custom Visual Basic for Applications (VBS) code into the workbook

The third step is to add custom code to the newly-created workbook that, when opened, will refresh the list data from SharePoint and save it as a new date-and-time-stamped Excel file in a desired folder on the PC or network.

Using the Excel file created in step two, press ALT+F11 to open Excel’s Visual Basic editor. In the left-hand Project Explorer (tree view), double-click ThisWorkbook and paste the code below into the editor’s body window. 

The only two lines of this code that require editing are lines #2 and #3, to update the value of the FILE_PATH and FILE_NAME constants. The FILE_PATH value can be a local or network drive and should end with a backslash. The FILE_NAME value should be a desired Excel file using the .XLSX extension. 

Finally, press CTRL+S to save the code, then close the Visual Basic editor. Then Save, Close, and move the original Excel workbook that contains the code to a desired folder from which it will reside.

# # # BEGIN CODE TO PASTE INTO 'create-sharepoint-list-snapshot.xlsm' WORKBOOK
Option Explicit
Private Const FILE_PATH = "C:\Users\Matt\OneDrive\sharepoint-list-snapshot\"     'Ensure the path ends with a \ character
Private Const FILE_NAME = "sharepoint-list-all-fields-and-values.xlsx"           'Excel file name ending with .xlsx extension
Dim strFilenamePrefix As String                                                  'procedure-level scope variable
Private Sub Workbook_Open()
         ' Create a snapshot of the list fields and values when this workbook opens
         Call CreateListSnapshot
End Sub
Sub CreateListSnapshot()
         Call SetFilenamePrefix
         ' Refresh the "query" (connected) worksheet from the SP list
         ' Select all data
         ' Paste the values into a new workbook
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         Application.CutCopyMode = False
         On Error Resume Next
         ActiveWorkbook.SaveAs Filename:=FILE_PATH & strFilenamePrefix & FILE_NAME, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         On Error GoTo 0
End Sub
Private Sub SetFilenamePrefix()
         Dim strMonth As String
         Dim strDay As String
         ' Append zero for months 1 thru 9
         strMonth = Month(Now)
         If Len(strMonth) = 1 Then strMonth = "0" & strMonth
         ' Append zero for days 1 thru 9
         strDay = Day(Now)
         If Len(strDay) = 1 Then strDay = "0" & strDay
         ' Set the procedure-level scope variable
         strFilenamePrefix = Year(Now) & strMonth & strDay & "-"
End Sub
# # # END CODE TO PASTE INTO 'create-sharepoint-list-snapshot.xlsm’ WORKBOOK

Create a Visual Basic Scripting (VBS) text file that runs at PC startup, or desired intervals using Windows Task Scheduler

The final step is to create a Visual Basic Scripting (VBS) file that silently (without visual windows or prompts) opens the ‘create-sharepoint-list-snapshot.xlsm’ workbook, refreshes the dataset from SharePoint, and saves a new, different workbook named ‘YYYYMMDD-sharepoint-list-all-fields-and-values.xlsx’. 

Create a new text file using NOTEPAD.EXE and paste the code below. The only three lines requiring editing are lines #1, #2, and #3, to update the FOLDER_PATH, SHOW_BEGIN_POPUP, and SHOW_END_POPUP constants. The FOLDER_PATH value should be a local drive folder that contains only the code-embedded Excel file created in steps 2 and 3. The SHOW_BEGIN_POPUP and SHOW_END_POPUP can be set to TRUE or FALSE depending on whether it is desired to show a popup dialog box when the code execution starts and/or ends.  Save the file as ‘create-sharepoint-list-snapshot.vbs’.

Personally, I choose to run this file once on PC startup by placing a copy of the .vbs script in the Windows 10 startup folder (%appdata%\Roaming\Microsoft\Windows\Start Menu\Programs\Startup). Optionally, the Windows Task Scheduler (%windir%\system32\taskschd.msc /s) can be used for greater nuance, like applying conditions such as whether the task should run when the PC is running on AC power, battery, or both, and advanced scheduling like daily, weekly, or monthly.

# # # BEGIN CODE TO PASTE INTO 'create-sharepoint-list-snapshot.vbs' TEXT FILE
Const FOLDER_PATH = "C:\Users\Matt\OneDrive\sharepoint\"
Const SHOW_BEGIN_POPUP = False                  
Const SHOW_END_POPUP = False         
' ------------------------------------------------------------------------------------
'    NAME
'    create-sharepoint-list-snapshot.vbs
'    This script opens, refreshes, and saves all Microsoft Excel files with
'    extension .XLS, .XLSX, or .XLSM within a local, network, or synchronized
'    SharePoint library. 
'    Place the script in your Windows "Startup" folder to run when the machine
'    boots, and/or use the "Task Scheduler" in the "Administrative Tools" section
'    of Microsoft Windows 7 or 10 to allow job to run at scheduled times.
'     1.0 | April 2019 - Matt Robb (
'        - Original version
'     1.0 |
'        - Original version
'        - none / no 3rd-party resources used
' ------------------------------------------------------------------------------------
' Alert the user when the script begins
         msgbox "The script will now begin execution. Press OK to continue."
End If
' Create the Microsoft Excel object, and silently (no screen indication) open the files
Set app = CreateObject("Excel.Application")
app.Visible = False
app.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
' Iterate through each Microsoft Excel file
For Each f In fso.GetFolder(FOLDER_PATH).Files
         On Error Resume Next
         If ((LCase(fso.GetExtensionName(f)) = "xls") or (LCase(fso.GetExtensionName(f)) = "xlsx") or (LCase(fso.GetExtensionName(f)) = "xlsm")) Then
                   Set wb = app.Workbooks.Open(f.Path)
                   app.DisplayAlerts = False
                   app.DisplayAlerts = True
                   wb.Close True
         End if
         On Error GoTo 0
' Alert the user when the script ends
         msgbox "The script has finished. Press OK to exit."
End If
Set app = Nothing
Set fso = Nothing
# # # END CODE TO PASTE INTO 'create-sharepoint-list-snapshot.vbs' TEXT FILE


To test that everything is working properly, double-click the ‘create-sharepoint-list-snapshot.vbs’ file in the PC or network folder, wait about 30-60 seconds depending on the number of records in the SharePoint List, and monitor the output folder for creation of the new ‘YYYYMMDD-sharepoint-list-all-fields-and-values.xlsx’ file. If all works as expected, the new file will appear. One can create pivot tables, charts, sort the data, and format for printing as with any other Excel document.

I hope you find this tutorial helpful to accommodate your unique needs.