Check Status Code of all urls in column

[chilly_bang]

Hi

I have here a VBA macro which checks a server status of an URL, placed in a cell (here C2). How could this macro be adjusted to test all urls in the given column?

The code is:

Private Changing As Boolean

Private Sub RedirectChecker(ByVal url As String)
    Dim sh As Worksheet
    Set sh = ActiveSheet
    
    Dim http As New WinHttp.WinHttpRequest
    http.Option(WinHttpRequestOption_UserAgentString) = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"
    http.Option(WinHttpRequestOption_EnableRedirects) = False
    
    '' Clear existing info
    sh.Cells(3, 3).Formula = ""
    sh.Cells(4, 3).Formula = ""
    DoEvents
    
    '' Add protocol if missing
    If (InStr(url, "://") = 0) Then
        url = "http://" & url
    End If
    
    '' Launch the HTTP request
    http.Open "GET", url
    If Err.Number <> 0 Then
        '' Handle URL formatting errors
        sh.Cells(3, 3).Formula = Trim(Err.Description)
        Exit Sub
    End If
    http.Send
    If Err.Number <> 0 Then
        '' Handle HTTP errors
        sh.Cells(3, 3).Formula = Trim(Err.Description)
        Exit Sub
    End If
    '' Show HTTP response info
    sh.Cells(3, 3).Formula = http.Status & " " & http.StatusText
    sh.Cells(4, 3).Formula = http.GetResponseHeader("Location")
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Changing Then Exit Sub
    Changing = True
    Dim Name As String
    On Error Resume Next
    Name = Target.Name.Name
    If Name = "URL" Then
        RedirectChecker Target.Value
    End If
    On Error GoTo 0
    Changing = False
End Sub

 

[JLGWhiz]

You have two macros. One of which is a worksheet_change event macro that calls the other macro and supplies the URL string to the second macro. You could make a list of the urls you want to check and use a loop in the worksheet_change macro to pass the different urls to the web query macro.