|
Private Sub CommandButton1_Click()
Dim path$, myfile$, wkb As Workbook, ar(), i&, s$, sr$, sh As Worksheet
Dim br, m&, k&, n&
s = ThisWorkbook.Worksheets("面板").[B4]
path = ThisWorkbook.path & "\"
myfile = Dir(path & "*.xls*")
Application.ScreenUpdating = True
Do While myfile <> ""
If myfile <> ThisWorkbook.Name Then
Set wkb = GetObject(path & myfile)
With wkb
sr = Left(.Name, InStr(.Name, ".") - 1)
Set sh = .Sheets(1)
n = sh.Range("a" & sh.Rows.Count).End(xlUp).Row
br = sh.Range("a1:c" & n)
For m = 2 To UBound(br)
If br(m, 2) = s Or br(m, 3) = s Then
k = k + 1
ReDim Preserve ar(1 To 3, 1 To k)
ar(1, k) = sr
ar(2, k) = br(m, 2)
ar(3, k) = br(m, 3)
End If
Next
.Close False
End With
End If
myfile = Dir
Loop
Application.ScreenUpdating = True
If k > 0 Then
With ThisWorkbook.Worksheets("查询结果")
.Range("a2:c10000") = ""
.Range("a2").Resize(k, 3) = Application.Transpose(ar)
End With
MsgBox "你好,共查询到" & k & "条满足条件的记录", , "新年快乐"
Else
MsgBox "不存在您要查询的城市名称", vbExclamation, "请核实": Exit Sub
End If
Erase ar: Erase br
End Sub |
|