Invalid and Inconvenient File Name Characters in DOS

File names that include a space require special handling. Specifically, the name or path needs to be surrounded by quotation marks when they’re referred to.

For that reason I have an aversion to using space characters in my file names. Whenever I download a file, I find myself replacing them with underscore characters. It’s easy to do in Altap Salamander¹. Open the Rename tool with Ctrl-Shift-R, put a space in the Search box, an underscore in the Replace box, press Alt-Y to uncheck “Only Once” and finally Alt-R to perform the action.

Since I’m learning Python, I thought it would be fun to write a script to do this. And while I’m at it, I should create the script so it also replaces any illegal characters in a string.

Thus I went down the rabbit hole of searching for a list of illegal characters.

According to Microsoft², there are these: < > : " / \ | ? *. But I realized that I didn’t want a list of illegal characters; I wanted a list of characters whose use is a bad idea – inconvenient characters. To me, they are ( ) + & , ^ ! %.

So I looked at a popular thread on Stackoverflow³, where, to my chagrin, a Python script similar to mine⁴ was waiting for me. I enjoyed reading the answers, the comments, the opinions. But not once did anyone mention an inconvenient character except for space. And so I revved up my “expertise engine” and looked for the post editor in which to write my response.

Alas, I did not have enough “reputation points” to answer the question. And anyway, I thought the whitelist idea was the best.

But in case you’re curious, here are explanation of why these characters are inconvenient:

Parenthesis: They confuse the DOS FOR command. Create my(file).txt. Then enter the following at the cmd prompt:⁵

for /F %s in (my(file).txt) do echo %s

I’m using Windows 10 and verifying these examples with it. On older Windows (7, XP?) parenthesis caused a problem even if the file name is placed after the do, like this:

for %i in (2 4 6 8) do copy my(file).txt my(file)%i.txt

Plus sign: The DOS copy command can be used to concatenate two or more files into one. What’s the concatenation operator? You guessed it – it’s the plus sign. How would you concatenate my+1.txt with my+2.txt? This doesn’t work:

copy my+1.txt+my+2.txt ex2.bat

You can do this instead, which is just as bad as using spaces:

copy "my+1.txt"+"my+2.txt" ex2.bat

Ampersand: Two statements can be placed on one line when they’re separated by an ampersand. If you wanted to copy one file to another and then print the contents to the screen you could enter:

copy ch2.txt ch42.txt & type ch42.txt

But if instead of ch2.txt you had ch2&3.txt, it gets tricky. For example, enter:

copy ch2&3.txt ch42.txt & type ch42.txt

It gives up rather quickly, saying that ch2 and ch42.txt could not be found and that 3.txt is not a command. That’s because DOS interprets it as three separate commands, not two:

copy ch2

&3.txt ch42.txt

type ch42.txt

Comma: My only concern about commas is that I work with CSV data files frequently. If a file name needed to be included in such a file, the comma could be interpreted as a delimiter and screw up the layout.

Percent and Exclamation mark: These are used in batch files to reference variables or command line parameters. Consider what special handling you’ll need for a file named my%100.txt:

set x=2

copy my%100.txt my%100%x%.txt

The %1 is replaced by the first command line parameter, which is not what you want. When this is run with no parameters, the batch file tries to copy my00.txt to my002.txt.

When delayed environment variable expansion is enabled, the second statement could be written as shown below, which can be inconvenient when the file name includes an exclamation mark:

copy my%100.txt my%100!x!.txt

If the file is named my!100.txt, you can make this example work by escaping the ! that’s part of the file name. Use ^ as the escape character, but you’ll have to double it:

copy my^^%100.txt my^^%100%x%.txt

Unfortunately, there is no way (that I know of in DOS) to escape the percent character in %1.

And that brings us to caret. If you use it in a file name such as my^100.txt, you’ll need to escape it, as well.

copy my^^100.txt my^^100%x%.txt

Command prompt help lists even more characters that require quotes if they’re used in filenames. You can find this at the end of the output from cmd /h:

The completion code deals correctly with file names that contain spaces or other special characters by placing quotes around the matching path…. The special characters that require quotes are:

<space>

&()[]{}^=;!'+,`~

Note that % isn’t among them.

I hope I’ve convinced you to avoid any characters other than alphanumeric, hyphen, underscore and dot. If not, try to avoid the command prompt.

