How do I read CRLF delimited lines from MSXML.ResponseText in VBA/Excel

Question

Most of the examples I see with MSXML have to to with Javascript or JQuery, but I'm writing an Excel 2010 macro that doesn't use either.

My goal is to download a file (as shown below), and parse a medium sized (5 to 15MB) CSV file. I ultimately want to save the CSV data in a hidden data tab.

I made a little progress with this CSV VBA sample here but I don't know how to glue the output of MSXML.ResponstText with that sample.

Here is my VBA/Macro code

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
'objHttp.SetRequestHeader "Content-Type", "text/csv"
'objHttp.SetRequestHeader "charset", "gb2312"
Call objHttp.Open("GET", fileURL, False)
Call objHttp.Send("")
'Call MsgBox(objHttp.ResponseText)

How do I get excel to work with ResponseText and only read one line at a time?

@JP I'm downloading a CSV file [similar to this one](http://nfp.blob.core.windows.net/exchange/DatabaseWeeklyStats.csv?se=2011-12-12T05%3A50%3A27Z&sr=b&si=readonly&sig=pk2CqlboFdSYQApAQiKOC9%2Bpds5qqRjo2JRCQJi%2Fdbg%3D) - though please excuse the wonky / misaligned data — makerofthings7, Dec 06 '11 at 04:13
Instead I would use the URLDownloadToFile API to save the file locally, then simply open it using Excel's native methods (see @Jean-François Corbett's answer). — JimmyPena, Dec 06 '11 at 11:59

score 1 · Answer 1 · answered Dec 06 '11 at 04:37

1

Dim opener As New FileSystemObject
Dim fContainer

Set fContainer = opener.OpenTextFile("c:\DatabaseWeeklyStats.csv")

Do Until fContainer.AtEndOfStream
    sText = fContainer.ReadLine
    Debug.Print sText
Loop

' This requires reference to Microsoft Scripting Runtime

answered Dec 06 '11 at 04:37

Romeo

1,093
11
17

This helps, but does it work using ServerXMLHTTP.ResponseText or a URL as the argument for OpenTextFile? I suppose I'll try that tomorrow. – makerofthings7 Dec 06 '11 at 04:45
1

`opener`?! `fContainer`?! Weird variable naming! Still, +1 – Jean-François Corbett Dec 06 '11 at 08:21

Jean-François Corbett · Accepted Answer · 2011-12-06T08:58:23.913

I say, don't mix things. First download the CSV file, then read it.

From your question it isn't obvious what your goal is. If you want to parse the file, then you can read and parse it line by line like this using native VBA statements:

Dim filePath As String
Dim fn As Integer
Dim myLine As String
Dim myParsedLine() As String

filePath = "C:\DatabaseWeeklyStats.csv"

fn = FreeFile()
Open filePath For Input As #fn
Do Until EOF(fn)
    Line Input #fn, myLine
    myParsedLine = Split(myLine, ",")
    ' Line is now parsed. Do stuff.
Loop

If you just want to stick the entire CSV file in a new sheet in your workbook without necessarily "parsing" it (i.e. interpreting its contents) beforehand, then you can do this:

Dim dbSheet As Worksheet
Dim targetSheet As Worksheet

Workbooks.Open Filename:="C:\DatabaseWeeklyStats.csv", _
    Format:=2 ' use comma delimiters
Set dbSheet = ActiveSheet

Set targetSheet = Workbooks("Book1").Sheets(3) ' wherever you want to move it to

dbSheet.Move After:=targetSheet
' dbSheet is now in your workbook.

' Hide it.
Set dbSheet = ActiveSheet
dbSheet.Visible = xlSheetHidden

+1 you could also include code that uses the URLDownloadToFile API to grab the CSV file, that would make this answer complete. — JimmyPena, Dec 06 '11 at 11:58

How do I read CRLF delimited lines from MSXML.ResponseText in VBA/Excel

2 Answers2