Category Archives: business & IT operations

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.

Goals and objectives can be strengthened with cross-functional peer reviews

Climbers by Jaroslav Kuba is licensed under Creative Commons | Attribution 2.0 Generic

The yearly goals and objectives (G&O) process in most organizations is often perceived by staff as a mundane HR activity required solely to support year-end performance appraisals. Here’s my thoughts as to how directors and managers can strengthen the process with cross-functional peer reviews.

In my experience, the goals and objectives process is typically structured and communicated as a vertically-siloed exercise held between a supervisor and employee. Staff are asked to create tactical objectives to help achieve broader goals that cascade down from executives within a single department like I.T. or Finance.

Continue reading Goals and objectives can be strengthened with cross-functional peer reviews

Bi-weekly software updates hurt end-user productivity

SML Books by See-ming Lee is licensed under Creative Commons | Attribution-ShareAlike 2.0 Generic

Like most, I spent the recent year-end holidays reconnecting with family and friends over a tin of homemade Toll House chocolate chip cookies and non-fat cappuccinos. We laughed. We cried. We debated the merits of Agile SaaS deployments and Minimum Viable Product (MVP) delivery.

It began as an innocuous statement:

“Drive-thru windows at coffee shops. Call-ahead seating at chain restaurants. Online order pickup at big-box stores. Clearly we’re an impatient society. We know what we want. And even if we don’t, we want it now, anyway.”

Continue reading Bi-weekly software updates hurt end-user productivity

Change Management Training: Selecting the right employees to attend

Change by Conal Gallagher is licensed under Creative Commons | Attribution 2.0 Generic

Not every employee is the ideal candidate to attend formal change management training. This article presents a few considerations organized by role.

I’ll always remember my first experience being on the receiving end of an enterprise software replacement project that occurred without a formal change management strategy. I had just returned from a week’s vacation and discovered the introduction, rollout, and three (and only three) software training sessions for the new system all occurred the week I was away. The system adoption results were abysmal.

Continue reading Change Management Training: Selecting the right employees to attend

Project labor demand prioritization in the PMO

Screen capture of resource management utilization report.

Executive leadership just gave the go ahead for the PMO to  fire-up another project but it feels like staff is already over-capacity to take on more work.  Here’s how I built a lightweight, easy-to-sustain project labor demand solution using SharePoint and Excel to measure and prioritize project labor demand.

Does this sound familiar?

Let me take a shot at reading your mind:

Your executive leadership team just gave the go ahead to fire-up another project but it feels like your staff is already over-capacity to take on more work. That’s right—feels—a squishy, subjective term. The reality is you don’t currently have a compelling way to show leadership your project labor demand. You’d prefer to wait two months (or more) before taking on more projects but that pushback won’t fly without hard facts.

Continue reading Project labor demand prioritization in the PMO