|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 导入数据()
Application.ScreenUpdating = False
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Dim f$, Wb As Workbook
Dim aa As String
Dim ar As Variant, br As Variant
Dim arr()
aa = InputBox("请输入文件名:", "导入数据", 36)
If aa = "" Then MsgBox "您没有输入文件名!": End
p = ThisWorkbook.Path
f = Dir(p & "\" & aa & ".xls*")
If f = "" Then MsgBox "没有找到文件名为“" & aa & "”文件。": End
With ActiveSheet
r = .Cells(Rows.Count, 2).End(xlUp).Row
If r > 2 Then
ar = .Range("a2:h" & r)
For i = 2 To UBound(ar)
If ar(i, 2) <> "" And ar(i, 3) <> "" Then
zd = ar(i, 2) & "|" & ar(i, 3) & "|" & ar(i, 7) & "|" & ar(i, 8)
d(zd) = i
End If
Next i
End If
Set Wb = Workbooks.Open(p & "\" & f)
With Wb.Worksheets(1)
rs = .Cells(Rows.Count, 2).End(xlUp).Row
br = .Range("a2:h" & rs)
End With
Wb.Close False
ReDim arr(1 To UBound(br), 1 To UBound(br, 2))
For i = 2 To UBound(br)
If br(i, 2) <> "" And br(i, 3) <> "" Then
zd = br(i, 2) & "|" & br(i, 3) & "|" & br(i, 7) & "|" & br(i, 8)
If Not d.exists(zd) Then
n = n + 1
For j = 1 To UBound(br, 2)
arr(n, j) = br(i, j)
Next j
End If
End If
Next i
If n = "" Then MsgBox "没有需要更新的数据!": End
.Cells(r + 1, 1).Resize(n, UBound(arr, 2)) = arr
End With
Application.ScreenUpdating = True
MsgBox "本次更新了" & n & "行数据!"
End Sub |
|