---

¹ https://www.altap.cz/

² https://learn.microsoft.com/en-us/windows/win32/fileio/naming-a-file

³ https://stackoverflow.com/questions/1976007/what-characters-are-forbidden-in-windows-and-linux-directory-names

⁴ The one in my head that I hadn’t yet written

⁵ Some code samples can be found at my incovenient_characters repo

Excel VBA -- Named Range Automation

A few weeks ago, I was working with an Excel workbook that had 18 sheets that were similar in structure. Half the sheets had names L1_1, L1_2, … L1_9; the other half, R1_1, R1_2, … R1_9. They contained dimensions of structures on the left and right of a substrate.

I wanted to display a summary of a few averages from each sheet on a new sheet. I knew this would involve the tedious process of selecting a range of cells for each average, so I decided I would write VBA code to create a named range for each group of cells that I wanted averaged.

I turned on the Macro Recorder and then manually created the range so I could get some idea of what object and property to set. It recorded this:

    ActiveWorkbook.Names.Add "PGC_1”, "=L1_1!$D$3:$D$84"

This told me that the Add method of the Names collection would require the Name and the Address of the desired named range.  And so I wrote this subroutine:

Sub CreateNamedRanges()
Dim objN As Name
Dim i As Integer, s As String

For i = 1 To 9

s = CStr(i)

ActiveWorkbook.Names.Add "PGC_" & s, "=L1_" & s & "!$D$3:$D$84"

ActiveWorkbook.Names.Add "PGL_" & s, "=L1_" & s & "!$D$190:$D$376"

ActiveWorkbook.Names.Add "PTL_" & s, "=L1_" & s & "!$D$101:$D$173"

ActiveWorkbook.Names.Add "PGR_" & s, "=R1_" & s & "!$D$190:$D$376"

ActiveWorkbook.Names.Add "PTR_" & s, "=R1_" & s & "!$D$101:$D$173"

Next i

For Each objN In ActiveWorkbook.Names

Debug.Print objN.Name, objN.RefersTo

Next

End Sub

But the code crashed on the fourth Add method; it complained about an invalid address.

It didn’t make sense. I looked carefully at the fourth Add method. There were no illegal characters or trailing whitespace or other pitfalls. I found references to the error online, but none of the content pertained to what I was working on.

Eventually it dawned on me that perhaps Excel was confusing the “=R1_” & s as an R1C1 style address. Perhaps I needed to make sure it knew that “=R1_” & s was a sheet name. And so I placed two single quotes in each address: one right after the equal sign, and one right before the exclamation mark, like this: "=’R1_" & s & "’!$D$190:$D$376"

After this correction, the code ran quickly.

Get Path to Current LibreOffice Spreadsheet

There are two comments related to the previous post:

1. The blood draw wasn’t scheduled for the next morning. It was for the following morning! What a dummy I am!
2. The code that I use in LibreOffice Calc to return the Current Working Directory can be found below. I call it with this worksheet function as an argument: CELL("FILENAME",$A$1).  Please note that the module in which this resides has the following statement at the top: Option VBASupport 1

Function LG_GetPath(s As String) As String

' 2019-02-26 LG  Created, mostly to derive Current Working Directory
	Dim sTemp As String
	Dim i As Long, j As Long
	' MsgBox "Contents of s: " & s
	If IsNull(s) Then
    	LG_GetPath = "ERROR"
    Else
    	sTemp = CStr(s)
    	i = InStr(s, "///") + 3
    	j = InStrRev(s, "/")
    	j = j - i
    	If j > 0 Then
    		sTemp = Mid(s, i, j) & "/"
    		LG_GetPath = sTemp
    	Else
    		LG_GetPath = "ERROR"
    	EndIf
    EndIf	
End Function ' LG_GetPath

Add Command Prompt Here to Explorer Without Mucking About With the Registry

Windows 10 computers are missing the “Open Command Prompt Here” on the shortcut menu.

You can find a registry change online that will bring it back. But that “fix” involves changing the Permissions / Ownership of that part of the registry, so I declined to try it.

