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!

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

11th July 2018

Applications

How to remove wrapping tags in PHP Storm

How often do you code something and need to delete a wrapping link or div? I was using PHPStorm and had grabbed some code from another file that had links in the tags which weren’t needed in the new file. I didn’t want to manually remove each link and after a quick look around PHPStorm’s menus, discovered that PHPStorm has a really useful command to remove the wrapping element for you.

Select the element, then from the menu, choose Code > Unwrap/Remove… or use the keyboard shortcut, Command + Shift + Backspace and then choose the appropriate wrapping element to remove.

Gif of Unwrap in Action — Unwrapping in Action

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

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.

9th June 2016

Applications

Cheap Macbook Pro Docking Solution for about $30

I typically prefer developing on my desktop and large dual screen monitor setup as opposed to my Macbook. Recently, I’ve gotten into more PHP development and developing on the Mac is proving to be a more enjoyable experience only because Windows is still a second class citizen for most PHP libraries/tools. I still wanted a larger screen setup, so I considered buying a docking station to hook up to a larger monitor but I soon learned they feature the ‘Mac Penalty’ in that they cost more than they should just because it’s for Apple products.

I already have two large monitors and figured my Macbook could serve as third screen. Then I did some thinking and realized I could use one of the monitors for both computers. Assuming you have two HDMI screens already hooked up to your desktop, this would save you $150+ and still allow you to be more productive.

Get a copy of Synergy (http://symless.com/) for $10. It’s open source and can be built if you have the time, but a one time fee of $10 saves me the hassle of doing so. With this, you can share your desktop keyboard across all your computers with the one license and it’s cross platform so Linux support is included too.
Purchase an auto HDMI switcher. I purchased the PORTTA PET0301S 3×1 Port HDMI Switch/Switcher for about $9.
Be sure to get 2 HDMI cables if you don’t already have them on hand. I don’t like the Amazon Basic brand for these as I’ve had problems with the Mac and those cables hooking up to larger screens.
Install Synergy on the desktop as a server. Install on the Macbook as a client and it should autoconnect.
Plug the HDMI cable from the computer into the HDMI switcher, and plug the spare into the Macbook pro. Plug the “Out” end into the monitor.

When you plug your HDMI cable into the Macbook, the HDMI switcher will automatically switch to it and project the Macbook. Synergy will auto-connect as long as it’s running on both and you can share the mouse and keyboard between both and work seamlessly.

If you have an iPad and want to turn that into an additional screen, grab a copy of Duet Display on your desktop/Macbook and install on your iPad for $15.99, and viola, instant portable second screen!

29th May 2016

Administration

How to repair permissions on Linux Apache /var/www/html folder

I recently ran into an issue where I could no longer FTP files to my Linux droplet when multiple users were uploading to the server. The server kept the user as the owner despite me adding them to the www-data group. This fix comes from my ex-boss, James Tomasino who was kind enough to provide some help since I was stumped. I’m sharing in case anyone else runs into the same issue and finds it useful.

You’ll need to fire up terminal, ssh to the server, and then execute these commands:

cd /var/www
sudo chmod 775 html
sudo chgrp www-data html
sudo chmod g+s html

cd /var/www

sudo chmod 775 html

sudo chgrp www-data html

sudo chmod g+s html

+s makes permissions sticky so that all files will inherit from the parent directory. This was the setting I was missing.

Open up /etc/ssh/sshd_config. I use nano so:

sudo nano /etc/ssh/sshd_config

1	sudo nano /etc/ssh/sshd_config

Hit CTRL+W and look for “subsystem” which is typically located near the bottom of the file. Change

subsystem sftp /usr/lib/openssh/sftp-server

1	subsystem sftp /usr/lib/openssh/sftp-server

subsystem sftp /usr/lib/openssh/sftp-server -u 0002

1	subsystem sftp /usr/lib/openssh/sftp-server -u 0002

If you already have files in the HTML folder, you’ll want to run these commands to reset the permissions:

cd /var/www/
sudo chgrp -R www-data html
find . -type f -exec chmod 664 {} \;
find . -type d -exec chmod 775 {} \;

cd /var/www/

sudo chgrp -R www-data html

find . -type f -exec chmod 664 {} \;

find . -type d -exec chmod 775 {} \;

6th February 2016

Administration

Copy File Context to Clipboard via Command Line, Git Bash (Mingw64), or Terminal

I’ve been spending more time switching from SVN to Git and learning the command line options. I’m finding a lot of the Mac Git terminal commands I run aren’t the same in Git Bash (Mingw64). One of the commands I use is the pbcopy command to copy the ssh key to the clipboard to paste into Github and Bitbucket accounts. I’m starting to use it more as I am learning managing Linux servers and adding my key to login. I’m adding it here as a cheatsheet for myself but I thought others may find it useful.

On Windows, you can run this command to copy your ssh key to the clipboard:

clip < ~/.ssh/id_rsa.pub

1	clip < ~/.ssh/id_rsa.pub

On Mac:

pbcopy < ~/.ssh/id_rsa.pub

1	pbcopy < ~/.ssh/id_rsa.pub

or if you want to view it in the terminal window:

cat ~/.ssh/id_rsa.pub

1	cat ~/.ssh/id_rsa.pub

18th January 2016

Administration

How to Copy and Paste into the Digital Ocean VNC Console

I recently signed up for Digital Ocean to test droplets for my development and linux management testing. I was securing the server and setup fail2ban, disabled the root login, and created a user account but forgot to change the user name from root to the user name I selected in my SSH client profile. I ended up locking myself out of the server and had to resort to logging into the admin panel on Digital Ocean’s website and use their browser embedded VNC client to restore my access. I quickly discovered that one of the downsides of using this panel is that you can’t copy and paste commands. Luckily there’s a workaround and you can use the browser console to do sendkeys through Javascript.

Bring up the console in the browser developer tools. Here’s a cheat sheet for keyboard shortcuts:

Browser	Description	Windows	Mac
Chrome	Open Developer Tools and bring focus to the console	Ctrl + Shift + J	Cmd + Opt + J
Firefox	Open Console	Ctrl + Shift + K	Cmd + Opt + K
Internet Explorer	After hitting F12, you have to click the console tab. There’s no direct shortcut to the console tab.	F12	N/A

<s>!function(){function t(){window.rfb.sendKey(e.shift().charCodeAt()),e.length>0&amp;&amp;setTimeout(t,10)}var e=prompt("Enter text to be sent to console").split("");t()}();</s>

1	<s>!function(){function t(){window.rfb.sendKey(e.shift().charCodeAt()),e.length>0&&setTimeout(t,10)}var e=prompt("Enter text to be sent to console").split("");t()}();</s>

Update 9-21-16

Ruden and Sebastiaan’s pointed out there was a bug in the code above. This updated snippet has support for characters when using shift characters like !@#$%^&*()_+



!function(){function t(){function n(t,e){s=s.concat(RFB.messages.keyEvent(t,e))}var o=e.shift(),s=[],i=o.charCodeAt(),c=-1!=='!@#$%^&amp;*()_+{}:"&lt;>?~|'.indexOf(o),r=XK_Shift_L;c&amp;&amp;n(r,1),n(i,1),n(i,0),c&amp;&amp;n(r,0),rfb._sock.send(s),e.length>0&amp;&amp;setTimeout(t,10)}var e=prompt("Enter text to be sent to console").split("");t()}();

You’ll receive a dialog prompt to enter the copy to paste in. Please note that you need to click on the VNC console and hit enter to execute the command.

If you need to enter more than one command, just hit the up arrow on your keyboard in the console to get the script again for easy reuse.

Tips and Tricks

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

How to get website average latency in BASH

How to remove wrapping tags in PHP Storm

Windows 7 & 10 How to Bulk Unblock Blocked Files

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

Cheap Macbook Pro Docking Solution for about $30

How to repair permissions on Linux Apache /var/www/html folder

Copy File Context to Clipboard via Command Line, Git Bash (Mingw64), or Terminal

How to Copy and Paste into the Digital Ocean VNC Console

Update 9-21-16