Automation error with  Set IE = CreateObject("InternetExplorer.Application")

pcalabria
pcalabria used Ask the Experts™
on
I'm running code in MS Access 360 on a Windows 7 machine to do website captures... The code was developed and originally run on an XP machine using IE 8.

The new machine is Windows 7 and uses IE 11.

When the code gets to the line:

 Set IE = CreateObject("InternetExplorer.Application")

I get the following error message:

Automation error
Unspecified error

In the MS Access references, I have selected Microsoft Internet Controls.  It is pointing at ieframe.dll in the SysWOW64 folder
I'm sure this code worked at one point on this machine.  I have no idea what changed.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
maybe unregister and reregister the .dll to see if somethimg else is going on
NorieAnalyst Assistant

Commented:
You shouldn't need a reference if you are using CreateObject.

Author

Commented:
I just have it dimensioned as a object.. is that ok?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NorieAnalyst Assistant

Commented:
That should be fine.

Try removing the reference to Internet Controls and running the code again.

If that doesn't work try setting the reference again and rather than using CreateObject try this.
Set IE = New InternetExplorer

Open in new window

Author

Commented:
I'll give those things a try.. but keep in mind.. it used to work on the same machine.

Author

Commented:
@John Register then re-register did not work.. if what you mean is check and then uncheck.

I unchecked...closed access.. then opened and rechecked.. no help.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, go to menu Debug and compile the project. If any error, correct and repeat until success.
Then save the module. and close the VB editor.

Now, go to menu (Database) Tools, and click (at left) Compact and Repair Database.

When done, try to run your code.
John TsioumprisSoftware & Systems Engineer

Commented:
unregister/register  means
C:\Windows\SysWOW64\regsvr32 /u ieframe.dll
C:\Windows\SysWOW64\regsvr32  ieframe.dll

Open in new window

If there is an issue with the .dll it will bomb out..

Author

Commented:
I've tried all of the suggestions offered but still get the same error.
I do have NEW information however.

The code always works correctly when the machine is rebooted.
The following line executes without error.

Set IE = CreateObject("InternetExplorer.Application")

The application is encountering an error and when the code is restarted, a second time, without rebooting the machine, the line above fails.
Perhaps the first object still exists???

Any ideas?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, that typically indicates that you don't get the object closed decently when your code has finished using it.

Author

Commented:
I'm still stuck... I tried adding the code:

 On Error Resume Next
 IE.Close
 Set IE = Nothing
 
 On Error GoTo Error_Routine

before Set IE = CreateObject("InternetExplorer.Application") but I get the same error.  The routine ran about 80 times before it failed.  Once it failed, I could not get it to start again without rebooting.

Any ideas would be appreciated!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You must also first kill any objects opened after the IE object was opened.

But you have the code. We have nothing.

Author

Commented:
I'm not sure what you mean by kill...
Use on error the ie.close?
Isn't that what I'm effectively doing?
NorieAnalyst Assistant
Commented:
There is no Close method for Internet Explorer, to close an instance of Internet Explorer you use Quit.
IE.Quit

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
As I wrote:

But you have the code. We have nothing.

That leave's with guessing only.

Author

Commented:
So my condo now looks like this:

On error resume next
IE.quit
Set IE=nothing

On error goto ErrorRoutine
Set IE=createobject("internetexplorer")

My hope is that if the operation fails this code will prevent the reported error without rebooting.

Does this seem correct!

Thanks
NorieAnalyst Assistant

Commented:
Can you post the whole code?

Author

Commented:
Public Sub ReadURLandSaveNew2(strURLtext As String, bolSuccess As Boolean, strWebsite As String, Optional strLocalFile As String, Optional SearchNumber As String, Optional bolAbort As Boolean)

'This routine was written with the help of Jim Detman of Experts Exchange
'The routine addresses allows us to capture data from the pccomponents website which requires that we first login
'To use the routine you must first go the the pccomponents website and log in using IE and our company password
'This routine allows us to accomplish the log in using IE
'The routine is only being used for the pccomponents website
'for some unknown reason at this time, the routine does not work with the other website we use that does not require a log in
'The original routing (created with help from Gustav Brock will continue to be used with websites which do not require that we first sign in (ReadURLandSaveNew)


    Dim i As Long
    Dim URL As String    
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim strURL As String
   
    On Error GoTo Error_Routine
   
    strURLtext = ""
    bolAbort = False
    bolSuccess = False
   
    DoEvents: If bSilentMode = False Then Forms!frmCaptureWorldInventory.txtStatus = "Entered ReadURLandSaveNew2 Subprogram"


    If strWebsite = "pccomponents" Then
        'if we are making a capture with pcComponents set the URL and Local file name to that of the pcComponents URL and file name
        strURL = "http://www.pccomponents.com/results.htm?r=1&t=f&src=&so=&lgc=equals&pn1=" & SearchNumber & "&slgc1=equals&QTY_MIN_FLT=0&fQTY_MIN_FLT=0"
        strLocalFile = stCaptureFilePath & "CaptureURLnet.htm"
    End If

    If strWebsite = "pcc" Then
        strURL = "http://www.pcc.com/avail?part=" & SearchNumber
        strLocalFile = stCaptureFilePath & "CaptureURLfc.htm"
    End If
   
