Hello excelhatesme2017,
Welcome to the forum!
This macro will return the title for each URL in your list. Then macro starts at cell "A1" on the ActiveSheet and goes down to the last URL in the column.
If the URL is opened successfully then the title is placed to the right of the URL in column "B". You can interrupt the loop by pressing the keys CTRL+BREAK.
Add a new VBA Module to your VBA Project. Copy and Paste the code below into the new Module.
Sub GetURLTitles()
Dim Cell As Range
Dim HTMLdoc As Object
Dim PageSrc As String
Dim RngBeg As Range
Dim RngEnd As Range
Dim row As Long
Dim Title As Object
Dim XMLHTTP As Object
Dim Wks As Worksheet
Set Wks = ThisWorkbook.ActiveSheet
Set RngBeg = Wks.Range("A1")
Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
Set HTMLdoc = CreateObject("htmlfile")
For row = RngBeg.row To RngEnd.row
DoEvents
With XMLHTTP
Set Cell = RngBeg.Offset(row - RngBeg.row, 0)
.Open "GET", Cell.Value, False
.Send
If .statusText = "OK" Then
HTMLdoc.Write .responseText
Set Title = HTMLdoc.GetElementsByTagName("Title")
If Title.Length > 0 Then
Cell.Offset(0, 1).Value = Title(0).innerHTML
End If
End If
End With
Next row
End Sub
Bookmarks