I briefly joined my wife at her practice to help her grow the business and figure out how to make things more efficient. One of the things I learned is that my wife created a sign-in sheet for the office in Microsoft Word. Every week she would open the file and manually enter the date for each day of the week and then print out the documents. I took over the responsibility for a month and it annoyed me due to how inefficient the process was and I decided to automate the entire thing. I couldn’t find a solution to the problem online so I had to roll my own and am sharing the code in case someone else can benefit from it.

Important Details

The script will calculate the first day and last day of the month and then do a loop to append the date in the “Day, Month day, Year” format (i.e. Thursday July 17, 2019) to a text field.

There are a few important steps involved to get the script working as is:

Create a Word doc with the first page that you want to duplicate.
Add a text field from the developer tab. To copy and paste the code below as-is, you’ll need to name it txtDate. This is where the date will be added. If you want a different field name, change the name at line 26 and 83. You can also change the date formats to suit your needs here as well.
Add a second blank page to the document. I was running into issues where the paste was appearing partially on the first. The blank page resolved this and I added code to remove the original page as well as the blank one from the beginning.

How to Use

Open up Word, then open up VBA, and copy and paste this snippet into a module. When you run the function, it’ll create a copy for every day of the month. I also created a function to start at a specific date in case you run it in the middle of the month.

Sub CreateSigninsForMonth()
    Dim N As Integer
    Dim sCurrentMonth, sCurrentYear As String
    Dim sNewDate As String
    
    N = 1

    Count = Day(GetLastDayOfMonth)
           
    For CopyNumber = 1 To Count
    
        With Selection
            .GoTo wdGoToPage, wdGoToAbsolute, 1
            .Bookmarks("\Page").Range.Copy
            .Paste
        End With
    
        With ActiveSheet
       
         sCurrentMonth = Format(Date, "mmmm")
         sCurrentYear = Format(Date, "yyyy")
         sNewDate = (CopyNumber &amp; " " &amp; sCurrentMonth &amp; " " &amp; sCurrentYear)
        
        ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "DDDD MMMM dd, YYYY")
          
        End With
        N = N + 1
    Next CopyNumber
    
    'Delete template + blank page
    For i = 1 To 2
    With ActiveDocument
        strt = .GoTo(wdGoToPage, wdGoToLast).Start
        Set r = .Range(strt - 1, .Range.End)
        r.Delete
    End With
    Next

End Sub

Sub CreateSigninsForMonthStartingDate()
    Dim Count As Integer
    Dim N As Integer
    Dim sCurrentMonth, sCurrentYear As String
    Dim sNewDate, sEndDay As String
    
    N = 1
    Count = 0

    iStartDay = InputBox("Which day do you want to start on?", "Starting Day", "1")
    Count = InputBox("Which day do you want to end on?", "Ending Day", Day(GetLastDayOfMonth))
    
    Do While Count > Day(GetLastDayOfMonth)
        sEndDay = InputBox("Which day do you want to end on?", "Ending Day", Day(GetLastDayOfMonth))
                    
        If iStartDay = vbNullString Or sEndDay = vbNullString Then
            MsgBox "You clicked cancel.", vbOKOnly, "Try again later!"
            Exit Sub
        End If
           
        If IsNumeric(CInt(sEndDay)) Then
            Count = CInt(sEndDay)
        End If
    Loop
    
    For CopyNumber = iStartDay To Count
    
        With Selection
            .GoTo wdGoToPage, wdGoToAbsolute, 1
            .Bookmarks("\Page").Range.Copy
            .Paste
        End With
    
        With ActiveSheet
       
         sCurrentMonth = Format(Date, "mmmm")
         sCurrentYear = Format(Date, "yyyy")
         sNewDate = (CopyNumber &amp; " " &amp; sCurrentMonth &amp; " " &amp; sCurrentYear)
        
        ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "DDDD MMMM dd, YYYY")
          
        End With
        N = N + 1
    Next CopyNumber
    
    'Delete template + blank page
    For i = 1 To 2
    With ActiveDocument
        strt = .GoTo(wdGoToPage, wdGoToLast).Start
        Set r = .Range(strt - 1, .Range.End)
        r.Delete
    End With
    Next

