|
以提取微信银行的数据为例,代码如下:
- Sub 自动取数()
- Dim arr, i As Long, wb As Workbook, mFile As String, mRow As Long, mStr As String, mKeys, key
- Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
- mFile = ThisWorkbook.Path & "\\2020年考核_微信银行注册绑卡用户数表.xlsx"
- Set wb = GetObject(mFile)
- With wb
- With .Worksheets(1)
- mRow = .Range("J1000").End(3).Row
- arr = .Range("J5:W" & mRow).Value
- End With
- .Close False
- End With
- For i = 1 To UBound(arr)
- If Len(arr(i, 1)) > 0 And Not d.Exists(arr(i, 1)) Then d(arr(i, 1)) = Val(arr(i, 5))
- Next
- mKeys = d.keys
- With ThisWorkbook.Worksheets("Sheet1")
- .Activate
- mRow = .Range("A1000").End(3).Row
- arr = .Range("A5:W" & mRow).Value
- For i = 1 To UBound(arr)
- For Each key In mKeys
- If InStr(key, arr(i, 1)) Then arr(i, 7) = d(key)
- Next
- Next
- .Range("A5:W" & mRow).Value = arr
- End With
- Set d = Nothing
- End Sub
复制代码
|
|