excel搜索指定文字列

excel搜索指定文字列

'Input1：要搜索的目录
'Input2：目标字符串
Debug.Print[イミディエイトウィンドウ]里显示 (または Ctrl + G で表示させます)
Private Sub CommandButton1_Click()
Dim strFolder As String
Dim strFile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim strSearch As String

'指定搜索目录
strFolder = ActiveSheet.Cells(3, 3).Value
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder + "\"
End If
'指定要搜索的文字列
strSearch = ActiveSheet.Cells(5, 3).Value

'搜索目录下的所有Excel文件
strFile = Dir(strFolder & "*.xlsx")

Do While strFile <> ""
Set wb = Workbooks.Open(strFolder & strFile)
For Each ws In wb.Worksheets
'在每个工作表搜索文字列
Set rng = ws.Cells.Find(strSearch, LookIn:=xlValues, lookat:=xlPart)
If Not rng Is Nothing Then
'找到文字列，输出文件名和单元格地址
Debug.Print strFile & " : " & ws.Name & " - " & rng.Address
End If
Next ws
wb.Close Savechanges:=False
strFile = Dir
Loop

MsgBox "OK"
End Sub

tool画面

immediate