Instead I came up with a solution that uses the SendTo feature to call a batch file that calls CMD with the /K switch. To use it, create a batch file in your SendTo directory called OpenCMD.cmd. Add the content below, between the “BEGIN--------” and “END--------” tokens, and save it. Then Right-Click any file, choose SendTo OpenCMD.cmd, and a command prompt will be opened in the directory in which that file resides (if the path is on a local hard drive or mapped drive).

The SendTo directory can be found under your user profile; the <UN> in the path below is your user name:

C:\Users\<UN>\AppData\Roaming\Microsoft\Windows\SendTo

You can also enter the following in the explorer address bar to navigate to SendTo: shell:SendTo

Here’s the batch file source code. Please note that most of the lines are either comments (which start with REM) or debugging statements (which start with Echo). In fact the only lines that are necessary are the ones between the labels :OpenCMD and :END.  And please don't include the “BEGIN--------” and “END--------” tokens

Begin--------

REM OpenCMD.cmd (c) 2022 Luddite Geek

REM Opens the command prompt in the directory of the given path

REM

REM 2022-11-04 LG Created.

GOTO OpenCMD

GOTO BEGIN

cmd /?

:BEGIN

@echo off

Echo %0 %1

Echo DRIVE: %~d0

Echo PATH: %~p0

Echo NAME: %~n0

Echo EXT: %~x0

Echo.

Echo Drive and Path of parameter 1: %~dp1

pause

:OpenCMD

%~d1

cd %~dp1

CMD /D /K CLS

:END

End--------

Count Selected Items in Outlook

The modern Outlook status bar no longer shows the number of selected items. So some folks suggest that you select items and simply press Enter in order to trick Outlook into thinking you want to open all of them. And if you have more than four items selected, Outlook supposedly will pop up a warning that opening X number of items could take a long time.

But what happens if you accidentally agree to open them, or if the instance of Outlook doesn’t warn you, as in my case? Then you’ve wasted time opening emails and failed to get your answer.

No, it’s never a good idea to rely on an operation’s side effect; it could be eliminated in the next version!

What I do instead is surprisingly simple.  I just call the Count property of Selection.  This returns the number of selected items. I call the property with a message box that’s wrapped in a subroutine, which I link to a button on the Actions Menu.

This is all you need:

Sub CountItems()

MsgBox ActiveExplorer.Selection.Count & " items are selected", vbOKOnly, "CountItems() Message"

End Sub

It would be nice if there were an easy way to add a link to this on my shortcut menu.

View Internet Header of an Email Message in Modern Outlook Client

One should always examine the Internet Header of a suspicious email.  Yet, when Microsoft upgraded Outlook, this has become more difficult than just right-clicking and choosing View Header.  Instead, you'll first have to open the message (a cringe-worthy action), and then navigate to File and Properties as described here.

I can never remember the procedure.  Besides, I want to see the header before I open the message!

So I wrote a macro that displays the beginning of the header in a message box.  Then it offers the choice of whether to copy the header content to the clipboard, which would allow for pasting into a new message to the IT department (for example).

Then I added a button to Quick Launch and bound it to the macro.  The upshot is that I can select the message in my Inbox list of messages, press the button, and see the header!

Here's the code, which I couldn't have completed without the help of the Slipstick code sample.  Please be careful of unintended wrapping of code, particularly for the value of PR_TRANSPORT_MESSAGE_HEADERS constant!

Sub HeaderReview()
' Copy Message Header contents of selected Mail Item to the Windows Clipboard.
' See: https://www.slipstick.com/developer/code-samples/outlooks-internet-headers/
' 2020-01-23 LG  Created from CopyToClipboard dated 12/13/04

Dim objCB As New DataObject ' Clipboard object
Dim ol As New Outlook.Application
Dim oe As Outlook.Explorer
Dim mi As Outlook.MailItem
Dim strMH As String ' Mail Header

Set oe = ol.ActiveExplorer

If oe.CurrentFolder.DefaultItemType = olMailItem Then
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim olkPA As Outlook.PropertyAccessor
    Dim i As Integer
    Set mi = oe.Selection.Item(1)
    Set olkPA = mi.PropertyAccessor
    strMH = olkPA.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
    Debug.Print strMH
    i = MsgBox(strMH, vbYesNo, "Copy Message Header to Clipboard?")
    Select Case i
        Case vbYes
            objCB.SetText strMH
            objCB.PutInClipboard
    End Select
