Automation of IE from Access

[Peter Danes]

I have a button on a form which opens an instance of IE. The details are
that I feed a magazine title to a Google search string to allow the user to
try to find an ISSN number for the magazine. The code is here.

Static IEGoogle As Object
....
If IEGoogle Is Nothing Then
Set IEGoogle = CreateObject("InternetExplorer.Application")
IEGoogle.Visible = True
End If
IEGoogle.Navigate2 "http://www.google.com/search?hl=cs&q=ISSN+"" & .......
etc.

The code works fine. I put in the Nothing test to make sure that I don't
create an new instance of IE for every search - constantly openning new
windows that the user has to keep closing is very annoying behavior for an
application, so I open it once and then re-use it for every subsequent call.

The problem is if the user DOES close the IE instance. This is entirely
possible, since the search simply opens a page of Google search results.
There is no further code to scan Google's results or anything like that. The
user must look through the offerings, possibly clicking on links to
investigate further, getting sidetracked and all the usual stuff that
happens when you start wandering around web pages. They can then easily
return to the Access app by closing the IE window. Subsequent calls then
crash, since my object variable is not Nothing, but the thing it was
pointing to has vanished. I get an error telling me that the called object
has disconnected from its clients. Is there a way to test the object
variable to detect whether its target is still alive?

--

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Douglas J Steele]

While it may seem to be a lot of overhead, you simply need to repeat your

If IEGoogle Is Nothing Then

check before each reference to IEGoogle.

 

[Peter Danes]

Thank you Doug, but that's exactly what I do. The code I pasted here runs
EVERY time the user presses the button, it's directly in the OnClick event
handler. The problem is that the test for Nothing fails - the object
variable is NOT set to Nothing, it is still trying to point to something,
but that something has disappeared.

 

[Douglas J Steele]

You may have to use the technique shown in
http://www.mvps.org/access/api/api0007.htm at "The Access Web" (coupled with
the information from http://www.mvps.org/access/api/api0013.htm to ensure
that you're getting the correct class name) to determine whether or not the
window's been closed.

At least, I think that'll work: I think each open instance of IE will have a
different class name, but I haven't tested.

 

[Peter Danes]

Thanks, I ran across one of those pages hunting for techniques before I
posted my question. They look awfully complicated and I don't want to
accidentally blow away a different instance of IE than the one I started
myself. The user would not be happy with me if I crashed into a an IE window
where he had his webmail open, for instance. What I'm looking for is
something like the IsConnected property of an Access CodeProject, that would
tell me whether the target of an object variable is still there or not. It's
possible that such a thing does not exist.

I've got it working now by trapping the error and resetting the object
variable to Nothing, then re-entering the code just above the Nothing test.
It then sees that IEGoogle is Nothing, because I just made it that way, so
it simply starts a new instance of IE and all is well. I don't like error
trapping in general, since it breaks the flow of code in sometimes
non-obvious ways. I prefer testing and acting on the results of tests,
rather than attempting an action, crashing and recovering. But sometimes
there isn't any other way - this may be one such.

A related question, tho'.. Once the instance of IE is running, calling it
again does not force it to be the "on top and active" window. Do you know of
any way to make that happen? It does come to the fore on the initial call,
when the instance is created, but not on subsequent calls.

 

[Ron Hinds]

I think the answer to all your problems (including bringing the window to
the foreground on subsequent calls) lies in the value of the HWND property
of your IEGoogle object. You should be storing that value in a Static
variable, too. Then you can make a couple of simple API calls to A)
determine if the window still exists and B) set that window to the
foreground.

A) Add these declarations and this call:

Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA"
(ByVal hwnd As Long, ByVal nIndex As Long) As Long

Public Const GWL_HWNDPARENT = (-8)

Static IEGoogleHwnd As Long

lRet = GetWindowLong(IEGoogleHwnd, GWL_HWNDPARENT)

Compare the returned value (if non-zero) to this value:

Application.hWndAccessApp

If it is the same, this is the window you created and it is still active. If
it isn't the same or the return value is zero, then your app's window
doesn't exist anymore. Set your object to Nothing and re-create it.

B) Add these declarations and this call:

Public Declare Function SetWindowPos Lib "user32" Alias "SetWindowPos"
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y
As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Public Const HWND_TOP = 0
Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1

Static IEGoogleHwnd As Long

Dim lRet As Long

lRet = SetWindowPos(IEGoogleHwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or
SWP_NOSIZE)

 

[Peter Danes]

Hello Ron,

Many thanks, that was what I needed. It took a small mod to make it work for
me, the call

GetWindowLong(IEGoogleHwnd, GWL_HWNDPARENT)

I changed to

GetWindowLong(IEGoogleHwnd, 0)

since calling with the PARENT parameter always returned zero. I don't
pretend to understand the intricacies of API calls, but my highly uneducated
and speculative guess is that the automation call spawns IE as an
independent process and so it doesn't really have a parent. I also don't
need to compare with Application.hWndAccessApp. All I do is check IEGoogle
for Nothing or GetWindowLong for zero - if either is true, create (or
re-create) the IEGoogle object. I hope that I'm not setting myself up for a
problem down the road, but as is now, it works great

Here's the code snippet.

{Declarations elsewhere}

If IEGoogle Is Nothing Or GetWindowLong(IEGoogleHwnd, 0) = 0 Then
Set IEGoogle = CreateObject("InternetExplorer.Application")
With IEGoogle
.Visible = True
IEGoogleHwnd = .hwnd
End With
End If
IEGoogle.Navigate2 "http://www.google.com/search?hl=cs&q=ISSN+"" _
& Replace$(txtTitulCasopisu, " ", "+") & "%22&btnG=Vyhledat+Googlem&lr="
SetWindowPos IEGoogleHwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE

I've beaten the heck out of it, opened and closed other IE windows,
navigated elsewhere in both the original and others, closed it after a
while, closed it immediately, closed it while the Google search was still
running, killed it with the task manager, closed and re-opened the calling
Access form - nothing bothers it. It keeps perfect track of that one IE
instance, always brings it to the front and when I kill it, simply opens a
new one with no fuss. A purist would probably tell me that I should retrieve
a value and check for errors in the last statement, since it is barely
possible that something could happen in the brief interval between setting
the variable and setting its target's position, but I wouldn't know what to
do about it anyway - either it works or it doesn't. I don't have the skills
necessary to investigate and manage arcane API errors with Access VBA

Thank you again.

 

[Ron Hinds]

No problem. Part A was untested but I knew it would return 0 if the window
didn't exist. Glad to hear it is working!