End Sub

Function GetFirstDayOfMonth(Optional dtmDate As Date = 0) As Date
    
    ' Return the first day in the specified month.
    If dtmDate = 0 Then
        ' Use the current date if none was specified
        dtmDate = Date
    End If
    
    GetFirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Function GetLastDayOfMonth(Optional dtmDate As Date = 0) As Date
    
    ' Return the last day in the specified month.
    If dtmDate = 0 Then
        
         ' Use the current date if none was specified
        dtmDate = Date
    
    End If
    
    GetLastDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

Sub CreateSigninsForMonth()

Dim N As Integer

Dim sCurrentMonth, sCurrentYear As String

Dim sNewDate As String

N = 1

Count = Day(GetLastDayOfMonth)

For CopyNumber = 1 To Count

With Selection

.GoTo wdGoToPage, wdGoToAbsolute, 1

.Bookmarks("\Page").Range.Copy

.Paste

End With

With ActiveSheet

sCurrentMonth = Format(Date, "mmmm")

sCurrentYear = Format(Date, "yyyy")

sNewDate = (CopyNumber & " " & sCurrentMonth & " " & sCurrentYear)

ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "DDDD MMMM dd, YYYY")

End With

N = N + 1

Next CopyNumber

'Delete template + blank page

For i = 1 To 2

With ActiveDocument

strt = .GoTo(wdGoToPage, wdGoToLast).Start

Set r = .Range(strt - 1, .Range.End)

r.Delete

End With

End Sub

Sub CreateSigninsForMonthStartingDate()

Dim Count As Integer

Dim N As Integer

Dim sCurrentMonth, sCurrentYear As String

Dim sNewDate, sEndDay As String

N = 1

Count = 0

iStartDay = InputBox("Which day do you want to start on?", "Starting Day", "1")

Count = InputBox("Which day do you want to end on?", "Ending Day", Day(GetLastDayOfMonth))

Do While Count > Day(GetLastDayOfMonth)

sEndDay = InputBox("Which day do you want to end on?", "Ending Day", Day(GetLastDayOfMonth))

If iStartDay = vbNullString Or sEndDay = vbNullString Then

MsgBox "You clicked cancel.", vbOKOnly, "Try again later!"

Exit Sub

End If

If IsNumeric(CInt(sEndDay)) Then

Count = CInt(sEndDay)

End If

Loop

For CopyNumber = iStartDay To Count

With Selection

.GoTo wdGoToPage, wdGoToAbsolute, 1

.Bookmarks("\Page").Range.Copy

.Paste

End With

With ActiveSheet

sCurrentMonth = Format(Date, "mmmm")

sCurrentYear = Format(Date, "yyyy")

sNewDate = (CopyNumber & " " & sCurrentMonth & " " & sCurrentYear)

ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "DDDD MMMM dd, YYYY")

End With

N = N + 1

Next CopyNumber

'Delete template + blank page

For i = 1 To 2

With ActiveDocument

strt = .GoTo(wdGoToPage, wdGoToLast).Start

Set r = .Range(strt - 1, .Range.End)

r.Delete

End With

End Sub

Function GetFirstDayOfMonth(Optional dtmDate As Date = 0) As Date

' Return the first day in the specified month.

If dtmDate = 0 Then

' Use the current date if none was specified

dtmDate = Date

End If

GetFirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Function GetLastDayOfMonth(Optional dtmDate As Date = 0) As Date

' Return the last day in the specified month.

If dtmDate = 0 Then

' Use the current date if none was specified

dtmDate = Date

End If

GetLastDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)

End Function

6th August 2019

Development

How to Get Laravel Debugging to work with PHPStorm and MAMP Pro 5

This has been one of the more aggravating things I’ve had to deal with in setting up software for development. I’ve followed the official documentation from JetBrains, over 30 other blog tutorials, and literally failed in getting any of them to work.

I figured out an easy way to make the setup work so I’m sharing it in case someone else finds it useful and for self-reference since I’ll probably forget how to do this again in 6 months when I start a new project.

MAMP Configuration

Load MAMP and setup your host. Make note of the host name as you will need it to configure PHPStorm.
Go to PHP on the left under Languages.
On the right under Extensions, check Xdebug (Debugger).

