Adding CSV Formatted String to Excel Worksheet Using VBA

Question

Current Code:

' send http post request
objHTTP.Send (payload)

' response is a CSV formatted string
Dim response
response = objHTTP.responseText

If objHTTP.Status = "200" Then 'success
    ' add response to some worksheet
    ThisWorkbook.Names("some_cell").RefersToRange.Value = response
Else
    Debug.Print "Something Went Wrong!"
End If

Problem:

The entire csv string is being added to a single cell (not surprising since my named_range is a single cell), but I don't know what needs to be updated to get this working correctly.

Correct behavior would be adding the CSV string to the worksheet in the same manner as if excel was opening a .csv file.

Split the response and Resize the range to match the resulting array — chris neilsen
– chris neilsen, Commented Jan 4, 2019 at 20:48
Correctly parsing a multi-line (?) CSV string would depend on how it's formatted and for example whether any of the fields might contain commas. A simple approach would be to use Split(response, vbCrLf) to get an array of lines, dump those to the worksheet, then use Text To Columns to separate the fields. Or write the response to a temporary file and open it with Excel if you want to leverage Excel's ability to do the parsing. — Tim Williams
– Tim Williams, Commented Jan 4, 2019 at 20:48
Yes, the response is multi-line (number of lines always changing). Also, the number of columns may change. The csv is generated from a python backend using csv.writer (not sure what that means in terms of formatting, but figured it may help someone). Is it possible to create a temp file from my response, load that temp file into my worksheet, and then remove the temp file? — user2263572
– user2263572, Commented Jan 4, 2019 at 20:57
Yes - write out a temp file, open that in Excel, then copy the content to your named range and close the temp file. You can do all of that with ScreenUpdating off so no-one will notice... — Tim Williams
– Tim Williams, Commented Jan 4, 2019 at 21:01

Tim Williams · Accepted Answer · 2019-01-04 21:15:12Z

2

Something like this:

Sub Tester()
    LoadCsvToRange "A,B,C" & vbCrLf & "D,E,F", Sheet1.Range("e5")
End Sub

Sub LoadCsvToRange(csv As String, rngDest As Range)
    Dim f, wb
    'write to a temp file
    With CreateObject("scripting.filesystemobject")
        f = .GetSpecialFolder(2) & "\" & .GetTempName & ".csv"
        .CreateTextFile(f).write csv
    End With
    'open file in Excel and copy content
    With Workbooks.Open(f)
        .Sheets(1).UsedRange.Copy rngDest.Cells(1)
        .Close False
    End With
    Kill f 'remove temp file
End Sub

answered Jan 4, 2019 at 21:15

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding CSV Formatted String to Excel Worksheet Using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related