Happy 1st Birthday to Baby Zoe Collette!

Open VBA editor (Alt-F11). In the immediate window, type the following code, replacing ‘x’ with the appropriate Slide and Shape indices:

? Application _
.ActivePresentation _
.Slides(x) _
.Shapes(x) _
.LinkFormat _
.SourceFullName

This will output the full path information to the linked media file. To change the path, such as in the case of removing full path information and leaving just the filename, use the following syntax:

Application _
.ActivePresentation _
.Slides(x) _
.Shapes(x) _
.LinkFormat _
.SourceFullName = "mediafile.mpg"

I finally retrieved a demo I did with a band eighteen years ago. Eighteen years…

Ice Angel – Running

Ice Angel – Johnny

Ice Angel – Cheating Heart

Ice Angel – Witch

Update: Visit the Ice Angel MySpace Page (Thanks, J)!

Yourli.st is a smooth, registration-free online service for sending yourself reminder e-mails. The concept for this plugin came about as I was developing my cooking website, ScreamingSaucepan.com. It occurred to me it might be nice to be able to remind myself about recipes in some way other than tagging them on del.icio.us, perhaps at a time when I might be wandering around the aisles of the grocery store. This plugin has only the most basic functionality, no administration panel, no options. It simply opens the yourli.st page and prepopulates the Event Title field with the title of the blog posting and inserts the permalink in the Event Summary. All you need to do is fill in your e-mail address, target date, and whatever other options you’d like to adjust.

I’d like to thank Alan over at YourLi.st for building this service and his positive feedback on the plugin.

I’d also like to thank Ronald Huereca for his great series How to Write a WordPress Plugin. If you have any intention of developing your own plugins for WordPress, go there immediately.

Download the plugin here.

To avoid losing action items as my e-mail flows in, I make extensive use of the “Flag for Follow Up” feature in Outlook. Having grown tired from clicking around the dialog box with the calendar that pops up next to the “Due By” field, I created the following VBA macro:

Sub FlagForXMinutes(intMinutes As Integer, strFlagRequest As String)
Dim Item As Object
Dim SelectedItems As Selection
Set SelectedItems = Outlook.ActiveExplorer.Selection
For Each Item In SelectedItems
With Item
.FlagStatus = 2
.FlagDueBy = CStr(CDate(Format(CDbl(Now) + intMinutes / 1440)))
.FlagRequest = strFlagRequest
.Save
End With
Next Item
End Sub

I’ve assigned a couple of buttons on my Outlook toolbar with the following macros attached:

Sub FlagForFollowUp15Minutes()
FlagForXMinutes 15, "Follow Up"
End Sub

Sub FlagForFollowUp30Minutes()
FlagForXMinutes 30, "Follow Up"
End Sub

I have not played around with too many variations, but this works with items contained within Public Folders as well as Meeting Requests and Responses, thanks to the “Dim Item As Object” declaration which provides some added flexibility.

Here are a couple of not-so-very-well documented tips for MS Access 2000. I haven’t tested them on any other versions of Access, though I suspect most of them should work on later versions.

These tips require the use of the Microsoft Visual Basic Integrated Development Environment (IDE), which can be accessed by Alt-F11.

Use the Immediate Window

The Immediate Window can be accessed via Ctrl-G (or View – Immediate Window). It is an invaluable asset when debugging or testing. I’m not a programmer. I like to say I know just enough programming and scripting to be extremely dangerous. Despite this, I’ve saved myself days, possibly weeks, of time and frustration using this tool.

Use the Question Mark (?)

I discovered this by accident, but I remembered back in the old days programming Basic on my Atari 400 that the question mark character was shorthand for “Print.” As it turns out, VBA is a distant cousin to the old Basic. You can still use the “print” keyword, but the question mark is your friend. (Note that the question mark will be converted to the “print” keyword automatically in the Code Editor.)

Get Full Path and Connection Information to Linked Table

This can be a huge time saver, especially when working with databases that have a lot of linked tables and those with long path names. Sometimes the Linked Table Manager utility just doesn’t cut it.

? currentdb.TableDefs("tablename").Connect
or
print currentdb.TableDefs("tablename").Connect

Try it. You just might like the results.

Incorporate Custom Functions in Queries

This one might scare some people, as it requires some hand coding of SQL. That means leaving the comfort zone of the visual Query Designer and entering the land of Structured Query Language. It also requires some hand coding of VBA. This can be terrifying. Once you’re over the initial fear, however, you’ll be pleased to know that it’s pretty easy to get started.

Let’s take a sample table. We’ll call it tblPeople. Please note: The prefix “tbl” is a standard naming convention, but I won’t get into that here. I’d highly recommend the VBA Developer’s Handbook – (buy it here) for anyone interested in getting their hands dirty with that sort of thing. We have three fields we’re concerned with: fname, mname, and lname. We have three records:

Suppose we wanted these names to print out in a mail merge-friendly format. One method would be to hard code the logic in SQL. My personal preference is to write custom functions for tasks like this, as you end up with more readable and maintainable SQL. For instance, here’s the function:

Function fnNameForMerge(strFName As Variant, strMName As Variant, strLName As Variant) As String
fnNameForMerge = strFName & IIf(strMName = "" Or IsNull(strMName), " ", " " & strMName & " ") & strLName
End Function

We can now test the function using the Immediate Window:

Once satisfied, we can now use the function in the following SQL statement:

SELECT tblPeople.fname, tblPeople.mname, tblPeople.lname, fnNameForMerge(fname,mname,lname) AS NameForMerge
FROM tblPeople

This can also be done in the Query Designer:

The results look like this:

The more I use Access, the more I tend to dig into the SQL code. I’ve found myself leaning towards custom functions whenever my SQL starts getting messy, particularly when logic starts creeping in, such as in the example. Even if you’re a SQL-phobe, consider that it’s still pretty easy to use custom functions in the Query Designer. You will, however, need to acquaint yourself with Modules.

Don’t let VBA scare you. You don’t have to be a “programmer” to mess around with some code. It just might save you some time or even add quality years to your life. I know it has for me.

My Rant Against Anti-Automation

Unfortunately, there seems to be a tendency within organizations to disable macros due to the proliferation of malicious code. This inevitably means more people doing more manual labor, typing things they’ve typed hundreds of times before, moving the mouse and clicking on things over and over, inching ever closer to Repetitive Stress Injuries. It’s a shame. The last I checked, computers were supposed to automate things. Personally, I’d rather take the risk and encourage people to experiment, to unleash their Inner Automator. Repetition is evil – unless you’re actually LEARNING from the exercise.

If you’ve found any of this useful, please feel free to drop me a line or comment.