Else
    MsgBox "Sorry, HeaderReview() supports only Mail items at this time.", _
    , "HeaderReview() Help"
    
End If

End Sub

Open Org Mode Links With Default Windows Application

I've been using Org Mode for several years.

One Org Mode quirk is that the links in Org files always open in the Emacs editor, by default.  Click a link to Emacs_reference_card_v25.pdf on your hard drive and you might end up with a buffer that starts with this content...

%PDF-1.5
%    
3 0 obj
<<
/Length 4340      
/Filter /FlateDecode
>>
stream

I finally figured out a way to tell Emacs to open such "external" links with the default Windows application.  The multi-part solution involves first specifying all such links with a Special Prefix.  I chose "file:".  It's lame, I know, but it's also how you'd prefix an address for your web browser to open a local file.

So, for the PDF above, the link would look like this:
file:C:\emacs-25.2_64\doc\Emacs_reference_card_v25.pdf

The second part of the solution is to figure out which application on Windows you'd like to use to open the file "properly."  Rather than specifying multiple programs, such as a PDF reader for PDF files, a spreadsheet program for XLS/ODS, a word processor for DOC/ODT, etc., I decided that the single Windows command START already knows how to open all the programs on my computer.

If you were to enter the following in a command prompt, the program associated with the PDF extension would open the target file (assuming the file exists).

START C:\emacs-25.2_64\doc\Emacs_reference_card_v25.pdf

But there's a slight complication.  If the link contains a space or other delimiting character, you'll need to surround the file path with quotes like this: "C:\Path With Spaces\My PDF.pdf".  Unfortunately, START interprets quoted content as the title specification. Therefore, invoking the following would merely open a command prompt window with the title C:\Path With Spaces\My PDF.pdf

START "C:\Path With Spaces\My PDF.pdf"

So to get START to work as intended, you'd want to invoke it thusly:

START "DUMMY_TITLE" "C:\Path With Spaces\My PDF.pdf"

So having figured out the proper way to open file links, we create a function to implement it:

(defun ludditegeek-open-ext (path-to-media)
 (shell-command (concat "start \"ludditegeek-open-ext\" " path-to-media)))

Note that I chose to use the function name as the text for the title.  No matter, if all goes well, START will close the command prompt after it carries out the command -- most likely you'll never see the window.  For debugging, you could include the /WAIT switch, in which case you'll see the window, and the application will be listed in Task Manager with the title included.

Another thing to note here is that strings are defined in Lisp as characters enclosed in double-quotes.  But the title also requires double-quotes!  So I used a backslash character to escape the double-quote characters that are used to define the title.

The third part of the solution is to employ org-add-link-type to define the "file" link:

