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—
