Despite the plethora of other tools and editors that I use to create websites, there are times when I simply have to dust off my copy of (gasp!) Microsoft FrontPage 2003. It may be a dinosaur, but there are some things that it does really well, and periodically I simply need to use it.
An often-mocked and yet critically essential feature that FrontPage 2003 provided was affectionately called “Link Fix Up,” which was a feature that would replicate file renames across your entire website. In other words, if you had a file that was named “foo.html,” you could rename it to “bar.html” and FrontPage 2003 would update every hyperlink in every file in your entire website which pointed to that file. Needless to say, this feature was often indispensable when I was working with extremely large websites.
Other applications may have similar features, but when you combine that feature with FrontPage 2003’s built-in Visual Basic for Applications (VBA) functionality, you have a really powerful combination that can quickly seem indispensable.
With all of that being said, here’s a scenario where using FrontPage 2003’s “Link Fix Up” functionality with VBA really paid off, and I thought that it would make a great blog (in case anyone else runs into a similar issue and still has a copy of FrontPage 2003 lying around somewhere.)
Problem Description and Solution
I created a mixed-media website some years ago where I had thousands of images that were named like IMG5243.1024×768.png, IMG2745.1280×1024.png, IMG6354.800×600.png, etc. Some part of the file name obviously contained the image dimensions, which was useful at the time that I created the website, but that information was no longer necessary, and the filenames made the Obsessive Compulsive side of my behavior start to act up. (Too many characters.) With that in mind, I decided that I would rename all of those images back to simpler names like IMG5243.png, IMG2745.png, IMG6354.png, etc.
This is where FrontPage 2003’s “Link Fix Up” functionality would come in handy; trying to crawl every webpage in my website to update the thousands of image links would have been incredibly painful, whereas FrontPage 2003 would take care of keeping the image links up-to-date for free, provided that I could come up with a way to automate the renaming process. (Enter VBA.)
Here is where I quickly ran into a problem – I hadn’t standardized my file naming syntax. (Shame on me.) A lot of filenames had other parts or character strings that were going to cause problems, for example: IMG5243.1024x768_cropped.png, IMG2745.edited_1280x1024.png, IMG6354.new_800x600_small.png, etc. This meant that I was going to have to crawl through each filename character by character and look for image dimensions. This is not difficult through VBA, but it added a bit of complexity because I would have to locate any “x” character in a filename and then starting working my way to the right and left to see if it was surrounded by numbers. In other words, I would have to traverse every file name like “aaa_123x456_aaa.jpg” and “aaa.123×456.aaa.jpg” in order to remove “123×456,” while leaving “aaa.wxy.jpg” untouched. Of course, there were also topics to be considered after I removed the numbers, like malformed image names like “aaa__aaa.jpg” and “aaa..aaa.jpg” that had unnecessary character duplications.
VBA Bulk File Renaming Macro
All that being said, here is the VBA macro that I created, which worked great; I was able to have this macro rename my thousands of images in a matter of seconds, and FrontPage 2003 made sure that every image URL in my HTML/ASP files were kept up-to-date.
Sub RemoveImageSizesFromFilenames() Dim intSectionCount As Integer Dim intXPosition As Integer Dim intCharPosition As Integer Dim intDictionaryCount As Integer Dim objWebFile As WebFile Dim strExt As String Dim strOldName As String Dim strNewName As String Dim strUrlStub As String Dim strSections() As String Dim strWidth As String Dim strHeight As String Dim objDictionary As Object Dim objItem As Object Dim varKeys As Variant Dim varItems As Variant ' Define the list of file extensions to process. Const strValidExt = "jpg|jpeg|gif|bmp|png" ' Create a dictionary object to hold the list of old/new filenames. Set objDictionary = CreateObject("Scripting.Dictionary") ' Verify that a website is open; exit if not. If Len(Application.ActiveWeb.Title) = 0 Then MsgBox "A website must be open." & vbCrLf & vbCrLf & "Aborting.", vbCritical Exit Sub End If ' Loop through the files colleciton for the website. For Each objWebFile In Application.ActiveWeb.AllFiles ' Retrieve the file extension for each file. strExt = LCase(objWebFile.Extension) ' Verify if the filename is part of the valid list. If InStr(strValidExt, strExt) Then ' Retrieve the current file name strOldName = LCase(Left(objWebFile.Name, Len(objWebFile.Name) - Len(strExt) - 1)) ' Verify a multi-part filename. If InStr(strOldName, ".") Then ' Split the multi-part filename into sections. strSections = Split(strOldName, ".") ' Loop through the sections. For intSectionCount = 0 To UBound(strSections) ' Verify that each section actually has characters in it. If Len(strSections(intSectionCount)) > 1 Then ' Check for a lowercase X character. intXPosition = InStr(2, strSections(intSectionCount), "x") ' Make sure that the X character does not start or end the string. If intXPosition > 1 And intXPosition < Len(strSections(intSectionCount)) Then ' Make sure that the X character has numbers to the left and right of it. If IsNumeric(Mid(strSections(intSectionCount), intXPosition - 1, 1)) And IsNumeric(Mid(strSections(intSectionCount), intXPosition + 1, 1)) Then ' Initialize the width/height strings. strWidth = "" strHeight = "" ' Loop through the string to find the height. For intCharPosition = intXPosition + 1 To Len(strSections(intSectionCount)) If IsNumeric(Mid(strSections(intSectionCount), intCharPosition, 1)) Then strHeight = strHeight & Mid(strSections(intSectionCount), intCharPosition, 1) Else Exit For End If Next ' Loop through the string to find the width. For intCharPosition = intXPosition - 1 To 1 Step -1 If IsNumeric(Mid(strSections(intSectionCount), intCharPosition, 1)) Then strWidth = Mid(strSections(intSectionCount), intCharPosition, 1) & strWidth Else Exit For End If Next ' Remove the width/height string from the current filename section. strSections(intSectionCount) = Replace(strSections(intSectionCount), strWidth & "x" & strHeight, "") End If End If End If Next ' Reassemble the file sections. strNewName = Join(strSections, ".") If Right(strNewName, 1) = "." Then strNewName = Left(strNewName, Len(strNewName) - 1) ' Cleanup several unnecessary character sequences. If StrComp(strOldName, strNewName, vbTextCompare) <> 0 Then strOldName = strOldName & "." & strExt strNewName = strNewName & "." & strExt strNewName = Replace(strNewName, "_.", ".", 1, -1) strNewName = Replace(strNewName, "._", "_", 1, -1) strNewName = Replace(strNewName, "..", ".", 1, -1) strNewName = Replace(strNewName, "__", "_", 1, -1) strUrlStub = Left(objWebFile.Url, Len(objWebFile.Url) - Len(strOldName)) ' Add the old/new file URLs to the dictionary. objDictionary.Add strUrlStub & strOldName, strUrlStub & strNewName End If End If End If Next varKeys = objDictionary.Keys varItems = objDictionary.Items ' Loop through the collection of URLs to rename. For intDictionaryCount = 0 To (objDictionary.Count - 1) ' Avoid collisions with existing URLs. If Application.ActiveWeb.LocateFile(varItems(intDictionaryCount)) Is Nothing Then ' Get current URL. Set objWebFile = Application.ActiveWeb.LocateFile(varKeys(intDictionaryCount)) ' Rename the URL. objWebFile.Move varItems(intDictionaryCount), True, False End If Next End Sub
There are a couple of additional details about this macro that you should consider:
First of all, this macro intentionally avoids overwriting the destination filename if it already exists. For example, if you have two files named IMG1234.100×100.jpg and IMG1234.200×200.jpg, simply removing the image size characters from each file name would result in a collision for the name IMG1234.jpg. What the macro currently does is to rename the first file, then it leaves any possible collisions unchanged. You could easily modify this script to prompt the user what to do, or you could configure it to rename each file with a syntax like IMG1234a.jpg / IMG1234b.jpg / IMG1234c.jpg, but I’ll leave that up to you.
Second, I wrote this macro for a specific set of file types and filenames, but you could modify the macro for a variety of scenarios. For example, one developer that I knew liked to test his content on his production server by creating preview files with names like foo.preview.html and bar.preview.aspx. This allowed the production files to coexist on the same server with the preview files, although the production files would have the production-ready filenames like foo.html and bar.aspx. Once he was ready to push the preview files into production, he would simply rename the necessary files. This system worked for a small set of files, but it didn’t scale very well, so the amount of labor on his part would increase as the website grew more complex. (Of course, he should have been using a development website for his preview testing, but that’s another story.) In any event, this macro could easily be modified to remove the “.preview.” string from every file name.