VBA 按行提取*.txt文件数据并写入Excel单元格

---

1. 测试用例

Drop : 37800.700000
Create : 26049.600000
Insert : 14446.400000
Insert : 16425.800000
Insert : 17029.200000
Insert : 38510.100000
Insert : 19219.200000
Insert : 16962.800000
Insert : 12910.100000
Insert : 12250.300000
Insert : 21846.400000
Insert : 17188.100000
Update : 30752.200000
Delete : 16287.100000
Select : 168256.600000
Batch : 100261.900000
Batch : 85628.500000
Batch : 169102.600000
DbData : 959430.000000
DbData : 1216097.200000

2. 测试代码

Sub 按钮1_Click()
    Dim a, b, k%, i%, j%, q%
    Dim str As String
    str = Dir(ThisWorkbook.Path & "\*.txt")
    Open ThisWorkbook.Path & "\" & str For Input As #1
        a = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
    Close #1
    k = UBound(a)
    For i = 0 To k
        b = Split(a(i), ":")
        q = UBound(b)
        For j = 0 To q
            Worksheets("cost time").Cells(i + 2, j + 1) = b(j)
        Next
    Next
End Sub

3. 实现效果