CODEWITHSUNDEEP
vba
user3900603
user3900603

Reputation: 69

#Value error on Winhttp.Winhttprequest in Excel VBA

I have written some code to retrieve url, but i am getting #Value error. Is anything wrong in this code,

Public Function Rurl(ByVal URL As String)
Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.Option(WinHttpRequestOption_UserAgentString) = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"
http.Option(WinHttpRequestOption_EnableRedirects) = True

If Not InStr(URL, "://") > 0 Then URL = "http://" & URL

http.Open "GET", URL
http.Send
Rurl = http.GetResponseHeader("Location")
Set http = Nothing
End function

Upvotes: 1

Views: 4161

Answers (2)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

trap for the 302 status code then get the Location variable from the http header.

strUrl = "https://xx123.abc.com/"

Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
With http
    .Open "GET", strUrl, False
    .setRequestHeader "Content-Type", "text/css" '"application/x-www-form-urlencoded"
    .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0"
    .Option(WinHttpRequestOption_EnableRedirects) = False


    .Send
    d = .waitForResponse()
    If (.Status = 302) Then
        temp = .responseText
        headers = .getAllResponseHeaders()
        cookie = .getResponseHeader("Set-Cookie")
        redirectedURL = .getResponseHeader("Location")
    End If
End With

Upvotes: 0

djikay
djikay

Reputation: 10628

You don't say where you're getting the error, so I'm going to assume it's at this line:

Rurl = http.GetResponseHeader("Location")

Something to ask yourself: what will your code do if the site at the supplied URL doesn't redirect?

The answer is that your code will give you an error at the above line which you don't handle anywhere in your code, very likely resulting in the #VALUE! error that you're seeing.

I'd suggest adding some error checking to ensure your function works in all situations. So, give this a go:

Public Function Rurl(ByVal URL As String)

  On Error GoTo ErrorHandler

  Dim http As Object
  Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
  http.Option(WinHttpRequestOption_UserAgentString) = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"
  http.Option(WinHttpRequestOption_EnableRedirects) = True

  If Not InStr(URL, "://") > 0 Then URL = "http://" & URL

  http.Open "GET", URL
  http.Send
  Rurl = http.GetResponseHeader("Location")
  Set http = Nothing

  Exit Function

ErrorHandler:
  Rurl = "" ' or you can say something like: "No redirection".
  Resume Next

End Function

If an error occurs anywhere in your function, the error handler will set the return value of your function to something sensible, clean up and exit the function. If no error occurs, everything should work like before. We're just adding a bit of code to handle potential errors.

Upvotes: 1

Related Questions