How to Delete a Row in Excel Using the Elgato Stream Deck
Recently I was working on a massive Excel Spreadsheet and needed to manually review each entry and clean up rows that were no longer needed. The Elgato Stream Deck came in handy for a quick shortcut so I thought I’d share it in case anyone else can use it.
I took this opportunity to practice creating the first of what I hope are many training videos. This took me around 30 minutes to do from start to finish as I had to learn the video editing software including how to record, how to split and edit, and how to add text overlays. Hopefully the next videos will be faster but it was a fun exercise and I hope someone else finds it useful.
How to Delete Folder with Special Character in Windows 10/11
I ran into an issue where a folder was created by some application with a special Unicode character that Windows Explorer doesn’t seem to play nicely with. I also was unable to tell what the character was since nothing would reveal it. The folder’s there, but you can’t rename or delete it. If I tried to remove or delete it, I’d get an error saying the folder doesn’t exist:
I have LockHunter installed but it wasn’t able to delete it for some reason. The easiest way I found to delete the folder was to use Git Bash and then use the appropriate commands to rename or delete the folder.
Browse to the folder where the offending folder is located. For example purposes, I’ll use c:\temp\folder1
cd c:/temp
Rename:
mv fol (hit tab to autocomplete) folder1
Delete:
del fol (hit tab to autocomplete)
If you don’t have Git Bash or are not a developer/power user, you can download the portable version from https://git-scm.com/download/win to use temporarily. Once you decompress the files to a folder, you’ll find git-bash.exe which you can double-click to run and use the above commands.
How to Clear Archive & Read-only flags on Files in Windows in Bulk
I ran into an issue where I had to move files from one system to another and was running into issues because files had been set as read-only, had the archive flag set, or both. It was causing the system to skip files which wasn’t acceptable. Normally you could just use Windows to clear it in bulk, but that could potentially mess up file permissions. I needed a way to automatically just clear all flags but respect permissions.
I did some searching and didn’t find a utility that would do the job and most of the solutions I found required Powershell which wasn’t available on the system I was on. I ended up writing a quick console application in C# to do the trick. I’ve made it free and open sourced it in case anyone wants to use it.
If you need just the app, you can find the release build here with instructions. The app also prompts for input to make things a bit easier to use. There’s no install, no tracking or metrics, or anything else related to privacy concerns in this app. It’s a simple throwaway utility to get the job done and move on.
https://github.com/gregvarghese/clearflags/releases/tag/1.0.0
If you want to see the source code, that is available here:
https://github.com/gregvarghese/clearflags/
Please note that I did this in about 10 minutes for my own use so error handling is pretty much non-existent. I mention this because I did run into one issue where Windows was somehow seeing a folder with files in it as a file and it couldn’t be deleted or renamed and the utility couldn’t get past it until it was resolved. I didn’t spend much time debugging and just used my Mac to rename the folder and Windows was able to recognize it after the change, so the utility was able to continue processing.
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 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 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 ‘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.
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!
How to fix “hacked by Moroccanwolf” WordPress site
A client of mine had their wordpress hacked and when you would load the site, it would simply display a message that said “hacked by Moroccanwolf”. I did some digging and luckily it wasn’t a major hack and they didn’t mess with the posts or other settings as a lot of the hacks do.
Quick Fix
- To fix it, you’ll need to connect to your database using an editor of some sort, such as PHPmyadmin.
- Once logged in, expand your database on the left.
- Click on ‘wp_options’.
- On the top right, click the ‘Browse’ tab.
- Look for ‘widget_text’ in the option_name field. (For my client, it was at row 90). You should see something similar to this:
<script>document.documentElement.innerHTML = unescape(''%48%61%63%6b%65%64%20%62%79%20%4d%6f%72%6f%63%63%61%6e%77%6f%6c%66%20%26%26%20%61%62%64%65%6c%6c%61%68%20%45%6c%6d%61%67%68%72%69%62%69'');</script>
Delete the entire tag and this  should restore your website. Now remember to change your logins and update wordpress.
How I Figured it Out
Most of the hacks I’ve seen are done through injecting javascript into the database somewhere which either force a redirect or something along those lines. Here’s the steps I followed to find it:
- To fix it, you’ll need to connect to your database using an editor of some sort, such as PHPmyadmin.
- Once logged in, click on your database on the left. Ensure you’re on the database and not a table.
- Click Export.
- Leave it to quick and click go.
- You should now see a textbox with a mess of SQL commands.
- Copy and paste into your favorite editor,
- Search for <script> and you should find something that doesn’t belong. Â In this instance, that was the only thing I found of note.
- You’ll want to scan the rest of the database for things that don’t belong. Additionally, you’ll want to replace all the wordpress files and confirm no .htaccess files were created that give hackers write access.