PHPStorm Configuration

Load PHPStorm and load your Laravel project.
Setup your PHP executable and interpreter as per the official documentation and then resume here.
On the top right of PHPStorm, select Edit Configurations from the dropdown.
Click on the Plus Button on the top left of the dialog and then select ‘PHP Web Page’.
Enter a descriptive name in the textbox. I use the host name from MAMP so it’s easy to identify visually. Click on the 3 dots with next to Server.
Enter a descriptive name. I use the host name here as well. For the host, omit the http/https and just add the host name from MAMP.
Click OK
Now add your breakpoints and click on the Debugger Button on the top right and PHPStorm will load the site into the browser and break when breakpoints are hit.

Happy debugging!

21st May 2019

Administration

Self-signed SSL certificates not working with MAMP and Chrome

I use MAMP Pro for most of my PHP development and Chrome has annoyingly been blocking the self-signed SSL certificates MAMP generates, saying the certificate is not valid and “Your connection is not private”:

Thankfully, I found an easy solve to fix this. These steps assume you’ve created a host in MAMP. You’ll need to go to the SSL tab of the host you are trying to fix and

Check the SSL button
Click the self-signed certificate button.

Fill in the fields in the dialog modal and click Generate
You’ll be prompted to save the certificate. Choose whatever location you’d like.

Click on the circle with the arrow to open the directory where the certificate was saved.

In the finder window that opens, double click the .crt file (be sure it’s the same file name that’s in the SSL window in case you have multiple).

In the Add Certificate window, click Add. I testing adding it to login and system and both worked, so add whichever you prefer.

In the Certificates Window, double click the certificate. It’s easier to filter by name if you have a lot of items.
In the next modal window, choose Always Trust from the dropdown.

Close out the next confirmation window, and you’ll be prompted to authenticate with your mac login to save the updates.

Go back to Chrome, and reload the page and the certificate should work now:

14th May 2019

Development

How to get shape type in Visio using VBA?

I’m working with a Visio 2016 file with over 100 tabs and need to extract the data (mainly text, connector from/to, and shape) for data processing for a processing engine. I was trying to figure out how to get the shape type name in Visio using VBA. For example, in a flowchart, I’m trying to figure out how to tell if a shape is a process, decision, data, etc. The

visShape.Type

1	visShape.Type

property seems to always return 3 which appears to be visTypeShape from https://docs.microsoft.com/en-us/office/vba/api/visio.visshapetypes. After hunting through all the available properties on the Shape object, I found that the shape.Master.Name property will return the shape name, but you need to check if it’s Nothing first in case it’s not a shape.

I didn’t do that and it kept breaking the script originally because some of the pages had text fields and the first few items on the first sheet I was working with were text boxes. Hopefully this snippet will save you the time I wasted figuring it out.

Public Sub GetShapeAndID()

Dim visShape As Shape

    For Each visShape In ActivePage.Shapes
        If Not visShape.Master Is Nothing Then
         Debug.Print visShape.ID &amp; " - " &amp; visShape.Master.Name
        End If
    Next

End Sub

Public Sub GetShapeAndID()

Dim visShape As Shape

For Each visShape In ActivePage.Shapes

If Not visShape.Master Is Nothing Then

Debug.Print visShape.ID & " - " & visShape.Master.Name

End If

End Sub

10th May 2019

Development

How to get website average latency in BASH

I was working on a project today and wanted to be able to get the average latency for an API that I was working on. Performance is a concern because we’re running the API over a VPN, and then SSH tunneling over to another server. I wanted a quick way to do it and wrote a little bash function that will calculate the average for me. I couldn’t find an example on how to do this online so I’m sharing in case anyone else runs into the same issue.

This is tested on Mac only. Add these two functions to your .bashrc and do a shellupdate in terminal to load the latest, or just grab my dotfiles from my github: https://github.com/gregvarghese/dotfiles

curlb(){
  curl -s -o /dev/null -w '%{time_starttransfer}\n' "$@"
}

