none
I have 3,000 web addresses, URL's in Excel 2010 that I need to check and see if they exists

    Question

  • I have never made a macro or anything in visual basic.

    I have 3,000 logo links for a client in excel 2010 listed in a column in the form of: www.example.com. There is no http entered before the logo links/web addresses.

    I need to check each link to see if they work. If they work I will see an image of the logo in my web browser. If there is no logo I get the error "The resource you are looking for has been removed, had its name changed, or is temporarily unavailable." in my web browser. 

    Is there a way for me to have this done automatically so that I can find out if the link is good or not? I need to save time and will be using it again in the future. If so, I would need very specific, simple instructions since I am not familiar with macros/VBA.

    Thank you.

    Wednesday, December 12, 2012 7:12 PM

Answers

  • I just tried this; seems to work fine:

    Function HttpExists(sURL As String) As String
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")

    If Not UCase(sURL) Like "HTTP:*" Then
    sURL = "http://" & sURL
    End If

    On Error GoTo haveError
    oXHTTP.Open "HEAD", sURL, False
    oXHTTP.send
    HttpExists = IIf(oXHTTP.Status = 200, "OK", "Not OK")
    Exit Function
    haveError:
    HttpExists = "Not OK"
    End Function

    Call the function like this, in Cell B1:

    =HttpExists(A1)

    This is in Cell A1

    www.google.com


    Ryan Shuell

    Thursday, December 13, 2012 12:21 AM