Excel VBA, Http.ResponseText Runtime Error 91

Question

Using Excel 2010 with VBA 7.0

Code in this video works until I get to 10:01. When execute the line

Dim Resp As String: Resp = Http.ResponseText

I get a Runtime error 91 Object variable or With block variable not set.

Question: How do I resolve this error message?

Private Sub btnRefresh_Click()
 Dim W As Worksheet: Set W = ActiveSheet
 Dim Last As Integer: Last = W.Range("A100").End(xlUp).Row
 Dim Symbols As String
 Dim i As Integer
 Dim URL As String
 Dim Http As WinHttpRequest
 If Last = 1 Then Exit Sub

 For i = 2 To Last
   Symbols = Symbols & W.Range("A" & i).Value & "+"
 Next i

 Symbols = Left(Symbols, Len(Symbols) - 1)
 URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"
 Http = New WinHttpRequest
 Http.Open "GET", URL, False
 Http.Send

 Dim Resp As String: Resp = Http.ResponseText
  Debug.Print Symbols
 Debug.Print URL
End Sub

Andrew L · Accepted Answer · 2014-08-12 03:38:08Z

Two ways to solve this issue.

1) Set the reference to the Microsoft WinHttp 5.1 library (Tools->References toolbar in VBA) and fix the following lines of your code below:

Change

Dim Http As WinHttpRequest     'Incorrect
Dim Http As New WinHttpRequest 'Correct

Delete

Http = New WinHttpRequest      'Incorrect

2) You can also do this with a separate object which will avoid the need to set the library reference in VBA:

Private Sub btnRefresh_Click()
  Dim W As Worksheet: Set W = ActiveSheet
  Dim Last As Integer: Last = W.Range("A100").End(xlUp).Row
  Dim Symbols As String
  Dim i As Integer
  Dim URL As String

  If Last = 1 Then Exit Sub

  For i = 2 To Last
    Symbols = Symbols & W.Range("A" & i).Value & "+"
  Next i

  Symbols = Left(Symbols, Len(Symbols) - 1)

  URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"

  ' Create object here
  Dim HTTP As Object
  Set HTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  HTTP.Open "GET", URL
  HTTP.Send

  'Test Response
  MsgBox HTTP.ResponseText

End Sub

Collectives™ on Stack Overflow

Excel VBA, Http.ResponseText Runtime Error 91

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related