vba split data but comma skip quotes

Question

By The following code I import data from a morningstar.com CSV file, the data split by commas. The problem that some of the data contain a comma.

For example, "XX, XXX". the result of this situation is:

cell1(X1,Y1)="XX       
cell(X1,Y2)=XXX"
instead of:
cell1(X1,Y1)=XX,XXX

My VBA

Sub GetKeyRatios()
Dim URL As String, csv As String, Lines, Values
Dim i As Long, j As Long, WinHttpReq As Object
Dim rngStart As Range

URL = "http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t=XNYS:JNJ&region=usa&culture=en-US&cur=USD&order=asc"

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", URL, False
WinHttpReq.send

csv = WinHttpReq.responseText

Lines = Split(csv, vbLf)

Set rngPaste = Sheets("KeyRatios").Range("A1")

For i = 0 To UBound(Lines)
    Values = Split(Lines(i), ",")
    For j = 0 To UBound(Values)
        rngPaste.Offset(i, j).Value = Values(j)
    Next j
Next i
End Sub

Is there any way to do this?

example

Answer 1

You can try this. I added some variables and corrected your declaration rngPaste (you declared rngStart).

Sub GetKeyRatios()
Dim URL As String, csv As String, Lines() As String
Dim Values() As String
Dim i As Long, j As Long, WinHttpReq As Object
Dim k As Integer, l As Integer 'Added to separate row and column numbering
Dim rngPaste As Range
Dim revenue                    'Added for concatenation of revenue values

URL = "http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t=XNYS:JNJ&region=usa&culture=en-US&cur=USD&order=asc"

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", URL, False
WinHttpReq.send

csv = WinHttpReq.responseText

Lines = Split(csv, vbLf)

Set rngPaste = Sheets("KeyRatios").Range("A1")

k = 0
For i = 0 To UBound(Lines)
    Values = Split(Lines(i), ",")
    l = 0
    If UBound(Values) > 0 Then
        For j = 0 To UBound(Values)
            If Values(j) = "" Then
                l = l + 1
            ElseIf Mid(Values(j), 1, 1) = Chr(34) Then
                revenue = Mid(Values(j), 2)
            ElseIf Mid(Values(j), Len(Values(j)), 1) = Chr(34) Then
                revenue = revenue & "," & Mid(Values(j), 1, Len(Values(j)) - 1)
                rngPaste.Offset(k, l).Value = revenue
                l = l + 1
            Else
                rngPaste.Offset(k, l).Value = Values(j)
                l = l + 1
            End If
        Next j
    ElseIf UBound(Values) = 0 Then
        rngPaste.Offset(k, l).Value = Values(0)
    End If
    k = k + 1
Next i
End Sub

Answer 2

You can try replacing all occurrences of "," (including the quotes) with a ;. I have also replaced all quotes in my code after the first operation. Then you can split by ; to get each number.

Sub m()
    Dim str As String

    'string to split
    ' Value in A10 is "53,234","45,568","99,999"
    'just an example
    str = Range("A10").Value ' in your case will be = Lines(i)

    'replace all occurences of  "," with ;
    str = Replace(str, """" & "," & """", ";")

    'replace any extra "
    str = Replace(str, """", "")

    MsgBox (str)

End Sub