I have a particular Code that isn't working properly. The first time it loops through the instance of IE, it works beautifully. Each succession, not so.
It's meant for opening an instance of IE, filling out the needed form, and the page returns a table of a staggering number of cells, which must be cycled through until it finds a specific string of text within the cell:
Sub MFHLookup()
Dim objIE As Object
Dim objDoc As Object
Dim objTable As Object
Dim objCell As Object
Dim FormValue As String
Dim Anymore As Boolean
Dim Found As Boolean
Dim c
Do Until Anymore = True
FormValue = ActiveCell.Value
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = False
objIE.Navigate "http://(specific URL)"
Do While objIE.Busy: DoEvents: Loop
Do While objIE.readystate <> 4: DoEvents: Loop
With objIE
.document.getElementById("textbox").Focus
.document.getElementById("textbox").Value = FormValue
.document.getElementById("submitkey").Click
Do While .Busy: DoEvents: Loop
Do While .readystate <> 4: DoEvents: Loop
End With
Set objDoc = objIE.document
Set objTable = objDoc.getElementById("idTable")
c = 20
Set objCell = objTable.Cells(c)
' Here's the part that looks for the specific text in the HTML Table result
If Trim(objCell.InnerText) <> "Specific Text" Then
Do Until Found = True
c = c + 1
Set objCell = objTable.Cells(c)
If Trim(objCell.InnerText) <> "Specific Text" Then
Found = False
Else
Found = True
End If
Loop
Else
End If
' Once it catches this text in the cell, it then goes to the next cell after it_
and grabs the text to write to the Excel column/row
c = c + 1
Set objCell = objTable.Cells(c)
ActiveCell.Offset(0, 1).Value = Trim(objCell.InnerText)
objIE.Quit
Set objIE = Nothing
Set objDoc = Nothing
Set objTable = Nothing
Set objCell = Nothing
ActiveCell.Offset(1, 0).Select
Anymore = IsEmpty(ActiveCell.Value)
Loop
ActiveWorkbook.Save
End Sub
I 'think' for some reason it's not resetting c in the concurrent loop, but I thought that re-determining "c" as a set number before it goes searching again would have worked. Obviously, this is not going to find the specific text each time it gets to the table to get the next cell's text and return it to my sheet cell. Any ideas?
Bookmarks