Geeky Bob | All posts tagged 'Visual Basic'

Using FrontPage 2003 to Bulk Rename Images Using VBA

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.1024x768.png, IMG2745.1280x1024.png, IMG6354.800x600.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.123x456.aaa.jpg" in order to remove "123x456," 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

In Closing...

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.100x100.jpg and IMG1234.200x200.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.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

Programmatically Flushing FTP Logs

I had a great question from Scott Forsyth earlier today about programmatically flushing the logs for an FTP site. Scott had noticed that there was a FlushLog method listed on the following page in the IIS Configuration Reference:

http://www.iis.net/ConfigReference/system.applicationHost/sites/site/ftpServer

Unfortunately there wasn't a code sample for that method; but as luck would have it, I had already written some code to do just that. (I love synchronicity...) With that in mind, I though that I'd post the code in a blog. In keeping with the cross-language samples that I wrote for the topics in the Configuration Reference, I thought that's I'd include several languages in this blog to make it easier for someone else to copy and paste.

C#

using System;
using System.Text;
using Microsoft.Web.Administration;

internal static class Sample
{
   private static void Main()
   {
      using (ServerManager serverManager = new ServerManager())
      {
         Configuration config = serverManager.GetApplicationHostConfiguration();
         // Retrieve the sites collection.
         ConfigurationSection sitesSection = config.GetSection("system.applicationHost/sites");
         ConfigurationElementCollection sitesCollection = sitesSection.GetCollection();

         // Locate a specific site.
         ConfigurationElement siteElement = FindElement(sitesCollection,"site","name",@"ftp.contoso.com");
         if (siteElement == null) throw new InvalidOperationException("Element not found!");

         // Create an object for the ftpServer element.
         ConfigurationElement ftpServerElement = siteElement.GetChildElement("ftpServer");
         // Create an instance of the FlushLog method.
         ConfigurationMethodInstance FlushLog = ftpServerElement.Methods["FlushLog"].CreateInstance();
         // Execute the method to flush the logs for the FTP site.
         FlushLog.Execute();
      }
   }

   // Locate and return the index for a specific element in a collection.
   private static ConfigurationElement FindElement(ConfigurationElementCollection collection, string elementTagName, params string[] keyValues)
   {
      foreach (ConfigurationElement element in collection)
      {
         if (String.Equals(element.ElementTagName, elementTagName, StringComparison.OrdinalIgnoreCase))
         {
            bool matches = true;
            for (int i = 0; i < keyValues.Length; i += 2)
            {
               object o = element.GetAttributeValue(keyValues[i]);
               string value = null;
               if (o != null)
               {
                  value = o.ToString();
               }
               if (!String.Equals(value, keyValues[i + 1], StringComparison.OrdinalIgnoreCase))
               {         matches = false;
                  break;
               }
            }
            if (matches)
            {
               return element;
            }
         }
      }
      return null;
   }
}

VB.NET

Imports System
Imports System.Text
Imports Microsoft.Web.Administration

Module Sample
   Sub Main()
      Dim serverManager As ServerManager = New ServerManager
      Dim config As Configuration = serverManager.GetApplicationHostConfiguration
      ' Retrieve the sites collection.
      Dim sitesSection As ConfigurationSection = config.GetSection("system.applicationHost/sites")
      Dim sitesCollection As ConfigurationElementCollection = sitesSection.GetCollection

      ' Locate a specific site.
      Dim siteElement As ConfigurationElement = FindElement(sitesCollection,"site","name","ftp.contoso.com")
      If (siteElement Is Nothing) Then
         Throw New InvalidOperationException("Element not found!")
      End If

      ' Create an object for the ftpServer element.
      Dim ftpServerElement As ConfigurationElement = siteElement.GetChildElement("ftpServer")
      ' Create an instance of the FlushLog method.
      Dim FlushLog As ConfigurationMethodInstance = ftpServerElement.Methods("FlushLog").CreateInstance()
      ' Execute the method to flush the logs for the FTP site.
      FlushLog.Execute()

   End Sub

   ' Locate and return the index for a specific element in a collection.
   Private Function FindElement(ByVal collection As ConfigurationElementCollection, ByVal elementTagName As String, ByVal ParamArray keyValues() As String) As ConfigurationElement
      For Each element As ConfigurationElement In collection
         If String.Equals(element.ElementTagName, elementTagName, StringComparison.OrdinalIgnoreCase) Then
            Dim matches As Boolean = True
            Dim i As Integer
            For i = 0 To keyValues.Length - 1 Step 2
               Dim o As Object = element.GetAttributeValue(keyValues(i))
               Dim value As String = Nothing
               If (Not (o) Is Nothing) Then
                  value = o.ToString
               End If
               If Not String.Equals(value, keyValues((i + 1)), StringComparison.OrdinalIgnoreCase) Then
                  matches = False
                  Exit For
               End If
            Next
            If matches Then
               Return element
            End If
         End If
      Next
      Return Nothing
   End Function

