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
VBA练习——逐行读取csv文件
最新推荐文章于 2024-07-29 10:53:14 发布