|
如题求助,跨工作薄查询并给出路径,谢谢大侠!
- Public Brr(), r&
- Sub main()
- Dim fp As String, Arr, i&, nm$, Myr&, j&, r1, r2, col%, d
- Dim Sht As Worksheet, sh As Worksheet
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- fp = ThisWorkbook.Path & ""
- Call searfile(fp, ".xls")
- Sheet1.Activate
- [b2:b1000].ClearContents
- Arr = [a1].CurrentRegion
- For i = 2 To UBound(Arr)
- d(Arr(i, 1)) = i
- Next
- k = d.keys: t = d.items
- For i = 1 To UBound(Brr, 2)
- If Brr(2, i) <> ThisWorkbook.Name Then
- With GetObject(Brr(1, i) & Brr(2, i))
- For j = 0 To UBound(k)
- Set r1 = .Sheets(1).[b:b].Find(k(j), , , 2)
- If Not r1 Is Nothing Then
- Cells(t(j), 2) = Brr(1, i) & Brr(2, i)
- d.Remove (k(j)): k = d.keys: t = d.items: Exit For
- End If
- Next
- .Close False
- End With
- End If
- Next
- Application.ScreenUpdating = True
- End Sub
- Sub searfile(fp As String, fkey As String)
- Dim Arr1() As String, i1 As Integer, i2 As Integer, fm
- If Right(fp, 1) <> "" Then fp = fp & ""
- If Len(fkey) < 1 Then fkey = ".xls" '文件类型省略则仅搜索.xls文件
- fm = Dir(fp, vbDirectory)
- Do While fm <> ""
- If fm <> "." And fm <> ".." Then
- If (GetAttr(fp & fm) And vbDirectory) = vbDirectory Then
- i1 = i1 + 1
- ReDim Preserve Arr1(1 To i1)
- Arr1(i1) = fp & fm
- End If
- If Right(fm, 4) = fkey Then '如果是07版本,红字改为5
- r = r + 1
- ReDim Preserve Brr(1 To 2, 1 To r)
- Brr(1, r) = fp
- Brr(2, r) = fm
- End If
- End If
- fm = Dir
- Loop
- For i2 = 1 To i1
- Call searfile(Arr1(i2), fkey)
- Next
- End Sub
复制代码
|
|