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
‘string
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
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.’
At my last job, it seems we were always forced to use the wrong wrench to pound in the wrong screw.
– Jon