convert ADODB binary stream to string vba

Question

I have the following problem: I have a CSV file which is stored on a server but it has 3 characters as delimiters: "[|]". I would like to load the data from the URL and fill the data in the columns of my Excel page using the [|] as delimiter. Until now I found code to load the file from a website using an ADODB recordset but I cannot get any further:

 myURL = "http://www.example.com/file.csv"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1 'binary type
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "E:\file.csv", 2 ' 1 = no overwrite, 2 = overwrite   
oStream.Close
End If

This works fine to save a file directly. But I do not want to save it to a file, I want to enter the data in the proper cells. Is there any way to do this? I would prefer not tu use Internet Explorer objects

Answer 1

Tested OK with a regular csv file:

Sub Tester()
    Dim myURL As String, txt As String, arrLines, arrVals
    Dim l As Long, v As Long, WinHttpReq As Object
    Dim rngStart As Range

    myURL = "http://www.mywebsite.com/file.csv"

    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False, "username", "password"
    WinHttpReq.send

    txt = WinHttpReq.responseText

    'might need to adjust vbLf >> vbCrLf or vbCr
    '  depending on the file origin (Win/Unix/Mac)
    arrLines = Split(txt, vbLf)

    Set rngStart = ActiveSheet.Range("A1")

    For l = 0 To UBound(arrLines)
        arrVals = Split(arrLines(l), "[|]")
        For v = 0 To UBound(arrVals)
            rngStart.Offset(l, v).Value = arrVals(v)
        Next v
    Next l

End Sub