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:
- Create a personal SharePoint View that includes all List field columns
- Export the view to Excel, automatically creating the read-only connection
- Add custom Visual Basic for Application (VBS) code into the workbook
- 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 ActiveWorkbook.RefreshAll ' Select all data Cells.Select Selection.Copy ' Paste the values into a new workbook Workbooks.Add 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 ActiveWorkbook.Close Windows("create-sharepoint-list-snapshot.xlsm").Activate 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 ' ' PURPOSE ' 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. ' ' VERSION HISTORY ' 1.0 | April 2019 - Matt Robb (linkedin.com/in/mattrobb/) ' - Original version ' ' RELEASE NOTES ' 1.0 | ' - Original version ' ' DEVELOPMENT RESOURCES ' - none / no 3rd-party resources used ' ' ------------------------------------------------------------------------------------ ' Alert the user when the script begins If SHOW_BEGIN_POPUP = True Then 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 wb.Save app.DisplayAlerts = True wb.Close True End if On Error GoTo 0 Next ' Alert the user when the script ends If SHOW_END_POPUP = True Then msgbox "The script has finished. Press OK to exit." End If app.Quit 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.