Checking Web Page(s) within a table.

[Madlock]

I'm having trouble trying to accomplish something specific, and I'd greatly appreciate any guidance available. I'm very "functionally proficient' in Excel, but am by NO means an advanced user or programmer.

I have a list of web page URL's in a spreadsheet column. For example, A1:A100.

I'd like to be able to tell whether or not a particular web page exists at each of the URL's in column A, with some kind of result appearing in each corresponding cell of Column B. I can be something as straightforward as a TRUE/FASLE, or even an ERROR/non ERROR would suffice.

Is there a way for a relative novice to accomplish this?

Thanks in advance for any guidance.

[Richard Buttrey]

Create a Module Level object in the VBE and add the following and then run the macro "CheckURLExists"

Sub CheckURLExists()
    Dim rCell As Range, stURL As String

    For Each rCell In ActiveSheet.Range("A1:A100")
        stURL = Trim(rCell.Value)
        If Len(stURL) > 0 Then
            If Not LCase(stURL) Like "http*" Then stURL = "http://" & stURL
            rCell.Offset(0, 1).Value = IIf(URLExists(stURL), "True", "False")
        End If
    Next rCell

End Sub

Function URLExists(stURL As String) As Boolean
    Dim oURL As Object
    Set oURL = CreateObject("MSXML2.XMLHTTP")
    oURL.Open "HEAD", stURL, False
    On Error Resume Next
    oURL.send
    URLExists = (oURL.Status = 200)
End Function

HTH

[TMS]

@Richard Buttrey: this looks really useful. Please could you step through the function and explain what you are doing. Thanks

[Madlock]

Wow! That's absolutely terrific. Thank you for your elegant solution. Even *I* was able to put it in place. Could you please help me understand the meaning of the 200 status?

This most certainly works, but apparently the test I was hoping to validate ultimately isn't based upon the presence of absence of a web page - as each URL ultimately returns a PDF file (or not). I though the "existence test" would work because when setting a cell's hyperlink property to the URL and then clicking on it, Excel would either open the PDF in Internet Explorer or present a message box that it was unable to open the URL.

Thank you again for your expertise.

[Madlock]

Richard,

I've stepped through the code - and, much like Spanish, although I can't speak a word of it, I think I understand what's happening.

Might I trouble you to perhaps help me complete the last step of the puzzle?

What I'm essentially trying to do is query a list of URLs to determine whether or not corresponding data exists. Think of it in terms of a football team with player uniform numbers 1 through 20.

I can query each player's information by a URL derived from his uniform number. I had presumed if no player wore #5, there'd be no web page about him. I was wrong. When I query uniform #5, I will indeed get the full bio page if there is such a player, but I will receive a default "no such player exists" web page if there's not.

Using the Array and Looping structure you've provided, is there any way to quickly determine which page I'm seeing (i.e. a valid bio page - or a "no such player page")?

I'm sorry if I'm inarticulate or this seems arcane. I greatly appreciate your help.

Thank you.