Creating 413 Web Pages with VBA

Most of my Excel thinking lately has been geared toward moving this blog. So, I blog about it. There was a lot of Excel work involved in getting moved, mostly working with text files in VBA. Find and replace? Who need them. As Bernie Deitrick once said “When all you have is a hammer, every problem looks like a nail.” Therefore, Excel was my tool of choice.

The following sub was used to create 413 web pages to redirect the old Permalinks to the new posts. It also has some body html for kicks. The old Permalinks are in column C, the post date is in column B, and the new post location is in column D.

Sub MakeRedirs()

    Dim sHtml1 As String, sHtml2 As String
    Dim sHtml3 As String, sHtml4 As String
    Dim rCell As Range
    Dim lFnum As Long, sFname As String
    Dim lFileStart As Long
    Dim sFullHtml As String
    Const sSTARTPATH As String = “C:Documents and SettingsDick” & _
        “My DocumentsRevisemedia2004?
    ‘Set up some html strings
    sHtml1 = “<html>” & vbNewLine & “<meta HTTP-EQUIV=””REFRESH”” content=””0; url=”
    sHtml2 = Chr(34) & “>” & vbNewLine & “<body>” & vbNewLine & vbTab
    sHtml2 = sHtml2 & “<p><a href=””””>Daily Dose of Excel</a>”
    sHtml2 = sHtml2 & “moved servers in November of 2004.  You should have been redirected “
    sHtml2 = sHtml2 & “to the new location.</p>” & vbNewLine
    sHtml2 = sHtml2 & “<p>Page you requested: <a href=” & Chr(34)
    sHtml3 = “</a></p>” & vbNewLine & “<p>New page: <a href=” & Chr(34)
    sHtml4 = “</a></p>” & vbNewLine & “</body>” & vbNewLine & “</html>”

    ‘loop through the old Permalinks
    For Each rCell In Sheet1.Range(“C1:C413?).Cells
        ‘Find where the filename starts
        lFileStart = InStrRev(rCell.Value, “/”) + 1
        ‘Name for the textfile in a directory similar to TypePad’s
        ‘Permalink structure
        sFname = sSTARTPATH & Format(rCell.Offset(0, -1).Value, “mm”) & “/” & _
            Mid(rCell.Value, lFileStart, Len(rCell.Value))
        ‘Get the next available text file number
        lFnum = FreeFile
        ‘Open (create) the text file for writing
        Open sFname For Output As lFnum
        ‘Link the html strings and the old and new urls to creat a long
        sFullHtml = sHtml1 & rCell.Offset(0, 1).Value & sHtml2 & rCell.Value & _
            Chr(34) & “>” & rCell.Value & sHtml3 & rCell.Offset(0, 1).Value & _
            Chr(34) & “>” & rCell.Offset(0, 1).Value & sHtml4
        ‘Write to the file
        Print #lFnum, sFullHtml
        ‘Close the file
        Close lFnum
    Next rCell
    ‘old = /excel/2004/03/sumif_between_t.html
    ‘new = /archives/2004/03/29/sumif-between-two-dates/
End Sub

Posted in Uncategorized

2 thoughts on “Creating 413 Web Pages with VBA

  1. As Bernie Deitrick once said…

    I don’t think Bernie would want the credit…
    Abraham Kaplan’s Law of the Instrument, often mistakenly attributed to Mark Twain: ‘Give a small boy a hammer and he will find that everything he encounters needs pounding.’ Then there’s Tully’s Corollary to the Law of the Instrument: ‘When you really want to drive a nail, everything starts to look like a hammer.’

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.