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