• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

excel macro/vb http get results in excel


via Excel (cell a1 ) I access a url, how in cell a2 can I view the http response code?

all help will do, would like to build this in a macro

  • 2
1 Solution
Please try this code,
Private Sub TestGetStatus()
    GetStatus "A1"
End Sub

Sub GetStatus(ByVal Ref As String)

    Dim HttpReq As Object
    Dim Rslt As Variant
    Set HttpReq = CreateObject("MSXML2.XMLHTTP")
    On Error Resume Next
    With HttpReq
        .Open "GET", ActiveSheet.Range(Ref).Value, False
        If Err Then
            Rslt = "No connection"
            Rslt = Val(.Status)
            If Err Then
                Rslt = "No Response"
                Rslt = "Status: " & Rslt
            End If
        End If
    End With
    ActiveSheet.Range(Ref).Offset(0, 1).Value = Rslt
End Sub

Open in new window

Enter your URL in column A. See the response in column B.
For testing, please use the procedure 'TestGetStatus'. Amend the referenced cell in column A as required.
rutgermonsAuthor Commented:
how can I get this to look at all rows in colum a?
This should do the job:-
Private Sub WriteStatus()

    Dim R As Long
    For R = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            GetStatus Cells(R, 1).Address
    Next R
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now