VBA拆分数据但逗号跳过引号 [英] vba split data but comma skip quotes

问题描述

通过以下代码，我从morningstar.com CSV文件导入数据，该数据按逗号分隔。一些数据包含逗号的问题。

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.

例如 XX，XXX。
这种情况​​的结果是：

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

我的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

有什么办法吗？

示例

example

推荐答案

您可以尝试一下。我添加了一些变量并更正了您的声明 rngPaste （您声明了 rngStart ）。

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

这篇关于VBA拆分数据但逗号跳过引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！