vba 将文本中的数据解析为excel列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15228387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing data in text into excel columns
提问by SamSong
I want to parse the below portion of the text file such that the incremental numbers under "DVA" are placed into a column as a range of cells that I determine. I would like to do the same for the serial numbers starting with S in the second column, also in a range that I determine. There is a |delimeter between them, and I would like DAU SNo.-C0to be the string that I search for.
我想解析文本文件的以下部分，以便将“DVA”下的增量数字作为我确定的单元格范围放入列中。我想对第二列中以 S 开头的序列号执行相同的操作，也在我确定的范围内。|它们之间有一个分隔符，我想DAU SNo.-C0成为我搜索的字符串。
I am thinking: search for DAU SNo.-C0, then somehow skip two lines and copy what is underneath into Excel, parsing the data into two columns and determining the range of cells it should be placed in.
我在想：搜索DAU SNo.-C0，然后以某种方式跳过两行并将下面的内容复制到 Excel 中，将数据解析为两列并确定应放置的单元格范围。
DAU SNo.-C0
+-------------------------+
|DVA| 0 |
+-------------------------+
| 0 | S1050360701270 |
| 1 | S1050344701369 |
| 2 | S1050360701315 |
| 3 | S1021360701337 |
| 4 | S1050360701367 |
| 5 | S1050332701350 |
| 6 | S1050360701584 |
+-------------------------+
This is what I have: UPDATED
这就是我所拥有的：更新
Do While i < UBound(strAll)
If (InStr(1, strAll(i), "DAU SNo.-C0", vbTextCompare) > 0) Then
i = i + 4 'Skip 4 lines to get to first SN
Do Until InStr(1, strAll(i), "+", vbTextCompare) > 0 'Loop until line includes "+"
strSNO = Split(strAll(i), "|", -1, vbTextCompare)
'put strSNO into next cell in column A
i = i + 1
Loop
End If
i = i + 1
Loop
Next
I have tried several ways of putting the split string into the next cell in the column but I can't get it to work. Everything words except the actual copying the value (where the comment is in the code). I verified that the rest works by printing the strSNO(1) and strSNO(2).
我已经尝试了几种将拆分字符串放入列中的下一个单元格的方法，但我无法让它工作。除了实际复制值（注释在代码中）之外的所有单词。我通过打印 strSNO(1) 和 strSNO(2) 验证了其余部分是否有效。
Any direction, explanation or code would be helpful.
任何方向、解释或代码都会有所帮助。
采纳答案by MattCrum
When you've found the line, use the VBA Splitfunction, using |as a delimiter. It splits a string into an array.
找到该行后，使用 VBASplit函数|作为分隔符。它将一个字符串拆分成一个数组。
Example: if you do this:
示例：如果你这样做：
strTest = Split(">| 0 | S1050360701270", "|", -1, vbTextCompare)
it will come out like strTest(1) = 0, strTest(2) = S1050360701270.
它会出来strTest(1) = 0，strTest(2) = S1050360701270。
You may also want to use strAll = Split(objReadFile.ReadAll, vbCrLf, -1, vbTextCompare)to bring the whole text file into VBA first (it's sometimes quicker), then using an integer variable to work through each line - it means you can skip a few lines when you find that "DAU SNo.-C0" string, rather than having to read each one.
您可能还想strAll = Split(objReadFile.ReadAll, vbCrLf, -1, vbTextCompare)先将整个文本文件导入 VBA（有时会更快），然后使用整数变量来处理每一行 - 这意味着当您发现“DAU SNo.-C0 " 字符串，而不必阅读每一个。
======EDIT======
======编辑======
Try replacing the do loop in your code with something like this:
尝试用以下内容替换代码中的 do 循环：
Dim strAll() As String, strSNO() As String, i As Integer
strAll = Split(objReadFile.ReadAll, vbCrLf, -1, vbTextCompare) 'Gets each line from file
i = LBound(strAll)
Do While i < UBound(strAll)
If (InStr(1, strAll(i), "DAU SNo.-C0", vbTextCompare) > 0) Then
i = i + 4 'Skip 4 lines to get to first | 0 | S1050360701270 | line
Do Until InStr(1, strAll(i), "+", vbTextCompare) > 0 'Loop until line includes "+"
strSNO = Split(strAll(i), "|", -1, vbTextCompare)
'This will add them to the next available row in A / B... change as needed
Range("A60000").End(xlUp).Offset(1, 0).Value = Trim(strSNO(1))
Range("B60000").End(xlUp).Offset(1, 0).Value = Trim(strSNO(2))
i = i + 1
Loop
End If
i = i + 1
Loop
