Below I have listed my code. I am having a problem searching through multiple sheets in an excel workbook. As of now, it will load cboPcMark with only the first page of search results. It will also duplicate a couple of them. Can someone please take a look. Thanks in advance.:eek:
cboPcMark.Clear
Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet
Dim ExcelCutlist As String
Dim rng As Range
Dim firstAddress As String
Dim PcMark As String
'Dim objSheet As Excel.Sheets
ExcelCutlist = App.Path & "\" & "TANA EC 271 I.xls"
Set objExcel = CreateObject("excel.application")
Set objBook = objExcel.Workbooks.Open(ExcelCutlist)
'Set objSheet = objBook.Worksheets.Item(1)
objExcel.Application.Visible = True
For Each objSheet In objBook.Worksheets
Set rng = Nothing
With objSheet.Cells
Set rng = objSheet.Range("D:E").Find(What:=txtHeat.Text, lookin:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
cboPcMark.AddItem Range(rng.Address).Offset(0, -4)
Set rng = objSheet.FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> firstAddress
End If
End With
Next objSheet
Set objSheet = Nothing
If Not objBook Is Nothing Then objBook.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing