Put this in a module as VBA, and use the function in your workbook, eg. =GetPageTitle(A1)! Very handy for checking if a URL still works!
Function GetPageTitle(a As String)
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = False
.Navigate a
Do Until .ReadyState = 4
DoEvents
Loop
GetPageTitle = .document.Title
.Quit
End With
End Function
A Loop to go trought a list of URL’s (url in column B) to retrieve the title (and place it in column E):
Sub Test2()
' Select cell A2, *first line of data*.
Range("B120").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
Dim a As String
Link = ActiveCell.Value
Row = ActiveCell.Row
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = False
.Navigate Link
Do Until .ReadyState = 4
DoEvents
Loop
ActiveSheet.Cells(Row, 5).Value = .document.Title
.Quit
End With
' Step down 1 row from present location.
Row = Row + 1
ActiveCell.Offset(1, 0).Select
Loop
MsgBox ("All Done!")
End Sub
Source: MrExcel Forum