Run Time Error 80004005

[Steconnor]

Hi folks,

New to the forum, hopefully, I can tap into some of the knowledge here to help with run time I'm experiencing at the moment. I'm new to writing VBA, although I'm an experienced web developer in PHP, JS, HTML etc.

I'm currently working on a web bot that takes two variables from cells in the same row of a spreadsheet and uses the variables as search terms on a website. Then when the site returns the search result (a webpage), the bot will scrape certain info from the page and return it to cells adjacent to the search term variables on the spreasheet.

When I run the code using the play button in the VBA code editor in Excel, the code run momentarily (i have debug.print within the code at certain stages so i can monitor the progress in the immediate window) and then I get the Run-Time Error '2147467259 (80004005)' Automaton Error Unspecified Error.
If I step down through the code line by line using F8 is runs perfectly

Software Details
Windows 10
Excel 2013
VBA 7.1




Public Sub LoopColumn()

Dim myIE As Object
'Dim myIEDoc As Object

'Start Internet Explorer
Set myIE = CreateObject("InternetExplorer.Application")

'if you want to see the window set this to True
myIE.Visible = False

Dim n As Integer
n = 2

Dim c As Range

For Each c In Range("B2:B11")
If IsEmpty(c.Value) = False Then
Dim x As Range

Set x = Cells(n, 2)
Debug.Print x

Dim y As Range
Set y = Cells(n, 1)
Debug.Print y

myIE.Navigate "https://www.vesselfinder.com/vessels/" & y & "-IMO-" & x

'Print current url.
Debug.Print myIE.LocationURL

Dim IEDocument As HTMLDocument
Set IEDocument = myIE.document

Dim IETables As IHTMLElementCollection
Dim IETable As IHTMLTable
Dim IETable1 As IHTMLTable
Dim IETable2 As IHTMLTable

Set IETable = IEDocument.getElementsByTagName("table")(0)
Debug.Print Trim(IEDocument.getElementsByTagName("table")(0).Children(1).Children(0).Children(0).innerText)
Cells(n, 4) = Trim(IEDocument.getElementsByTagName("table")(0).Children(1).Children(0).Children(0).innerText)
Cells(n, 5) = Trim(IEDocument.getElementsByTagName("table")(0).Children(1).Children(0).Children(1).innerText)

Set IETable1 = IEDocument.getElementsByTagName("table")(1)
Debug.Print Trim(IEDocument.getElementsByTagName("table")(1).Children(1).Children(0).Children(0).textContent)
Cells(n, 3) = Trim(IEDocument.getElementsByTagName("table")(1).Children(1).Children(0).Children(0).textContent)

Set IETable2 = IEDocument.getElementsByTagName("table")(2)
Debug.Print IEDocument.getElementsByTagName("table")(2).Children(0).Children(9).Children(1).innerText
Cells(n, 6) = IEDocument.getElementsByTagName("table")(2).Children(0).Children(9).Children(1).innerText

End If

n = n + 1
Next



End Sub

[SamT]

I think the problem is that Excel VBA is too fast for IE + internet lag... Unless you are stepping thru the code manually.

In each of the IE sections of code, remove the Debug line and after each "Cells(n,?)=" line add a loop like

Do While Not IE.Ready
'Use a Wait Method here
Loop
You will need to code that yourself, since I don't speak IE at all.

I don't see anything else particularly wrong with that code, but I personally would make a couple of changes

Change
For Each c In Range("B2:B11")
If IsEmpty(c.Value) = False Then
Dim x As Range

Set x = Cells(n, 2)
Debug.Print x

Dim y As Range
Set y = Cells(n, 1)
Debug.Print y

To

On Error GoTo ErrHandler
For Each c In Range("B2:B11")
If Not IsEmpty(c) Then
Change every Cells(n, 1) Et al
To
Cells(c.Row, 1) Although I really prefer just c.Offset(0, number as needed)

Before Each Set IETable I would add, and increment

CodeSegment = 1

After the Next, Add


Exit Sub
ErrHandler:
MsgBox "Error with Sheet Row " & c.Row & ", value =" & c.Value & "and Code Segment " & CodeSegment
End Sub

Advanced VBA:
An object created from a Class module does not use CPU cycles while waiting. When timing is critical, I will create a hierarchy of Classes that "talk" to each other wherein the one that is "Active" tells a higher/Parent class when it is done.

[Steconnor]

Thanks SamT. Good to know I was on the right track with the code.

I put a wait method into the code, just a straight forward 5 second delay and it helped the code run smoothly from start to finish. Although it still trips itself up from time to time. IE is definitely the limiting factor. I'm looking the XMLHTTP GET method as a possible alternative to using IE. Will have to spend some time on it to fully understand it but it does look to be the best way to go.

[SamT]

Please keep us up dated in this thread how it goes.