I am using Excel VBA code to click a button on our website. I know this isn't the best of things to be doing, but it is the least objectionable option available to me.
I can using this code, successfully load imdb.com, google, etc. But when I load our local site, I lose control of the ie object, I can't check readyState, I can't Quit.
Here is the error I get.
Run-time error '-2147023179 (800706b5)':
Automation error
The interface is unknown
Every so often I instead get this message:
Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.
Clicking Debug indicates the ie.readyState
, I commented that out and then it points to ie.Quit
Sub dothestuff()
Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate "http://www.google.com/"
anerror = webload(ie)
ie.Quit
Set ie = Nothing
End Sub
Function webload(ie)
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
End Function
Step 1) Open an Excel-based Macro and access the developer option of excel. Step 2) Select Visual Basic option under Developer ribbon. Step 3) Insert a new module. Step 4) Initialize a new subroutine and name it as test2.
To do so, open Internet Explorer and in tools->Internet options , click on Content tab and then under AutoComplete, click on Settings and check box for forms. If you fill up a form once, it will be recorded and next time you could just click on textbox to fill it up.
Here is a quick and easy solution for this issue:
Instead of:
set IE = createobject("internetexplorer.application")
Use:
Set IE = New InternetExplorerMedium
No need to tweak the IE settings
Here's what's happening. When your browser internally "jumps" to a different security zone - from say a local address to an inTRAnet address or to an inTERnet address - IE closes/drops the current process of IE and opens another one with tighter security. The old and new IE processes are IE child processes to the parent IE task so you don't see it happen either by watching the browser or watching the process in Task Manager. If you use Process Explorer (free from Microsoft), you can see this happen.
What you need to do in this type of environment is use Anup Upadhyay's solution above. His code snippet looks at all IE tasks (parent and child doesn't make a difference) and finds the new IE process that is pointing to the web address that the original process was given. As long as you don't have multiple browsers open to the same address, it will find the new process reliably and carry on like you would expect it to.
Note: You might also need to use the InternetExplorerMedium object as opposed to the InternetExplorer object. It is also better at "holding on" to a session.
Here's my version which is almost the same thing as Anup's.
References for MS Office VBA:
Microsoft Internet Controls
Microsoft Shell Controls and Automation
n
Dim IE As InternetExplorerMedium ' This object (the "medium" variety as opposed to "InternetExplorer") is necessary in our security climate
Dim targetURL As String
Dim webContent As String
Dim sh
Dim eachIE
targetURL = "[your URL here]"
Set IE = New InternetExplorerMedium
IE.Visible = False ' Set to true to watch what's happening
IE.Navigate targetURL
While IE.Busy
DoEvents
Wend
Do
Set sh = New Shell32.Shell
For Each eachIE In sh.Windows
If InStr(1, eachIE.LocationURL, targetURL) Then
Set IE = eachIE
'IE.Visible = False 'This is here because in some environments, the new process defaults to Visible.
Exit Do
End If
Next eachIE
Loop
Set eachIE = Nothing
Set sh = Nothing
While IE.Busy ' The new process may still be busy even after you find it
DoEvents
Wend
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With