How to Generate a Page for Each Day of Month in Microsoft Word using VBA
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 & " " & 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
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 & " " & 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
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
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!
How to Automate Sending Emails through Outlook interop using C#
I was tasked with a tricky issue in sending emails. Due to security concerns, the client’s IT team was not willing to share SMTP information for their mail settings and was only willing to set up an account in Outlook directly on a dedicated machine without sharing the password with us to send the emails. The client’s ask was to send emails through Outlook without letting users see the emails or Outlook itself.
Installing Office Interop for Outlook
Sending emails through Outlook can be done using Microsoft.Office.Interop.Outlook but the documentation is really lacking. If you need to do the same, I hope this will save you the hours of time it took me to figure out what ends up not being complex code.
Create a new desktop application project in Visual Studio. Install the Microsoft Office Interop for Outlook. I used the NuGet package manager to install it since it wasn’t present on my system:
Install-Package Microsoft.Office.Interop.Outlook
Automating E-mails using C#
I created a static class to send the email through Outlook. Note that my error handling code was replaced with Debug.Writeline. Remember to modify it to handle errors or implement logging so it doesn’t fail silently.
Email.cs:
using System;
using System.Diagnostics;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Outlook;
using Exception = System.Exception;
namespace Email.classes
{
public class Email
{
public static void SendWithEmbeddedImages(string to, string subject, string htmlMessage)
{
var missing = Type.Missing;
Application oOutlook = null;
NameSpace oNS = null;
Folder oCtFolder = null;
Items oCts = null;
MailItem msg = null;
var sHeaderPath = Path.Combine(Environment.CurrentDirectory, "emails", "header.jpg");
var sLogoPath = Path.Combine(Environment.CurrentDirectory, "emails", "logo.jpg");
try
{
// Create an Outlook application.
oOutlook = new Application();
// Get the namespace.
oNS = oOutlook.GetNamespace("MAPI");
//Assumes MAPI profile name is Outlook
oNS.Logon("Outlook", missing, false, true);
msg = (MailItem) oOutlook.CreateItem(OlItemType.olMailItem);
var attachHeader = msg.Attachments.Add(sHeaderPath, OlAttachmentType.olEmbeddeditem);
var attachLogo = msg.Attachments.Add(sLogoPath, OlAttachmentType.olEmbeddeditem);
attachLogo.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001E", "logo");
attachHeader.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001E",
"header");
msg.Subject = subject;
msg.To = to;
msg.BodyFormat = OlBodyFormat.olFormatHTML;
msg.HTMLBody = htmlMessage;
//Show email
msg.Display();
//Send email
//((Outlook._MailItem)msg).Send();
oNS.Logoff();
}
catch (Exception ex)
{
Debug.WriteLine("Automate Outlook throws the error: {0}", ex.Message);
}
finally
{
// Manually clean up the explicit unmanaged Outlook COM resources by
// calling Marshal.FinalReleaseComObject on all accessor objects.
// See http://support.microsoft.com/kb/317109.
if (msg != null)
{
Marshal.FinalReleaseComObject(msg);
msg = null;
}
if (oCts != null)
{
Marshal.FinalReleaseComObject(oCts);
oCts = null;
}
if (oCtFolder != null)
{
Marshal.FinalReleaseComObject(oCtFolder);
oCtFolder = null;
}
if (oNS != null)
{
Marshal.FinalReleaseComObject(oNS);
oNS = null;
}
if (oOutlook != null)
{
Marshal.FinalReleaseComObject(oOutlook);
oOutlook = null;
}
}
}
}
}
Example on how to call the class:
var sEmailPath = Path.Combine(Environment.CurrentDirectory, "emails", "single.html");
var htmlMessage = "";
if (File.Exists(sEmailPath))
{
//Load HTML from file
htmlMessage = File.ReadAllText(sEmailPath);
}
Email.SendWithEmbeddedImages("toaddress@test.com", "Outlook Automation Test", htmlMessage);
email.html:
<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
</head>
<body>
<table width="600" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><img src="cid:header"></td>
</tr>
<tr>
<td>
Hello world!
</td>
</tr>
<tr>
<td><img src="cid:logo"></td>
</tr>
</table>
</body>
</html>
Important Notes and Gotchas
- CIDs need to be unique. I’ve seen them on all other posts with the format “file.extension@{random #}” but keeping it simple like in the code above worked for me with no issues with Outlook 2016. I did not test on older versions of Outlook to confirm as I no longer have access to them.
- Outlook ignores font rules in the HTML/CSS you code in the email and defaults to Times New Roman. I know Outlook uses the Word renderer but I have no idea why and the only solution I found was to update the default font in Microsoft Word. Yes, to change the font in Outlook, you’ll need to update the default font in Word. Here’s how to set it:
- Open Word
Go to Options -> Advanced -> Web Options
Change the default font in the Fonts tab
- Open Word
- Outlook only supports a subset of HTML so don’t forget to test and verify everything as most CSS formatting won’t work in Outlook.
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
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 & " - " & visShape.Master.Name
End If
Next
End Sub
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<=$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)
}
Usage example:
latencyavg 20 https://www.godaddy.com
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.
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
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
If you have sub-directories and need to unblock everything, use the -Recurse flag:
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
List of my must-have Alfred Workflows
Use a mac? You’ll want to grab Alfred App. I’m finding it an invaluable replacement for spotlight and the workflows allow me to supercharge my workflows. It’s so useful that I’ve purchased the Powerpack lifetime license.
In addition to the standard features, here are a list of the most useful workflows for dev/tech:
Kill process – by Nathan Greenstein (@ngreenstein)
I use it as an activity monitor for CPU usage, and from there I can easily force quit any process if needed. It’s easier to see all processes on the Alfred UI instead of opening Activity Monitor on your mac. There’s also the workflow Kill Application – by Sebastian Hallum Clarke (and also on his site you can find other cool workflows).
Timer – by Daniel Bader
I use this one a lot. It’s super simple and by writing “Timer” and the number of minutes, you can easily set a reminder. It’s great for anyone using the Pomodoro technique or even if you leave something on the stove and want to go back to work.
Copy SSH Public Key – By oldcai
This one saves me time when I need to deploy my SSH key on a new server. Type ‘pk [ssh key file name]’ and it’ll copy the ssh key to the clipboard.
Incognito – by Nedwood
I find myself using this when I need to test a page and bypass the cache. Type ‘incog [url]’ and it’ll launch a new chrome window in incognito mode.
Find Folder by Samvlu
Finds a folder by name. I find this is faster than spotlight in just about every search.
Smart Folders by Deanishe
List all the Smart Folders/Saved Searches (same thing) on your system and drill down into their contents. Works in much the same way as Alfred’s File Filter, but Smart Folders are also available outside Alfred and are a bit more flexible.
For example, you can configure a Smart Folder to show all video/audio/image files without having to specify each different filetype individually. If you already use Smart Folders, this workflow can save you the work of re-implementing them as File Filters.
What’s more, you can exclude specific filetypes with a Smart Folder, which Alfred cannot do.
Advanced Google Maps Search by stuartcryan
This workflow gives you some quick and dirty shortcuts into Google Maps:
Commands:
To Configure:
mapsethome <home address including street number, name, postcode> (i.e. what you would type into Google Maps)
mapsetwork <work address including street number, name, postcode> (i.e. what you would type into Google Maps)
Commands for Use:
maps <query> – Search Google maps for an address
dir <query> to <query> to <query> etc (seperate multiple addresses with ” to ” minus the quotes, and you will get a multiple location search)
dirfw Show directions from Work to address
dirfh Show directions from Home to address
dirtw <query> Show directions from query to Work address
dirth <query> Show directions from query to Home address
trafficw – Show traffic from Home to Work
traffich – Show traffic from Work to Home
StackOverflow Search by deanishe
If you use stackoverflow as much as I do, this is a must-have.
Date Calculator
I find myself needing to calculate differences between dates in my personal life a lot lately. This workflow saves me a lot of time to do that. Want to know how far Christmas is away in days? ‘dcalc 12-25-16 – now d’ returns the number of days (assuming you’re using the US short format like I am).
Wifi Control by miroman
All my Macbooks periodically have issues with wifi. I’ve never been able to figure out what’s causing it but I use Wifi Control to restart the wifi which allows me to connect successfully.
Bugnot by vitor
If you use bugmenot at all, this is a useful extension to get logins without loading a new tab. Type ‘bn domain.com’ and you’ll get a list of matching passwords to use.
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.