# Usage:
# latencyavg [# of times to run] [URL]
function latencyavg()
{
  time=0.00
  for (( c=1; c&lt;=$1; c++ ))
  do
     num1=$(curlb $2 -H 'Accept-Encoding: gzip, deflate, sdch' -H 'Accept-Language: en-US,en;q=0.8,ja;q=0.6' -H 'Upgrade-Insecure-Requests: 1' -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.86 Safari/537.36' -H 'Connection: keep-alive' --compressed)
     echo "$c - $num1"
     time=$(echo "$time + $num1" | bc -l)
  done
  echo "Total Time $time"
  echo $(echo $time / $1 | bc -l)
}

curlb(){

curl -s -o /dev/null -w '%{time_starttransfer}\n' "$@"

}

# Usage:

# latencyavg [# of times to run] [URL]

function latencyavg()

{

time=0.00

for (( c=1; c<=$1; c++ ))

num1=$(curlb $2 -H 'Accept-Encoding: gzip, deflate, sdch' -H 'Accept-Language: en-US,en;q=0.8,ja;q=0.6' -H 'Upgrade-Insecure-Requests: 1' -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.86 Safari/537.36' -H 'Connection: keep-alive' --compressed)

echo "$c - $num1"

time=$(echo "$time + $num1" | bc -l)

done

echo "Total Time $time"

echo $(echo $time / $1 | bc -l)

}

Usage example:

latencyavg 20 https://www.godaddy.com

1	latencyavg 20 https://www.godaddy.com

10th April 2018

Administration

How to fix VMWare Fusion Pro 10 “Cannot find a valid peer process to connect to”

I recently got a new mac and was migrating a VMWare Windows Virtual Machine from my other computer. When starting the virtual machine, I received a random message from VMWare Fusion saying “Cannot find a valid peer process to connect to.”

The fix was rather simple as it was just Apple blocking VMWare from running. To fix it, follow these steps:

Open System Preferences by searching in Spotlight or clicking the gear icon in the dock.
On the top row there is an icon for Security and Privacy – Click that icon
Near the bottom of the screen that appears the following statement
“System software from developer “VMWare, Inc.” was blocked from loading”
Click Allow

2nd April 2018

Administration

Windows 7 & 10 How to Bulk Unblock Blocked Files

While setting up a new computer, Windows was throwing up warnings that files transferred from a backup drive might be unsafe. The files were text and images, so the warnings were safe to ignore but the directory had over one thousand files. Rather than unblocking each file manually, Windows PowerShell makes it easy to unblock files in bulk.

Load up Windows Powershell:

Press Win + R on the keyboard to open the Run dialog.
In the Run box, type powershell

For one folder without subdirectories, this snippet will do the trick.“$env:userprofile\Downloads“ tells PowerShell to use the Download folder for the logged in user and unblocks all files in it. Change this to the folder path you need if it’s not the Downloads folder.

get-childitem “$env:userprofile\Downloads“ | unblock-file

1	get-childitem “$env:userprofile\Downloads“ \| unblock-file

If you have sub-directories and need to unblock everything, use the -Recurse flag:

dir “$env:userprofile\Downloads“ -Recurse | Unblock-File

1	dir “$env:userprofile\Downloads“ -Recurse \| Unblock-File

Want to see a report of files to be unblocked before running it? The -WhatIf flag will show you without executing.

dir “$env:userprofile\Downloads“ -Recurse | Unblock-File -WhatIf

1	dir “$env:userprofile\Downloads“ -Recurse \| Unblock-File -WhatIf

2nd November 2017

Development

How to automatically install gulp dependencies

I created a website where I’m using gulp to create a minified version and had to switch to another computer. For whatever reason, gulp install wouldn’t install the dependencies automatically, even when using the “npm-install-missing” plugin.

Add this script to the beginning of your gulpfile.js before the requirements, and when you run gulp, it will install the dependencies automatically. Be aware you’ll need to rerun your gulp command after the script completes.

(function(){
    var r=require;
    require=function (n){
        try{
            return r(n)
        }
        catch(e){
            r('child_process').exec('npm i ' + n,function (err,body){
                try{
                    console.log('Module "' +n + '"" not found, installing.\n' + body )
                    return r(n);
                }
                catch(e){
                }
            })
        }
    }
    console.log('""Please re-run gulp.\n' + body ) 
})()

(function(){

var r=require;

require=function (n){

try{

return r(n)

}

catch(e){

r('child_process').exec('npm i ' + n,function (err,body){

try{

console.log('Module "' +n + '"" not found, installing.\n' + body )

return r(n);

}

catch(e){

}

})

}

console.log('""Please re-run gulp.\n' + body )

})()

How to Fix ‘Converter Failed to Save File’ with Excel 2016

2nd November 2016

Administration

How to Fix ‘Converter Failed to Save File’ with Excel 2016

I recently upgraded to Office 2016 on my Windows 10 desktop and was getting the error “Converter failed to save file” when double clicking on the file along with an “There was a problem sending the command to the program error” every time. I finally had enough with the annoyance to troubleshoot it and figured out a solve.

If you have the same issue, here’s how to fix it:

Open your Default Programs configuration from the Control Panel. On Windows 10, you can hit start, type Default Programs, and it’ll open the app.
Scroll down the list until you get to the Excel formats (XLS):
If you see anything other than Excel as the default, you’ll need to change the default to Excel. For me, the issue was the Open XML Converter not being installed anymore after upgrading to 2016. To change the default, select the format, click the “Change Program” button and select Excel 2016 from the list of apps that pops up and click OK to set the association:
You’ll need to do this for each format in the list to correct it. The most common formats you’ll use are XLS, XLSX, & XLT.

28th September 2016

Administration

ReportCrash High CPU & How to Disable reportcrash in Mac OSX

For a while now, all of my MacBooks have run extremely hot and the fans have gone nuts. While troubleshooting the issue, Activity Monitor showed that an app named reportcrash has run very high on the CPU and has killed my battery life. Force quitting the app didn’t help as it would start right back up in a few seconds and climb back to 80-100% usage of the CPU.

What is CrashReporter?

CrashReporter runs in any time an application crashes and it’s designed to saves the application state to aid developers in working out why the app crashed. Basically a process is launching, crashing (and invoking CrashReporter) and then re-launching, repeating this cycle never ending.

How to Identify What’s Crashing

To show which process is triggering this cycle and stop it, CrashReporter is pretty verbose in its logging which makes finding the problem app somewhat easier. Open up the console.app (/Applications/Utilities/Console.app) and look towards the end of your system.log to see what app is crashing.

Unfortunately for me, the problem is a driver by some company called EFI and getting the latest drivers didn’t resolve the issue. The next obvious solution was to disable reportcrash.

How to Disable ReportCrash

Fire up terminal and run the following commands to disable reportcrash:

launchctl unload -w /System/Library/LaunchAgents/com.apple.ReportCrash.plist
sudo launchctl unload -w /System/Library/LaunchDaemons/com.apple.ReportCrash.Root.plist

1 2	launchctl unload -w /System/Library/LaunchAgents/com.apple.ReportCrash.plist sudo launchctl unload -w /System/Library/LaunchDaemons/com.apple.ReportCrash.Root.plist

How to Enable ReportCrash

If you need to reenable crash report, run the following commands in terminal:

launchctl load -w /System/Library/LaunchAgents/com.apple.ReportCrash.plist
sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.ReportCrash.Root.plist

1 2	launchctl load -w /System/Library/LaunchAgents/com.apple.ReportCrash.plist sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.ReportCrash.Root.plist

Troubleshooting

How to Generate a Page for Each Day of Month in Microsoft Word using VBA

Important Details

How to Use

How to Get Laravel Debugging to work with PHPStorm and MAMP Pro 5

MAMP Configuration

PHPStorm Configuration

Self-signed SSL certificates not working with MAMP and Chrome

How to get shape type in Visio using VBA?

How to get website average latency in BASH

How to fix VMWare Fusion Pro 10 “Cannot find a valid peer process to connect to”

Windows 7 & 10 How to Bulk Unblock Blocked Files

How to automatically install gulp dependencies

How to Fix ‘Converter Failed to Save File’ with Excel 2016

ReportCrash High CPU & How to Disable reportcrash in Mac OSX

What is CrashReporter?

How to Identify What’s Crashing

How to Disable ReportCrash

How to Enable ReportCrash