WinHttpRequest

Question

Can someone help me with this. It all works but i'd like to be able to read file line by line and put into my workbook. This returns everything headers and data all in one string.

Public Sub subGetWebCsvFileData()

Dim WinHttpReq As WinHttp.WinHTTPRequest

Set WinHttpReq = New WinHTTPRequest

Dim intFileReturned As String

WinHttpReq.Open "GET", "http://www.zacks.com/screening_2/custom/export.php?rptid=2itmx6L9&sid=87639"

WinHttpReq.Send

intFileReturned = WinHttpReq.ResponseText

MsgBox intFileReturned

End Sub

Answer 1

Public Sub subGetWebCsvFileData()

  Dim WinHttpReq As WinHttp.WinHTTPRequest

  Dim Temp, Data, Lines

  Dim I As Long, J As Long

  Set WinHttpReq = New WinHTTPRequest

  Dim intFileReturned As String

  WinHttpReq.Open "GET", "http://www.zacks.com/screening_2/custom/export.php?rptid=2itmx6L9&sid=87639"

  WinHttpReq.Send

  intFileReturned = WinHttpReq.ResponseText

  'This side generates a LF at the end of each line

  Temp = Split(intFileReturned, vbLf)

  'Make a variant array same size

  ReDim Data(0 To UBound(Temp))

  'Visit each line

  For I = 0 To UBound(Temp)

    'Split the CSV-data

    Data(I) = Split(Temp(I), ",")

    'Count number of columns

    If UBound(Data(I)) > J Then J = UBound(Data(I))

  Next

  'Rebuild to 2D-array, it's faster than write each single cell

  ReDim Lines(1 To UBound(Temp) + 1, 1 To J + 1)

  For I = 0 To UBound(Temp)

    For J = 0 To UBound(Data(I))

      Lines(I + 1, J + 1) = Data(I)(J)

    Next

  Next

  'Store the array

  Range("A1").Resize(UBound(Lines), UBound(Lines, 2)) = Lines

End Sub

Answer 2

For some reason it seemed to work today. I googled somethings on the issue and found that sometimes when a server is restarted the WinHTTPRequest will not work. Guess its one of those things that happen. WinHTTPRequest is probably 10 times faster then a web query, at least for this website. Thanks for the spilt code to break up the string. works like a charm!!

Answer 3

Thanks,

Im sure it would work but now i think thta the site has block me from getting the file. Either installed a router or block it thru the firewall.

Thanks anyway