VBA to check URL Status fails on certain websites with 404 even though website exists

Question

I have an Excel-list of some URLs I want to check regularly, if they still exist. In some cases I get a 404 error eventhough the website exists (but some images on it produce 404 errors. My code is as follows:

Sub Schaltfläche1_Klicken()
    Set sh = ThisWorkbook.Sheets("Tabelle1")
    Dim column_number: column_number = 2

    sh.Range("C2:D1000").Clear

    i = 2
    'Row starts from 2
    Do Until sh.Cells(i, column_number).Value = ""
        strURL = sh.Cells(i, column_number)
        sh.Cells(i, column_number + 1) = CallHTTPRequest(strURL)
        i = i + 1
    Loop

End Sub


Function CallHTTPRequest(strURL)
    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    objXMLHTTP.Open "GET", strURL, False
    objXMLHTTP.Send
    Status = objXMLHTTP.Status

    Set objXMLHTTP = Nothing
    CallHTTPRequest = Status
End Function

The problematic URL is http://www.ifz-berlin.de/#/rescue

Any help is appreciated on how I could solve this issue.

If you only want to check for 404 then using HEAD in place of GET is better. Have you tried monitoring the problem HTTP call using a tool like Fiddler ? That should give you some clues. — Tim Williams, Feb 21 '18 at 16:10
Thanks Ryan, I need to check for that specific sub-site. So that's not an option — Jan, Feb 22 '18 at 06:08
hi Tim, I checked this. it works fine with http://www.ifz-berlin.de/index.html but http://www.ifz-berlin.de/index.html#/rescue fails on VBA but works fine in any browser. The issue is that the # is needed but gets converted in VBA to %23. I'm currently searching a method to prevent this — Jan, Feb 22 '18 at 07:12

score 0 · Accepted Answer · answered Feb 22 '18 at 07:27

This answer provides the necsearry information for my solution. The URL has to be split. The # and everything beyond has to be transferred via send() like so:

Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")
objXMLHTTP.Open "GET", "http://www.ifz-berlin.de/index.html", False
objXMLHTTP.Send ("#/rescue")
Status = objXMLHTTP.Status

score -1 · Answer 2 · edited Jun 22 '18 at 15:52

testing website if exists -->answer (404 or else,..) thanks to Jan & ....for function above

adapted to test

'------------------------------------------------------------
'   check if website exits
'   sub part testet --> ok  22.6.18
'------------------------------------------------------------
Dim reWebsite, txtTmp As Variant
Set IE = CreateObject("InternetExplorer.Application")
reWebsite = CallHTTPRequest(Urls)
If reWebsite = 404 Then
    Debug.Print " website not existing --> Fehler "; reWebsite _
    & vbLf & " Sheet Name: "; wsTmp
    Debug.Print "sheet -->( "; ws.name; " ) NOT updated"
    GoTo jump1
Else
Debug.Print "sheet -->( "; ws.name; " ) exits"
End If

VBA to check URL Status fails on certain websites with 404 even though website exists

2 Answers2