Run-time error '-2147467259 (80004005): automation error, unspecified error

[olivieros]

Hi all,

Some months ago I've written a code to go to a webpage, click on some buttons within the page (IE.Docs.ElementById), do this again with adjusting some filters, and copy paste this to an excel..

now, since last month It stopped working . It gets stuck at obj.IE.Document.getElementById("searchSolrButton").Click with the pop up: Run-time error "-2147467259 (80004005): automation error, unspecified error"

Does anybody here knows how to solve this? Many thanks!

'start a new subroutine called SearchBot
Sub SearchBot()

 
    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
    Dim y As Integer 'integer variable we'll use as a counter
    Dim result As String 'string variable that will hold our result link
 
    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer
 
    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True
 
    'navigate IE to this web page (a pretty neat search engine really)
    objIE.Navigate ("https://registers.esma.europa.eu/publication/searchRegister?core=esma_registers_bench_entities")
 
    'wait here a few seconds while the browser is busy
    While objIE.Busy
    DoEvents
Wend
    'click the 'go' button
    objIE.Document.getElementById("searchSolrButton").Click
    'wait again for the browser
   While objIE.Busy
    DoEvents
Wend

  'Set table size to 100 values
  objIE.Document.getElementById("tablePageSize").Click
  objIE.Document.getElementById("tablePageSize").selectedIndex = 3
  objIE.Document.getElementById("searchSolrButton").Click

    'the first search result will go in row 2
    y = 2
    
    'for each <a> element in the collection of objects with class of 'result__a'...
    For Each aEle In objIE.Document.getElementsByClassName("Tabular TabularScroll")
 
        '...get the href link and print it to the sheet in col C, row y
        result = aEle
        Sheets("ESMAoutput").Range("C" & y).Value = result
 
        '...get the text within the element and print it to the sheet in col D
        Sheets("ESMAoutput").Range("D" & y).Value = aEle.innerText
        Debug.Print aEle.innerText
  
        'increment our row counter, so the next result goes below
        y = y + 1
 
    'repeat times the # of ele's we have in the collection
    Next
 
    'close the browser
    objIE.Quit

MsgBox "ESMA register has been updated succesfully"
Sheets("Dashboard").Select

Exit Sub

 

[Anthony47]

Your wait cycle after a Navigate is weak, as it checks only for the Busy status: you should also check (a) for the ReadyState to say "Document complete". Also you should expect that IE say "Busy" with some delay (b), few milliseconds, after a navigation is started.
To cope with (a) and (b), your wait cycles should be modified as follows:

objIE.navigate ("https://registers.esma.europa.eu/publication/searchRegister?core=esma_registers_bench_entities")
 'wait here a few seconds while the browser is busy        'EXISTING
'>>> see a and b above
Application.Wait (Now + TimeValue("0:00:01")) 
While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE                    'use "<> 4" if working in late binding, ie no reference to the ieframe library
    DoEvents
Wend
'<< End of changes
'click the 'go' button                                             'EXISTING
objIE.document.getElementById("searchSolrButton").Click

You have several waits, you have to modify all of them in the same way

There is a final note: on pages that are based on javascript (ie 99% of the pages) you cannot be sure that Busy and ReadyState reflects the real status of the page. It means that in some cases you have to look to other conditions to be sure that the page is ready; for example that a field is now available, the number of rows in a table is now stable. In other words, these type of automation are very unstable...

Bye

 

[olivieros]

Your wait cycle after a Navigate is weak, as it checks only for the Busy status: you should also check (a) for the ReadyState to say "Document complete". Also you should expect that IE say "Busy" with some delay (b), few milliseconds, after a navigation is started.
To cope with (a) and (b), your wait cycles should be modified as follows:

objIE.navigate ("https://registers.esma.europa.eu/publication/searchRegister?core=esma_registers_bench_entities")
'wait here a few seconds while the browser is busy        'EXISTING
'>>> see a and b above
Application.Wait (Now + TimeValue("0:00:01"))
While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE                    'use "<> 4" if working in late binding, ie no reference to the ieframe library
    DoEvents
