Getting started in amateur radio and want to explore the VHF/UHF bands in your area? Here’s a tip for creating heat maps.
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.
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-fifteen. Maybe 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
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
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
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
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
“Good evening, Mr. Stock. Please step over here so we may have a look in that backpack, please,” bellowed the school’s vice principal, Mr. Stuckley. “Planning to do homework at the homecoming dance, are we?”
I turned my head to see who Mr. Stuckley was speaking to. So did the two volunteer mothers manning the ticket table, and I found amusement as both their facial expressions drew noticeably uncomfortable once seeing the target of Mr. Stuckley’s attention. It was Adam Stock, another sophomore in my class who was dressed-to-impress this evening wearing black jeans, a tuxedo-printed t-shirt, polished cowboy boots, and a top hat that would make Slash envious.Continue reading The Smuggler
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
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:
Continue reading Bi-weekly software updates hurt end-user productivity
“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.”