How to find the title of an URL using VBA in MS Excel-CodePudding

I am working on a VBA project to get Page title and created date from an URL. I found this code online but it's not working since IE is not available. Is there any way I can get the title without IE?

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

I have tried to use Edge as Internet Explorer but it's not working. I have tried this code also HTML Page Title in Excel VBA

CodePudding user response：

If you want to automate Edge with VBA, you need to use SeleniumBasic. SeleniumBasic is a Selenium based browser automation framework for VB.Net, VBA and VBScript.

You can follow the steps below:

Download the latest version of SeleniumBasic v2.0.9.0 from this link and install it.
Download the corresponding version of Edge WebDriver from this link.
Find the path of SeleniumBasic which is C:\Users\%username%\AppData\Local\SeleniumBasic in my computer (it might also be in this path C:\Program Files\SeleniumBasic), copy the Edge WebDriver msedgedriver.exe to this path.
Rename msedgedriver.exe to edgedriver.exe.
Open Excel and write the VBA code.
In the VBA code interface, click Tools > References, add Selenium Type Library reference and click OK to save.
Sample VBA code to automate Edge using SeleniumBasic to get URL title:

Public Sub Selenium()
        Dim bot As New WebDriver
        bot.Start "edge", "https://www.bing.com"
        bot.Get "/"
        bot.Wait 5000
        Debug.Print bot.Window.Title  'this line of code can get title
End Sub

CodePudding user response：

Get Webpage Title

Option Explicit


Sub GetPageTitleTEST()

    Const URL As String = "https://www.google.com"
  
    Dim PageTitle As String: PageTitle = GetPageTitle(URL)
  
    If Len(PageTitle) > 0 Then
        Debug.Print GetPageTitle(URL)
    Else
        Debug.Print "Nope."
    End If

End Sub


Function GetPageTitle( _
    ByVal URL As String) _
As String
' MSXML2.ServerXMLHTTP
' WinHttp.WinHttpRequest.5.1
' MSXML2.XMLHTTP
' MSXML2.XMLHTTP.6.0
    Const ProcName As String = "GetPageTitle"
    On Error GoTo ClearError

    Dim Response As String
    With CreateObject("MSXML2.ServerXMLHTTP")
        .Open "Get", URL, False
        .send
        Response = .responseText
    End With
    With CreateObject("htmlfile")
        .body.innerHTML = Response
        GetPageTitle = .getElementsByTagName("title")(0).innerText
    End With

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function