locked
How to test the invalid URLs in the excel sheet using VBA RRS feed

  • Question

  • Hi,

    I would like to mark the invalid URLs in the excel sheet as yellow.I have around 2000 URls listed in the column A of my excel sheet. I dont want to check the contents inside URl. I just want to check the URL is valid or not without sending server request so that Urls can be quickly checked without the performance issue.

    I just tried a logic as below but hanging the excel sheet very badly while executing.

    Function HttpExists(sURL As String) As Boolean
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "HEAD", sURL, False
    oXHTTP.send
    HttpExists = (oXHTTP.Status = 200)
    End Function

    Sub TestHLinkValidity()
    Dim rRng As Range
    Dim fsoFSO As Object
    Dim strFullPath As String

    Dim strPath As String
    Dim strFName As String
    Dim cCell As Range
    Dim response
    Dim intSlashCount As Integer

    Set fsoFSO = CreateObject("Scripting.FileSystemObject")
    Set rRng = Selection
    For Each cCell In rRng.Cells
        If cCell.Hyperlinks.Count > 0 Then
            strPath = GetHlinkAddr(cCell)
            If HttpExists(strPath) = False Then
                cCell.Interior.Color = 65535
            End If
       End If
    Next cCell
    End Sub

    Function GetHlinkAddr(rngHlinkCell As Range)
        GetHlinkAddr = rngHlinkCell.Hyperlinks(1).Address
    End Function


    Knowledge is power.


    Friday, November 28, 2014 10:03 AM

All replies

  • I just want to check the URL is valid or not without sending server request

    And how would you determine that www.gogle.com is not a valid URL then? IMHO impossible.

    Andreas.

    Friday, November 28, 2014 4:06 PM