(org-add-link-type "file" 'ludditegeek-open-ext)

I call it with eval-after-load.  Here, finally, is what you can put in your .emacs:

(eval-after-load "org"
  '(progn
     ;; Create links in Org thusly:
     ;; [[file:/path/to/ppt.pptx][name of ppt]]
     ;; [[file:"/path with spaces/to/pdf.pdf"][name of pdf]]
     ;; [[file:/path/to/video.mkv][name of video]]
     (defun ludditegeek-open-ext (path-to-media)
       ;; Use Windows start command to open default application.
       ;; Note that first parameter to START is the command prompt window's title,
       ;; necessary for links that are enclosed in "", such as links with spaces.
       (shell-command (concat "start \"ludditegeek-open-ext\" " path-to-media)))
     (org-add-link-type "file" 'ludditegeek-open-ext)
     ))

I hope you find this useful!

Quickly Delete Many Excel Worksheets

In "Reverse the Order of Worksheets in an Excel Workbook" I show a VBA module that I wrote in order to reverse the order of several dozen worksheets in an Excel file.

Each tab contains a summary of data for a week.  After a few years, I had amassed over 100 worksheets.  So I decided I would split the workbook; each one would contain only one year's worth of data.  The workbook for 2018 would have only the 2018 worksheets; 2017 workbook, the 2017 worksheets; etc.

I copied the massive workbook to a 2018 workbook, from which I'd delete all but the 2018 worksheets.  Ditto for 2017, 2016, and, oh yes, 2019, as well.

Unfortunately I found this to be exceedingly tedious.  There didn't seem to be a way to delete multiple worksheets quickly and without many keystrokes and/or mouse clicks.  At best, I was able to select the six tabs that could be displayed at one time by clicking the left-most tab and shift-clicking the right-most tab.  Then I could right-click and delete the selected tabs.  But I'd have to do that about 20 times for each workbook!

So instead, I wrote the following VBA module to do it effortlessly.  Note that each worksheet is named with the date in YYYY-MM-DD format.  (So the worksheet for today would be named 2019-10-17. ) This module was used to delete all the 2019 worksheets.  Rather than write a nested loop to cycle through multiple years, I decided to change the year in the code manually.

  Sub DeleteNewWorksheets()
      ' 2019-03-24 TG  Created to clean up status records
      Dim Sheet As Worksheet
      Dim Book As Workbook
      Dim n As String
      Dim alerts As Boolean
      Dim i As Integer

      alerts = Application.DisplayAlerts
      Application.DisplayAlerts = False
      Set Book = ActiveWorkbook

      i = 0
      For Each Sheet In Book.Sheets
          n = Left(Sheet.Name, 4)
          If n = "2019" Then
              Debug.Print "Deleted " & Sheet.Name
              Sheet.Delete
              i = i + 1
          End If
      Next

      Application.DisplayAlerts = alerts
      MsgBox "Deleted " & i & " sheets.", vbInformation, "DeleteOldWorksheets Notification"
  End Sub

Reverse the Order of Worksheets in an Excel Workbook

Today I found myself wanting to reverse the order of sheet tabs in an Excel file.  The VBA code snippet below does just that.

Sub Worksheet_Reverse_Order()
Dim MySheet As Worksheet
Dim i As Integer

For Each MySheet In Worksheets
Debug.Print MySheet.Index, MySheet.Name
Next

For i = 2 To Worksheets.Count
Set MySheet = Worksheets(i)
MySheet.Move before:=Worksheets(1)
Next i

For Each MySheet In Worksheets
Debug.Print MySheet.Index, MySheet.Name
Next

End Sub


Background...
I had been maintaining weekly status updates as Excel spreadsheets, all grouped into one XLS document.  Each week I'd add a new sheet to the right of the previous week's worksheet tab.

But due to a change in workflow, I now have to copy the new sheet into that workbook rather than create it in the workbook.  In order to keep placing the new sheet after all the others, it's necessary to scroll to the end of the list of sheets and select "(move to end)".

"It would be so much easier if the sheets were in reverse order," I sighed to myself.  The thought of dragging them into reverse order manually was, well, unthinkable.  And thus this VBA macro was born.

Execute Text in MS Word Using the System Shell

I was writing a tutorial in Microsoft Word that describes commands that the reader is supposed to enter at the command prompt.  I thought it would be neat if I could run those commands from within Word to validate them as I entered them.

And so I came up with a surprisingly simple VBA subroutine that sends selected text to the shell.  It is quoted, below.  The code should be placed into a module in Normal.dot.

Note that I invoke two statements in the Shell.  They are separated by the double ampersands.  I combine them into a single string (strCmd) that I pass to the shell.

The first statement is to change to the current working directory, which I assume is the same directory that the Word document resides in.  This isn't fool proof, however.  One failure mode would be if someone were to start Word and create a new document without saving it to the hard drive before calling the routine.  Another failure mode would be if the Word document were to reside on a remote share through a UNC path, such as \\FileServer\ShareName\tutorial.doc -- it's not possible to CD into a UNC path.

The second statement is merely the selected text.

Also note that strCmd  is preceded by the Win32 command prompt CMD.EXE.  The "/D" switch makes sure that no "AutoRun" commands get executed.  The "/C" switch terminates CMD after the command is finished executing.  CMD is included because Shell isn't able to find DOS commands such as CD.

Sorry for the small font on this source code, but I wanted to ensure it wouldn't wrap.

Sub InvokeWithShell()
' Executes the selected text to using the shell
' 2014-06-09 LudditeGeek Created
    Dim strCmd As String
   
    If Selection.Characters.Count <= 1 Then
        MsgBox "Nothing Selected!", vbExclamation, "Invoke With Shell Macro Message"
    ElseIf Selection.Paragraphs.Count > 1 Then
        MsgBox "Multiple Lines Selected!", vbExclamation, "Invoke With Shell Macro Message"
    Else
        strCmd = Selection.Text
        Debug.Print "Invoking " & strCmd
        strCmd = "cd " & ActiveDocument.Path & "\ && " & strCmd
        Shell "cmd /D /C " & strCmd
    End If
   
End Sub

Outlook 2010 Macros -- Adventures in Getting Them to Work

In a post that included Outlook VBA code, I mentioned that I stopped using the macro because of Outlook's tougher security.

Today, I decided to try to eliminate the main problem that I had, namely an inability to run the macro except from within the VBA Project IDE.

Here's the scenario:  I have code that worked on Outlook 2000.  I assigned a toolbar button to call it.  But the toolbar button doesn't work in Outlook 2010.  Nothing happens.  Pressing Alt-F8 and clicking Run opens the VBA macro in the IDE and displays an error "Subroutine or Function not found" (paraphrased).  But then I can run the macro by clicking the play button.

One aspect of my solution was to make sure macros were not being disabled.  I choose to self-sign the macro rather than enable all macros.  First I used SelfCert.exe, which I found in the Outlook program directory (C:\Program Files\Microsoft Office\Office14).  SelfCert.exe can be used to create personal certificate -- it would work for me on my local computer.  After I created the certificate, I signed the macro (Tools | Digital Signatures | Choose).  After clicking OK, I immediately pressed Ctrl-sto save the macro.  And then I closed Outlook.  But when I did, it asked me whether I wanted to save VbaProject.OTM.  Odd.  First I responded No.  But when I reopened the VBA editor and checked for digital signatures, it reported that the macro was unsigned.  But answering Yes to the prompt to save didn't help either.  The macro still wasn't signed.

I wondered if the Read Only attribute had been set on VbaProject.OTM.  But no, I had Full Control rights on the file.  Yet, the file's timestamp was old!  It wasn't getting saved!  Ahh, but the old timestamp was a trick, an undocumented "feature".  According to this support thread, it was normal for the timestamp and file size of VbaProject.OTM to remain unchanged after a save.  (This is why the phrase "WTF?" was invented.)  I verified that the save was taking place by adding a comment to my code, saving, closing Outlook and then re-opening the macro.

After that little detour, I found that it was necessary for me to install the certificate in the "Trusted Root Certification Authorities."  This can be done deep within the bowels of the VBA editor.  Tools | Digital Signatures | Choose.  Click the link that says "Click here to view the certificate prope..."  Click the button "Install Certificate..." then Next.  In the next dialog box, click the radio button for "Place all certificates in the following store" then Browse.  Select "Trusted Root Certification Authorities" and then OK / Next your way out.  Save and close Outlook again.

BTW, every time you close Outlook, you should use Task Manager to verify that the Outlook process is not running.

Still the macro would not run except from within the editor.  But I found the solution in another thread.  My code was in a module.  After I moved it to ThisOutlookSession, I was able to assign an actual functioning toolbar button to it.

Whew!

It is very nice that even though I have other macros working that access the From and To properties of a message, I no longer get the annoying message box that warns me that my address book is being accessed and asks whether I want to allow that.

How I Got My Computer to Chime

It all started with a blog post by Sacha Chua that made me think, "I bet there's an app for that."  She described how she set up her smart phone to vibrate every half hour.

Soon after, I found Chime Time, by Hyperfine, which turned my tablet into an Aberdeen mantel clock.  And I loved the idea of chimes and bells so much that I also installed Bodhi Timer, by Yuttadhammo, which can be set up as a timer and play a variety of tones, including singing bowl, when the time is up.

Chime Time starts up automatically when Android starts up.  But Bodhi Timer does not, so I start it in the morning.  I might set it to go off every 15 minutes starting at about 7 minutes after the hour (or any 15-minute interval afterwards), or every 10 minutes starting at 5 after the hour (or any 10-minute interval afterwards), depending on when I can remember to do it.

Having bells and chimes sound off every so often reminds me to live in the present.  When I hear the sound I ask myself whether I'm using time mindfully.

However, my wife absolutely hates it.

Anyway, after enjoying this for a few days on my tablet, I wondered if there were something similar that I could use on my work computer.  I didn't feel like running the tablet just to have it make noise.

That motivated me to search on SourceForge, where I found TeaTimer.  But TeaTimer would pop up an alert box at the end of each interval because it was really intended as a timer for steeping tea.  So I decided to write my own in Visual Basic 6.

I wrote a simple application that would simply play a WAV file whenever it was invoked.  I chose chimes.wav from Microsoft Office, although I'm sure there's an equivalent from OpenOffice, as well.  Then I set a job in Task Scheduler to call it every 15 minutes.  There is a special trick to pulling this off, though, because while my program worked fine when invoked interactively, it refused to work when triggered by Task Scheduler.

I found the solution on the Microsoft Support website: http://support.microsoft.com/kb/86281.

Here's the source code in its entirety, comments removed for clarity:

Declare Function sndPlaySound Lib "WINMM.DLL" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Public Const SND_SYNC = &H0
Public Const SND_ASYNC = &H1
Public Const SND_NODEFAULT = &H2
Public Const SND_LOOP = &H8
Public Const SND_NOSTOP = &H10
'Here are explanations for the parameters: (removed)
Private Sub Main()
    Dim SoundName$
    Dim x%, wFlags%
   
    SoundName$ = "C:\Program Files (x86)\Microsoft Office\OFFICE11\MEDIA\CHIMES.WAV"
    wFlags% = SND_NODEFAULT ' Or SND_ASYNC
    x% = sndPlaySound(SoundName$, wFlags%)

End Sub

Outlook "Signature Code" Added

Back in June of 2005, I bragged about how I spent a few hours to write Outlook VBA code that eliminates a few keystrokes. I had a request for that code a few days ago, so I decided to edit that post to add the code. This link will take you to the edited post.

I should point out that I no longer use that code. I had lost it when my work computer was upgraded, and I failed to back up the source in a reasonable location. Too lazy to re-invent the wheel, I deigned to add signatures the MS Outlook way, using Alt-I S M X Enter. Besides, after a security patch was applied, Outlook would force me to respond to a warning every time I ran that macro. And anyway, when adding the signature to replies, I would always have to move the signature from the very bottom of the message to the point just after the end of my response and before the quoted message. (I'm pretty sure I can fix that, actually.)

So the code you see in the edited post came from an hour-and-a-half session I spent to recreate the code -- a Saturday night pursuit of geeky leisure.

More Outlook VBA: Toggling Grouping

In Am I Lazy or What? I described code that I wrote to add one of four signatures to an email message.

On Tuesday, I got tired of navigating the bowels of Outlook's menu system just to briefly turn grouping off and on. Grouping is a new, nifty feature in Outlook 2003.

So I wrote the following code to toggle grouping, and I customized my toolbar to add a button that invokes it. The code uses the XML property of the View object. The XML property is very cool. It looks like I can do a lot with it.

Sub ToggleGrouping() ' (c) 2005 Luddite Geek
' http://ludditegeek.blogspot.com
' Provide a way to toggle item grouping.
' 06/28/05 Created.

    Dim myOlApp As New Outlook.Application
    Dim myOlExp As Outlook.Explorer
    Dim myOlView As View
    Dim strView As String
    Dim i As Integer, j As Integer, n As Integer
            
    Set myOlExp = myOlApp.ActiveExplorer
    Set myOlView = myOlExp.CurrentView
    strView = myOlView.XML
    i = InStr(1, strView, "<arrangement>")
    j = InStr(i, strView, "<autogroup>")
    i = j + Len("<autogroup>")
    n = CInt(Mid(strView, i, 1))
        
    If n = 0 Then
        Mid(strView, i, 1) = 1
    ElseIf n = 1 Then
        Mid(strView, i, 1) = 0
    End If
    
    myOlView.XML = strView
    
End Sub

The code on this page is provided free of charge. The author assumes no liability for any undesired effects it might have. Users may freely distribute the code only if this disclaimer is included. Users may not claim the work as their own.

Am I Lazy or What?

Sunday's Dilbert cartoon might strike a nerve in some engineers. But why? A good engineer is a lazy engineer. The computer was invented because Charles Babbage got tired of calculating logarithms by hand.

Having written that, I'm proud to announce that I spent the last three hours automating something that used to take about five seconds. Yes, I know, but I do it a lot. Those five seconds add up. And now it only takes one second.

Here's the deal. My employer upgraded Outlook from 2000 to 2003 yesterday. But it didn't upgrade the other Office applications. I had used Word as my Outlook message editor, and I used its AutoText feature to add signatures to my emails. Since Outlook 2003 doesn't use Word 2000, I was forced to investigate Outlook's signature facility.

Outlook was thoughtful enough to allow for multiple signatures, and I thank the developers for that. But they failed to allow a proper method for selecting them with a keystroke. I was able to use keys, but look at the sequence involved: Alt-I S M x ENTER (where x is the first letter of a signature name.)¹

So I wrote a macro that allows me to choose one of my four signatures from a listbox. So now it's either Alt-S ENTER (for the default signature) or Alt-S x ENTER, a savings of two or three keystrokes. And that means extra time for blogging. :-)

---

The code for this automation can be entered into two text files called module1.bas and userform1.frm. I recommend first adding the module and creating the user form in the VBA editor. Add a listbox and two command buttons to the form. Their names should be listbox1, commandbutton1 and commandbutton2, and the form should have the name userform1.

Here's what should go into module1.bas:

Option Explicit

' Supporting code for selecting message signatures.
' Copyright 2005 - 2006 by Luddite Geek, luddite.geek@sbcglobal.net

Sub SelectSig()
Load UserForm1
UserForm1.Show

End Sub

Function HTMLize(strBody As String) As String
' Replaces vbCrLf with <br />
HTMLize = Replace(strBody, vbCrLf, "<br />")

End Function

Here's what should go into userform1.frm:

Option Explicit

' Signature Chooser Code
' Copyright 2005 - 2006 by Luddite Geek, luddite.geek@sbcglobal.net

Private Sub CommandButton1_Click()
' Based on http://www.outlookcode.com/codedetail.aspx?id=141

    Dim objItem As Object
    Dim thisMail As Outlook.MailItem
    'On Error Resume Next
    
    Set objItem = Application.ActiveInspector
    If Not objItem Is Nothing Then
        If objItem.CurrentItem.Class = olMail Then
            Set thisMail = objItem.CurrentItem
            If thisMail.HTMLBody = "" Then
                thisMail.Body = thisMail.Body & ListBox1.Text
            Else
                thisMail.HTMLBody = thisMail.HTMLBody & HTMLize(ListBox1.Text)
            End If
        End If
    End If
    
    Set objItem = Nothing
    Set thisMail = Nothing
    
    UserForm1.Hide
    Unload UserForm1
    
End Sub


Private Sub CommandButton2_Click()
    UserForm1.Hide
    Unload UserForm1

End Sub


Private Sub UserForm_Initialize()
UserForm1.Caption = "Luddite Geek Signature Chooser"
CommandButton1.Caption = "OK"
CommandButton1.Default = True
CommandButton2.Caption = "Cancel"
CommandButton2.Cancel = True

ListBox1.ColumnCount = 2

ListBox1.AddItem "Work"
ListBox1.List(0, 1) = vbCrLf & _
                      "Work Signature Line 1" & vbCrLf & _
                      "Work Signature Line 2" & vbCrLf & _
                      "Work Signature Line 3" & vbCrLf & _
                      "Work Signature Line 4"

ListBox1.AddItem "Home"
ListBox1.List(1, 1) = vbCrLf & _
                      "Home Signature Line 1" & vbCrLf & _
                      "Home Signature Line 2" & vbCrLf & _
                      "Home Signature Line 3" & vbCrLf & _
                      "Home Signature Line 4"

ListBox1.AddItem "Blog"
ListBox1.List(2, 1) = vbCrLf & _
                      "Blog Signature Line 1" & vbCrLf & _
                      "Blog Signature Line 2" & vbCrLf & _
                      "Blog Signature Line 3" & vbCrLf & _
                      "Blog Signature Line 4"

ListBox1.TextColumn = 2
ListBox1.ColumnWidths = "60;0"
ListBox1.SetFocus
ListBox1.ListIndex = 0

End Sub

---

¹ The keystroke sequence in Outlook 2010 is worse. It's Alt-N AS S and then you need to use the arrow key to select from the list of signatures. Pressing the first letter of the signature name no longer selects it.

---

Edited on 2006-07-06 to add requested code samples.
Edited on 2013-08-05 to update link to Dilbert cartoon and footnote 1.