Excel/VBA 301 redirects for htaccess

This Excel/VBA program takes a column list of urls where every odd numbered row is the original url and the next even row below it is the url that represents the redirected URL and writes an .htaccess statement for redirects.  This is helpful if you do not wish to manually write hundreds of redirects.  What this will do is take your large list in column A, put the original url’s in Column C, the redirect url’s in Column D, and your .htaccess 301 redirect statement in Column E:

Option Explicit
Private i As Integer ‘ Represents the current row we are evaluation in Column A
Private j As Integer ‘ Represents the current row for pasting in Column C,D, and E
Private ht_stmt As String  ‘ Represents the current row we are evaluation in Column A
Private Const stacked_rows = 2  ‘ Number of Rows that are stacked, in this case, 2, for both original url and new url


Sub side_by_side()

‘ Initialize Variables
i = 1
j = 0

‘ clear data
Columns(“C:E”).Select
Selection.ClearContents

‘ Loop Through Each Row until we hit a blank row
Do While Len(Trim(Cells(i, 1))) > 0
j = j + 1

‘ get original url
Cells(i, 1).Select
Selection.Copy
Cells(j, 3).Select
Selection.PasteSpecial

‘ now get redirect url
Cells(i + 1, 1).Select
Selection.Copy
Cells(j, 4).Select
Selection.PasteSpecial

‘ .htaccess statement – this will do a standard redirect formula
ht_stmt= “RewriteRule ^” & Replace(Cells(j, 3), ” “, “”) & ” http://www.” & Replace(Cells(j, 4), ” “, “”) & ” [R=301,L]”
Cells(j, 5).Formula = c1

‘ increment i – jump to next row of paired urls
i = i + stacked_rows
Loop

End Sub

Advertisements