End Module

JavaScript

// Create a Writable Admin Manager object.
var adminManager = new ActiveXObject('Microsoft.ApplicationHost.WritableAdminManager');
adminManager.CommitPath = "MACHINE/WEBROOT/APPHOST";

// Retrieve the sites collection.
var sitesSection = adminManager.GetAdminSection("system.applicationHost/sites","MACHINE/WEBROOT/APPHOST");
var sitesCollection = sitesSection.Collection;

// Locate a specific site.
var siteElementPos = FindElement(sitesCollection,"site",["name","ftp.contoso.com"]);
if (siteElementPos == -1) throw "Element not found!";

// Retrieve the site element.
var siteElement = sitesCollection.Item(siteElementPos);
// Create an object for the ftpServer element.
var ftpServerElement = siteElement.ChildElements.Item("ftpServer");
// Create an instance of the FlushLog method.
var FlushLog = ftpServerElement.Methods.Item("FlushLog").CreateInstance();
// Execute the method to flush the logs for the FTP site.
FlushLog.Execute();

// Locate and return the index for a specific element in a collection.
function FindElement(collection, elementTagName, valuesToMatch) {
   for (var i = 0; i < collection.Count; i++) {
      var element = collection.Item(i);
      if (element.Name == elementTagName) {
         var matches = true;
         for (var iVal = 0; iVal < valuesToMatch.length; iVal += 2) {
            var property = element.GetPropertyByName(valuesToMatch[iVal]);
            var value = property.Value;
            if (value != null) {
               value = value.toString();
            }
            if (value != valuesToMatch[iVal + 1]) {
               matches = false;
               break;
            }
         }
         if (matches) {
            return i;
         }
      }
   }
   return -1;
}

VBScript

' Create a Writable Admin Manager object.
Set adminManager = CreateObject("Microsoft.ApplicationHost.WritableAdminManager")
adminManager.CommitPath = "MACHINE/WEBROOT/APPHOST"

' Retrieve the sites collection.
Set sitesSection = adminManager.GetAdminSection("system.applicationHost/sites","MACHINE/WEBROOT/APPHOST")
Set sitesCollection = sitesSection.Collection

' Locate a specific site.
siteElementPos = FindElement(sitesCollection,"site",Array("name","ftp.contoso.com"))
If siteElementPos = -1 Then
   WScript.Echo "Element not found!"
   WScript.Quit
End If

' Retrieve the site element.
Set siteElement = sitesCollection.Item(siteElementPos)
' Create an object for the ftpServer element.
Set ftpServerElement = siteElement.ChildElements.Item("ftpServer")
' Create an instance of the FlushLog method.
Set FlushLog = ftpServerElement.Methods.Item("FlushLog").CreateInstance()
' Execute the method to flush the logs for the FTP site.
FlushLog.Execute()

' Locate and return the index for a specific element in a collection.
Function FindElement(collection, elementTagName, valuesToMatch)
   For i = 0 To CInt(collection.Count) - 1
      Set element = collection.Item(i)
      If element.Name = elementTagName Then
         matches = True
         For iVal = 0 To UBound(valuesToMatch) Step 2
            Set property = element.GetPropertyByName(valuesToMatch(iVal))
            value = property.Value
            If Not IsNull(value) Then
               value = CStr(value)
            End If
            If Not value = CStr(valuesToMatch(iVal + 1)) Then
               matches = False
               Exit For
            End If
         Next
         If matches Then
            Exit For
         End If
      End If
   Next
   If matches Then
      FindElement = i
   Else
      FindElement = -1   End If
End Function

Summary

Hopefully this gives you an idea of how to call the FlushLog method. You can also use these examples to call the Start and Stop methods for FTP sites; you just need to substitute the correct method in place of the FlushLog method.

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/