'this code no longer works due to a change in the website code
    If strWebsite = "ConvertCurrency" Then
    'the searchnubmer field is used to parse into the URL
        strURL = "http://xe.com"
        strLocalFile = stCaptureFilePath & "CaptureCurrency.htm"
    End If


 On Error Resume Next
 IE.Quit
 Set IE = Nothing
 
 On Error GoTo Error_Routine
 
 
    'Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    'Set IE.Visible = True to make IE visible, or False for IE to run in the background
    IE.Visible = False
 
    'Define URL

     'URL = "https://www.pccomponents.com"
    URL = strURL
   
    'Navigate to URL
    IE.Navigate URL
 
    ' Statusbar let's user know website is loading
    ' Application.StatusBar = URL & amp; " is loading. Please wait..."
 
    ' Wait while IE loading...
    'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertently skipping over the second loop)
    Do While IE.ReadyState = 4: DoEvents: Loop   'Do While
    Do Until IE.ReadyState = 4: DoEvents: Loop   'Do Until
 
    'Webpage Loaded
    ' Application.StatusBar = URL & amp; " Loaded"
   
    'Save page
    strURLtext = IE.Document.DocumentElement.innerHTML
   
   
    'Verify we are still logged in when capturing from pcComponents
    If InStr(strLocalFile, "CaptureURLnet") > 0 And InStr(strURLtext, "demo mode") > 0 Then
        strErrorMessage = "Please sign-in to pcComponents using INTERNET EXPLORER and then try again"
        Forms!frmCaptureWorldInventory!txtStopMessage = strErrorMessage
        Forms!frmCaptureWorldInventory!txtOnOff = "Stopped"
        DoEvents
        MsgBox "You are not signed into pcComponents" & vbCrLf & vbCrLf & "Use the ""Capture Codes"" button to see the captured page (""WI Text"" or ""WI HTML"")"
        bolAbort = True
        GoTo Error_Routine
    End If



bolSuccess = True
DoEvents: If bSilentMode = False Then Forms!frmCaptureWorldInventory.txtStatus = "Exiting ReadURLandSaveNew2 Sub (no Errors) " & strLocalFile

Exit Sub

Error_Routine:

DoEvents: Forms!frmCaptureWorldInventory.txtStatus = "Error in ReadURLandSaveNew2 Sub: " & strErrorMessage
   
Exit Sub
   
   
    'Unload IE
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
   
    'MsgBox "done"
   
    End Sub

Author

Commented:
The above routine is called by executing this line of code:

Call ReadURLandSaveNew2(strText, bolURLSuccess, "pccomponents", strLocalFile, StripNumber(strPN))

The problem is that while this code may work for hundreds of different stPN's, sometimes it fails and when it fails I need to reboot the machine to clear the error.

My goal is to figure out why the code is failing, and prevent the failure.
NorieAnalyst Assistant

Commented:
Why are you handling, i.e. closing IE, when there is an error?

All the error routine in that code does is put a message in a textbox on a form, it does nothing with IE which means you could have 'ghost' instances floating about and that could be the root of the problem.

Author

Commented:
So what should my error routine do??

Ie.quit
Set IE=nothing

The real question is why is there an error in the first place. I don't expect any.
NorieAnalyst Assistant

Commented:
What you could is remove/comment out the  On Error.... stuff, that could be hiding errors.

Author

Commented:
Only place I use on error next is where I want to make sure the object is already closed before opening.. that's all new code.. if singles stepped through it
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, this won't work - or rather, it will always fail - as IE at this point always is Nothing:

On Error Resume Next
IE.Quit

Open in new window

Then, here you only could quit IE in case of an error, but - before that - you exit the sub:

    bolSuccess = True

Exit Sub

Error_Routine:

    Exit Sub
    
    'Unload IE
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
    
End Sub

Open in new window

Finally, if the two other objects were used (have you left out some code?), they must be killed in reverse order:

    Set IE = CreateObject("InternetExplorer.Application")
    Set objCollection = ' something
    Set objElement = ' something

    ' Do stuff.

    Set objElement = Nothing
    Set objCollection = Nothing
    Set IE = Nothing

Open in new window

Author

Commented:
Please excuse the typos in the above message. I've been responding from my cell phone.

I was trying to say that the "on error next" statement, and statements following it to quit IE and set the object to nothing, were only added as a troubleshooting test in attempt to fixed the problem.

The bottom line is that the routine that captures the text returned by the URL occasionally fails, and when it fails the machine must be rebooted.
NorieAnalyst Assistant

Commented:
How does it fail?

Is the URL not returning what's expected?

P.S. Did you arrive at this code after asking a question here previously?

If so it might help to post a link to the original question.

Author

Commented:
I am trying to figure out what makes the code fail but without success.

I trapped the last error and found nothing eventful.  It was a good url and a good page came up when the url was posted.

I did notice that it is adding an instance of IE every time the loop runs.. perhaps it's running out of memory?

The ReadURLandSAveNew routine is called each time I capture a page.  I thought I was setting the objects properly each time I exit the loop but apparently I'm missing something?

Do I need ie.quit in the exit loop?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I did notice that it is adding an instance of IE every time the loop runs.. perhaps it's running out of memory?

No, but it would eventually.

What you need is to not/never exit the sub before you have closed ID and then set it to Nothing.

Author

Commented:
The entire code is as entered above, except of course the line which calls the sub (which I also entered in a separate message).

Another EE member provided me with this code, which I may have modified for my purposes.  The original code included dim statements for objElement and objCollection... I don't know why these references were including.  I did add code to set these objects to nothing when I started to have problems... but I never had code to SET the objects to anything.

It does add separate instances of IE in the task manager... I've modified my code to set IE = nothing when an error occurs.;.. should I be using ie.quit first?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes:
What you need is to not/never exit the sub before you have closed IE and then set it to Nothing.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial