Excel URL Validation for secured HTTP authenticated Website

[Avinesh]

Hi I was trying to validate a secured website using the URL in the following format https://username:password@host. When I run the site in browser it login and load the page automatically. But if I use =CheckURL(A1) in excel it says "The URL is invalid". Can someone help me with this please

This is the VBA code I use for validation

Public Function CheckURL(url As String)
    Dim request As Object
    Set request = CreateObject("Winhttp.WinhttpRequest.5.1")
    On Error GoTo haveError
    With request
    .Open "HEAD", url
    .Send
    CheckURL = .Status & ": " & .statustext
    End With
    Exit Function
haveError:
    CheckURL = Err.Number & ": " & Err.Description
    End Function

 

[Kyle123]

I suspect (though haven't checked), that to do it that way, you'll need to pass the "Basic Authentication" credentials in the Authorization header - rather than the url.

To check is, does this work? - in case it's not obvious, you'll need to extract the username and password

Public Function CheckURL(url As String)
    Dim request As Object
    Set request = CreateObject("MSXML.XMLHTTP")
    On Error GoTo haveError
    With request
    .Open "HEAD", url, false, "username", "password"
    .Send
    CheckURL = .Status & ": " & .statustext
    End With
    Exit Function
haveError:
    CheckURL = Err.Number & ": " & Err.Description
    End Function

 

[Avinesh]

Hi Kyle123, Thank you so much for your quick response. When I add the code and tried =CheckURL(A1) it say "#VALUE!" and not the status code

 

[Kyle123]

You need to step through it, not just enter it as a formula

 

[Avinesh]

You need to step through it, not just enter it as a formula

This is my first time using VBA and I have no idea on this. Can you explain in detail or share some article/video if possible. Thank you so much Kyle.