Wednesday, September 17, 2008

Excel Convert Multiple Cells to URLs

This will convert the selected cell range to a URL.  It assumes that the selected cells are vaild URLs but will skip empty cells.  

Sub Convert2URL()
    For Each cell In Selection
        If cell.Value <> "" Then
            If Left(cell.Value, 7) = "http://" Then
                URL = cell.Value
            Else
                URL = "http://" + cell.Value
            End If
            ActiveSheet.Hyperlinks.Add Anchor:=cell, _
              Address:=URL, TextToDisplay:=cell.Value
        End If
    Next cell
End Sub

Now lets say you have a list of order numbers and you want to click a link in Excel and go right to your order management screen you could do:

Sub Convert2URL()
    For Each cell In Selection
        If cell.Value <> "" Then
            If Left(cell.Value, 44) = "http://myordermanager.net/ordermanager.php?" Then
                URL = cell.Value
            Else
                URL = "http://myordermanager.net/ordermanager.php?" + cell.Value
            End If
            ActiveSheet.Hyperlinks.Add Anchor:=cell, _
              Address:=URL, TextToDisplay:=cell.Value
        End If
    Next cell
End Sub

Yup, its neat-o and works like a charm!

1 comment:

Johan Cyprich said...

Visual Studio 2008 has projects for Office 2003 and 2007. I'd like to learn these so that I can stop using VBA and use C# to write Office apps. :)