Import and Hyperlink text in Excel VBA

I have list of URLs that are in text files.  I need to import those text files into excel and hyperlink all of the URLs in the file.  So I created a excel file that is going to be the template.  The code is run from the template and it prompts me for a file.  Excel files and text files may be imported.  Upon importation of the list, the urls are immediately hyperlinked.

—- VBA Code—-

Sub GetFile()
Dim MyLine As String, FileNum As Long, file1, MySheet As Worksheet, FileLen As Long

Set MySheet = Sheets(“URLs”)

file1 = Application.GetOpenFilename(“Excel Files,*.xls,Text Files,*.txt”, _
2, “Select file “, , False)
If file1 = False Then
MsgBox “No file selected – exiting”
Exit Sub
End If

FileNum = FreeFile

Open file1 For Input As #FileNum
If Err.Number <> 0 Then
MsgBox “File open error #” & Err.Number & “!”, vbCritical, “Error!”
Exit Sub
End If

MySheet.Cells.ClearContents
MySheet.Cells(1, 1) = “URL”

FileLen = 2

Line Input #FileNum, MyLine
While Not EOF(FileNum)
MySheet.Cells(FileLen, 1) = MyLine
FileLen = FileLen + 1
Line Input #FileNum, MyLine
Wend

Dim Cell As Range

For Each Cell In ActiveSheet.UsedRange
If Trim(Cell) > “” Or Trim(Cell) = “URL” Then
If Left(Trim(Cell), 4) = “http” Then ‘ handles http and https
ActiveSheet.Hyperlinks.Add Cell, Trim(Cell.Value)
Else ‘ Default to http if no protocol was specified.
ActiveSheet.Hyperlinks.Add Cell, “http://” & Trim(Cell.Value)
End If
End If
Next

Close #FileNum

End Sub

—VBA Code—

Please follow and like us:

Author: admin2344

Leave a Reply