VBA Macro For Already Open IE Window

[RL101]

Hi all,

I'm looking to create a VBA macro which will be ran from Excel (2003 or 2010) & which works with Internet Explorer. I have added 'Microsoft Internet Controls' to VBA References, and have looked at plenty of VBA code on the forum which opens & then navigates to specific websites.

However, what I need is for this macro to affect an already open IE window.

Does anyone have an idea of the syntax / command to have excel "select" a specific IE window already open?

Many thanks!

 

[ZVI]

Hi bkclaw113, Welcome aboard! By the way, the 7th greeting of new member in this thread!

CreateObject("Shell.Application") in XP involves IDispatch4 for creating ShellWindows object.
Not sure what is IDispatch5 for, but googling says it sometimes can be in Vista or in Win7.

On my PC code works fine in Win7 64bit with Excel 2003,2007 32 bit and with 2013 64 bit.
IDispatch5 was not found in the registry of my PC at all.

If code runs fine on the two computers then something is wrong on colleagues computer.
It is impossible to say exactly what is wrong without debugging the problem.
Thus - just guessing.

The differences between working and not working PCs can be in registry item HKEY_CLASSES_ROOT\CLSID\{13709620-C279-11CE-A49E-444553540000}.
Hope, you might compare and fix it.

Without registry fixing, in the code
instead of: CreateObject("Shell.Application").Windows
try one of these:
1. CreateObject("Shell.Application.1").Windows
2. GetObject("New:{13709620-C279-11CE-A49E-444553540000}").Windows

Let us know if ledge-hammer cracked nuts

Regards

 

[bkclaw113]

Thanks for the quick reply. I was able to test the non-registry suggestions that you provided and I am still experiencing the issue on the one computer. I will explore the registry settings and see if I can resolve the issue that way.

Again really appreciate the quick and detailed response.

 

[ZVI]

Thank you for the feedback!
Please try testing the late binding version of your code.
This code requires reference to: VBE – menu Tools – References - Microsoft Internet Controls

Sub Test()
  ActivateIeByTitle1 "VBA Macro For Already Open IE Window"
End Sub
 
' Reference required: Tools - References - Microsoft Internet Controls
Sub ActivateIeByTitle1(Title)
  Dim shellWins As ShellWindows
  Dim w As InternetExplorer
  Set shellWins = New ShellWindows
  For Each w In shellWins
    If w.Name = "Windows Internet Explorer" Then
      Debug.Print w.locationname
      If w.locationname Like (Title & "*") Then
        w.Visible = False
        w.Visible = True
        Debug.Print "IE Page located"
        GoTo exit_sub
      End If
    End If
  Next
  Debug.Print "IE Page Not located"
exit_sub:
  Set w = Nothing
End Sub

 

[bkclaw113]

At the step

[COLOR=#00008b]Set[/COLOR] shellWins = [COLOR=darkblue]New[/COLOR] ShellWindows

we are getting an error:

Run-time error '-2147024894 (80070002)':
Automation error
The system cannot find the file specified.

​

I compared the registry entries and he has version 1.1 where I have version 1.0. I am going to try to test on some more computers and see if the registry values hold true for when the code works verse not. Even if I do find a pattern I think I will have an up hill battle getting the desktop support folks to fix anything. As always thanks for the assistance.

 

[ZVI]

Then you set reference to Microsoft Internet Controls it may reference to ieframe.dll
Sometimes this can raise an issue, seems it somehow depends from IE installation/updating.
For this case try:
1. Uncheck reference: VBE – menu Tools – References - Microsoft Internet Controls and press OK to close References window
2. Open References window again and use Browse button to choose in folder Windows\System32 the file shdocvw.dll and press OK.
Reference to Microsoft Internet Controls should be set via shdocvw.dll
3. Run the code of post#23 to see if it works

 

[bkclaw113]

Unfortunately this suggestion did not help either. I have some changes to my program that make this process no longer critical so I am afraid that I am going to need to leave this unsolved and focus on other priorities for the time being. Thanks for all of your effort.

 

[ZVI]

Thank you for the back response, it's sad the problem was not solved.

But at least we know now that early binding is not working at all.
Therefore the culprit is neither Excel nor the code. Something is wrong with system DLLs or their registration.
Just for the case, I would recommend reinstalling/updating IE on that PC.

Happy to know you've found the way to minimize dependence of that part of code in your program.
Good luck!

 

[Jamez007]

Hi,

I am trying to use the code provided by ZVI with the following title "ZVI:2011-08-04 VBA Macro For Already Open IE Window". I am having an issues in Win 7, IE 9 when trying to access a local htm file with javascript on it. The javascript causes IE to popup an alert saying it is restricting the script from running etc. This messes .readyState property, which results in an endless while loop where the timeout condition is ignored . Google works, but not my local htm file because IE prevents the script from running, resulting in a warning "The remote server machine does not exist or is unavailable".

Can I use VBA to get around this error without changing the default IE protection settings that the user will need for every day usage?

Thanks,
Alex

 

[l_rosete22]

Hello,

I am new here and this post is really helping.

I am using a combinations of codes from this board. I need help honestly. Is any one want to help me!
These are my objectives:

I'm doing it in a Excel userForm

1. Login in a webpage. - Validates if username and password is correct. ELSE repeat login or renter username/password.

- Assuming login success - Manually close IE then Click Login button again.
2. The IE should open then inform user that he is already logged in.

-Assuming that the IE left idle and the page expired. there is a link appear "click here" to log off
Note: Need to log off and close IE to be able to login again.
3. Validate if page if expired then close IE then, Open IE and navigate to login page.

Anyone help me to code this? I am really in trouble.

Thank you in advance,

 

[ZVI]

I am using a combinations of codes from this board

Hi,

It seems that your request is out of the thematic of this thread, mentioned in the title.

Therefore it is better to create new thread with posting of your existing code and description of the problematic parts.
Please take into account that web pages are mostly unique and there are no universal rules & code for their control or parsing.
That is, VBA code will depend from the source code and the engine of the particular web page.
And access to that particular web page is required for the debugging.
So to give someone a chance to help you, please provide in the new thread as many details as possible.

Good luck!