All posts by matt robb

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
         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 

Conclusion

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.

No reason to feel intimidated taking your first amateur radio license exam

My amateur radio examination form received upon successfully passing written Element 2

It was five minutes to noon and cold rain fell hard against a troop of girl scouts as they ran with poster-board projects from the door of the Hopewell branch public library to their parents’ idling cars.

I’ll wait five more minutes, I thought as I sat dry in my own car watching through the rear-view mirror.  The exam doesn’t begin until twelve-fifteenMaybe I’ll catch a break in the rain.

I spent two months preparing for my Technician exam and set my eyes to attend the closest license session, a twenty-minute drive from my home that was sponsored by the Delaware Valley Radio Association (DVRA).  By the time exam day arrived I had taken several online practice tests and passed most with a comfortable margin, missing only two or three questions on each attempt.  I’ve got this, I thought.

Continue reading No reason to feel intimidated taking your first amateur radio license exam

Three study resources for the Level 1 Technician Ham Radio License exam

Three resources I use to study for the Level 1 Technician Ham Radio license

Five days from now I will visit my local library and sit for the Level 1 Technician Ham Radio License exam.  I feel confident I will pass the exam largely from the help I received by three study resources below.

Continue reading Three study resources for the Level 1 Technician Ham Radio License exam

My first amateur radio transceiver: BaoFeng BF-F8HP

My first amateur radio tranceiver, the BaoFeng BF-F8HP. Alternatively, a photo of the world’s least impressive ham shack.

I discussed a few days ago how a box fan sparked my interest in amateur radio. If like me you’re just getting started with the hobby then you’ve already discovered the passionate discussions online about when to buy a radio and the qualities that make for a good first transceiver.

Continue reading My first amateur radio transceiver: BaoFeng BF-F8HP

How a box fan sparked my interest in amateur radio

I’m a Big Fan of Yours by Alan Levine is licensed under Creative Commons | Attribution 2.0 Generic (CC BY 2.0)

Two years ago I experienced an interesting phenomena. An inexpensive (dare I say, plastic) box fan randomly behaved like a low-volume, flat-sounding AM radio whenever positioned next to a window in my home.

Continue reading How a box fan sparked my interest in amateur radio

Coding text-based MMORPG games in PHP and MySQL

I’m out of the professional coding world for ten years, but it hasn’t stopped me from creating a BBS-style online text adventure game in the spirit of TradeWars 2002 and Legend of the Red Dragon using PHP and MySQL.

I remember well the specs of my first brand-new PC in the fall of 1993 that cost me $3,000: an Intel 486DX processor with 8MB RAM, 340MB hard-drive, and a USRobotics 14.4K modem. With that PC came the joy of exploring local dial-up BBS systems, namely the Trenton NJ area It’s All Rock ‘N Roll and the online text adventure games the SysOp offered his members.

Continue reading Coding text-based MMORPG games in PHP and MySQL

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