使用VBA事件中表单中的字符串从Access中搜索工作簿中的所有Excel工作表

Question

下面的代码从Access表单上的按钮事件打开Excel电子表格，并在命名的Worksheet上的表单控件中搜索字符串。我想调整此代码以搜索所有工作表。excel工作簿是从表单控件中提取的变量。

```
Private Sub Command132_Click()
On Error GoTo Err_Command132_Click
Dim filename As String
Dim searchstring As String
Dim xlApp As Excel.Application 'Excel object
Dim XlBook As Excel.Workbook 'Workbook object
Dim Xlsheet As Excel.Worksheet 'Worksheet object

Set xlApp = CreateObject("Excel.Application")
searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl
Set XlBook = xlApp.Workbooks.Open(filename)
xlApp.Visible = True
xlApp.ActiveWindow.WindowState = xlMaximized
Set Xlsheet = XlBook.Sheets("GroupMatrix")
With Xlsheet
.Cells.Find(What:=searchstring, After:=.Cells(1, 1), LookIn:=xlvalues, LookAt:=xlPart, 
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End With
Exit_Command132_Click:
Exit Sub
Err_Command132_Click:
MsgBox "Error " & Err.Number & "; " & Err.Description
Debug.Print "Error " & Err.Number & "; " & Err.Description
Resume Exit_Command132_Click
End Sub

I have tried using the code below to iterate through the worksheets. The search is run on the first worksheet and a value found, but subsequently an *Error 91; Object variable or With block variable not set* is generated. Can anyone help me with the iteration please? thanks 

For Each Xlsheet In XlBook.Worksheets
With Xlsheet
.Cells.Find(What:=searchstring, After:=.Cells(1, 1), LookIn:=xlvalues, LookAt:=xlPart, 
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End With
Next

Answer 1

您需要测试查找是否成功。

如果是，则退出循环(假设您想在找到searchstring之后停止；如果不是这样，则删除Exit For。(

For Each Xlsheet In XlBook.Worksheets
With Xlsheet
Dim foundCell as Range
Set foundCell = .Cells.Find(What:=searchstring, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not foundCell Is Nothing Then
.Activate
foundCell.Select
Exit For
End If
End With
Next