CODEWITHSUNDEEP
excelvba
zundarz
zundarz

Reputation: 1594

Excel VBA, Http.ResponseText Runtime Error 91

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

Upvotes: 2

Views: 2160

Answers (1)

Andrew L
Andrew L

Reputation: 1224

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

Upvotes: 2

Related Questions