VBA练习——逐行读取csv文件

Sub qry_Click()
Dim currentFileDirectory
currentFileDirectory = Application.ActiveWorkbook.Path

Dim queryDataRowCnt As Integer
'获取商户订单号
queryDataRowCnt = Application.Sheets(1).UsedRange.Rows.Count
Debug.Print "商户订单号条数为：" & queryDataRowCnt
Dim queryDatawechatRowIndex As Integer
queryDatawechatRowIndex = 2

Dim wechatFile
Dim fTextDir As String, wechatRowIndex As Integer, i As Integer
Dim paymentno As String
Dim wechatData As String
Dim resultCnt As Integer
resultCnt = 0


wechatFile = Dir(currentFileDirectory & "/*.csv")   '返回对应路径下第一个符合*.csv的文件名称

If wechatFile = "" Then
    MsgBox "请将微信商户号下载的订单文件放至目录" & currentFileDirectory
End If

Do While wechatFile <> ""          '当返回的文件名为空时停止循环

    wechatRowIndex = 1:
    fTextDir = currentFileDirectory & "\" & wechatFile ' csv文本路径
    Debug.Print fTextDir

    Open fTextDir For Input As #1 ' 导入文本
    Do While Not EOF(1) '逐行循环
        Line Input #1, currLine '取第一行，并赋值
        rowDataArr = Split(currLine, Chr(9))
        If wechatRowIndex > 5 Then

            wechatData = Right(rowDataArr(3), Len(rowDataArr(3)) - 1)
            'Debug.Print "wechatData-->" & wechatData
            '查询商户订单号是否匹配
            Do While queryDatawechatRowIndex <= queryDataRowCnt
            paymentno = Application.Sheets(1).Cells(queryDatawechatRowIndex, 2).Value
            'Debug.Print "queryValue->" & paymentno

            If resultCnt >= queryDataRowCnt Then
              GoTo LastLine
            End If

            If wechatData = paymentno Then
              Debug.Print "商户订单号" & paymentno & "对应的现金券抵扣金为" & rowDataArr(16)
              Application.Sheets(1).Cells(queryDatawechatRowIndex, 3).Value = rowDataArr(16)
              resultCnt = resultCnt + 1
            End If

            queryDatawechatRowIndex = queryDatawechatRowIndex + 1
            Loop

        End If
        queryDatawechatRowIndex = 2
        wechatRowIndex = wechatRowIndex + 1
    Loop
LastLine:
    Debug.Print "查询结束"
    Close #1

    wechatFile = Dir
    queryDatawechatRowIndex = 2

Loop
End Sub
Sub clear1_Click()
Dim currentFileDirectory
currentFileDirectory = Application.ActiveWorkbook.Path

Dim queryDataRowCnt As Integer
queryDataRowCnt = Application.Sheets(1).UsedRange.Rows.Count
For rowIndex = 2 To queryDataRowCnt
    Application.Sheets(1).Cells(rowIndex, 1).Value = ""
    Application.Sheets(1).Cells(rowIndex, 2).Value = ""
    Application.Sheets(1).Cells(rowIndex, 3).Value = ""
Next
End Sub