Wend
'<< End of changes
'click the 'go' button                                             'EXISTING
objIE.document.getElementById("searchSolrButton").Click

You have several waits, you have to modify all of them in the same way

There is a final note: on pages that are based on javascript (ie 99% of the pages) you cannot be sure that Busy and ReadyState reflects the real status of the page. It means that in some cases you have to look to other conditions to be sure that the page is ready; for example that a field is now available, the number of rows in a table is now stable. In other words, these type of automation are very unstable...

Bye

Hi Anthony,

Thanks for helping me and apologies for replying so late (busy times at this moment).

Unfortunately, when I insert your code it gives an errorcode again: "run-time error 462: the remote server machine does not exist or is unavailable."

when debugging it refers to the

While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE 'use "<> 4" if working in late binding, ie no reference to the ieframe library

I've tried <> 4 as well, same error code.

Do you know how to fix this?

thanks in advance!

 

[Anthony47]

Please show the complete macro.

Bye

 

[olivieros]

Please show the complete macro.

Bye

To give some background, i simply want to have all the fields given in the table, without filter. If the code wont work anymore for whatever reason I think ill have to try to download the list and copy it from there. However, I dont know how you can download the file/ open that specific file and give that file a variable, all fully automatic.

'start a new subroutine called SearchBot
Sub SearchBot()
 On Error GoTo Errormessage
 
    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
    Dim y As Integer 'integer variable we'll use as a counter
    Dim result As String 'string variable that will hold our result link
 
    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer
 
    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True
 
    'navigate IE to this web page (a pretty neat search engine really)
    objIE.Navigate ("https://registers.esma.europa.eu/publication/searchRegister?core=esma_registers_bench_entities")
    
     'wait here a few seconds while the browser is busy
     
    Application.Wait (Now + TimeValue("0:00:01"))
    While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE                    'use "<> 4" if working in late binding, ie no reference to the ieframe library
        DoEvents
    Wend

    'click the 'go' button
    objIE.Document.getElementById("searchSolrButton").Click
    
    'wait again for the browser
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

  'Set table size to 100 values
  objIE.Document.getElementById("tablePageSize").Click
  objIE.Document.getElementById("tablePageSize").selectedIndex = 3
  objIE.Document.getElementById("searchSolrButton").Click

    'the first search result will go in row 2
    y = 2
    
    'for each <a> element in the collection of objects with class of 'result__a'...
    For Each aEle In objIE.Document.getElementsByClassName("Tabular TabularScroll")
 
        '...get the href link and print it to the sheet in col C, row y
        result = aEle
        Sheets("ESMAoutput").Range("C" & y).Value = result
 
        '...get the text within the element and print it to the sheet in col D
        Sheets("ESMAoutput").Range("D" & y).Value = aEle.innerText
        Debug.Print aEle.innerText
  
        'increment our row counter, so the next result goes below
        y = y + 1
 
    'repeat times the # of ele's we have in the collection
    Next
 
    'close the browser
    objIE.Quit

MsgBox "ESMA register has been updated succesfully"
Sheets("Dashboard").Select

Exit Sub

Errormessage:

MsgBox "Unable to update the ESMA register. Please contact XXXX for further assistance"

 

[Anthony47]

I already anticipated you that

on pages that are based on javascript (ie 99% of the pages) you cannot be sure that Busy and ReadyState reflects the real status of the page. It means that in some cases you have to look to other conditions to be sure that the page is ready

Well, both your ".click" start a javascript, not a navigation (the items you click don't have a href tag attached).
It means that by using Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop you indeed don't wait even a microsecond; so try adding some wait after your clicks; for example:

    AnyObject.Click
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:03"))    'wait 3 secs'
    next instruction

Bye

 

[olivieros]

Hi,

I found out the main problem was the VPN connection of my work. Even the wait x seconds method didnt work. Thanks a lot for the support!