ERROR FOUND 0x80072ee7. The server name and address could not be resolved

[Rasmita Nayak]

Hi All,


I am new to coding and programming in excel vba. Actually I have 50 Stock Tickers in Column "C".I am trying to download data from the Yahoo Finance website using their API.


But when i run the code , an error is coming that:


Run-time error '-2147012889( 0x80072ee7)':
The server name and address could not be resolved

I am unable to solve that issue.
If any one know, how to fix the problem.
Please help.

Thanks in advance for any help you can provide me.


Private Sub stockData_Click()
Dim Worksheet As Worksheet
Set Worksheet = ActiveSheet
Dim LastRow As Integer
LastRow = Worksheet.Range("C100").End(xlUp).Row ' Find the last row used
If LastRow = 1 Then Exit Sub
Dim Symbols As String 'Stock Tickers
Dim i As Integer
For i = 3 To 60 'It Should Be 'LastRow' Instead Of 202 'Stock Tickers Starting In Row 3 To The Last row
Symbols = Symbols & Worksheet.Range("C" & i).Value & "+" 'Stock Tickers Starting In Column C
Next i
Symbols = Left(Symbols, Len(Symbols) - 1)
Dim URL As String
URL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snxd1vj1jkl1dr" ' The Yahoo Finance URL Used


Dim HTTP As New WinHttpRequest
HTTP.Open "GET", URL, False ' Download The CSV File
HTTP.Send


Dim Response As String
Response = HTTP.ResponseText
Dim Lines As Variant
Lines = Split(Response, vbNewLine)


Dim sLine As String
Dim Values As Variant
For i = 0 To UBound(Lines)
sLine = Lines(i)
If InStr(sLine, ",") > 0 Then
Values = Split(sLine, ",")
Worksheet.Cells(i + 3, 4).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0) ' Column D (Name)
Worksheet.Cells(i + 3, 5).Value = Replace(Values(UBound(Values) - 8), Chr(34), "") ' Column E (Exchange) - Removed " from the beginning & end
Worksheet.Cells(i + 3, 6).Value = Replace(Values(UBound(Values) - 7), Chr(34), "") ' Column F (Last Trading Date) - Removed " from the beginning & end
Worksheet.Cells(i + 3, 7).Value = Values(UBound(Values) - 6) ' Column G (Volume)
Worksheet.Cells(i + 3, 8).Value = Replace(Values(UBound(Values) - -5), "B", "") ' Column H (Market Cap) - Removed "B" from the end
Worksheet.Cells(i + 3, 9).Value = Values(UBound(Values) - 4) ' Column I (52 Week Low)
Worksheet.Cells(i + 3, 10).Value = Values(UBound(Values) - 3) ' Column J (52 Week High)
Worksheet.Cells(i + 3, 11).Value = Values(UBound(Values) - 2) ' Column K (Latest Price)
Worksheet.Cells(i + 3, 12).Value = Values(UBound(Values) - 1) ' Column L (Dividend/Share)
Worksheet.Cells(i + 3, 13).Value = Values(UBound(Values) - 0) ' Column M (P/E Ratio)
End If

Next i
End Sub