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!

 

[Excel_Phil]

Hello Guys,

Im kinda new to VBA. Im trying to get codes from posts #6 and #16 to work, but I cant. Where do I need to type my URLs? Or in the case of window title, I type it in: "VBA Macro For Already Open IE Window - Page 2" but it wont work. Am I getting the title page wrong? Am I not typing it in somewhere in the function or in the code? I have the function and the subs in the same module, it is something wrong with that?

In references, I have added Microsoft Internet Controls and I have no "missing anything." I just cant understand how to get the code to work.

Thanks in advance!!

 

[ZVI]

Hi Phil, welcome to MrExcel!

Well, lets's test the finding of IE window by its title.

Do as follows:

1. Select and copy all the below code to the clipboard.
2. In VBE use menu Insert - Module to create Module1
3. Paste from the clipboard the code of the function GetIeByTitle to that module
4. Open IE on this page http://www.mrexcel.com/forum/excel-...pplications-macro-already-open-ie-window.html
5. Run the Test_GetIeByTitle - the IE window have to be found. Message will inform about success.
6. Close that IE window and run again Test_GetIeByTitle - IE window shall not be found, error message will appear.

' ZVI:2013-09-10 Get IE object of already open window by its title
' Arguments:
'   Title     -  title of the searchied IE window
'   [IsLike]  -  False/True = exact/partial searching, default is False
'   [IsFocus] -  False/True = don't_activate/activate IE window, default is False
Function GetIeByTitle(Title, Optional IsLike As Boolean, Optional IsFocus As Boolean) As Object
  Dim w As Object
  For Each w In CreateObject("Shell.Application").Windows
    With w
      If .Name = "Windows Internet Explorer" Then
        If IsLike Then
          If InStr(1, .LocationName, Title, vbTextCompare) > 0 Then   ' ZVI:2013-09-10 fixed
            ' Partial title of window is found - activate IE window
            If IsFocus Then
              w.Visible = False
              w.Visible = True
            End If
            Set GetIeByTitle = w
            Exit For
          End If
        Else
          If StrComp(.LocationName & " - " & .Name, Title, 1) = 0 Then  ' ZVI:2013-09-10 fixed
            ' Title of window is found - activate IE window
            If IsFocus Then
              w.Visible = False
              w.Visible = True
            End If
            Set GetIeByTitle = w
            Exit For
          End If
        End If
      End If
    End With
  Next
  Set w = Nothing
End Function
 
' Testing subroutine for the above function GetIeByTitle.
'
' 1. Try this macro with already open IE window on this page:
'    http://www.mrexcel.com/forum/excel-questions/553580-visual-basic-applications-macro-already-open-ie-window.html
'    Macro should successfuly find the IE window by its Title.
'
' 2. Close that IE window and try the code again - IE window will not be found.
'    Error message will appear.
'
Sub Test_GetIeByTitle()
  Dim IE As Object
  Dim Title As String
  Title = "VBA Macro For Already Open IE Window"
  Set IE = GetIeByTitle(Title, True, True)
  If IE Is Nothing Then
    MsgBox "IE window with this Title is not found:" & vbLf & """" & Title & """", 48
  Else
    MsgBox "Well done!" & vbLf & Title, 64
  End If
End Sub

 

[Excel_Phil]

Hey ZVI, thanks for the quick reply. I wont work. I've tried it in two different computers... I really have no clue whats missing, I followed the steps, over and over. I do get the msgbox "IE window not found." I couldnt get it to work to get "Well Done". What kinda of bug can it be?? - I appreciate a lot any help!!

 

[ZVI]

What is your Operating System, version of Excel and version of IE?
Excel Home does not support CreateObject, for example.
Hope you manually ran IE to the mentioned URL, not the another type of internet explorer like Opera.

Not sure but there can be issue of IE protection settings (post#28).

Try Debugging VBA Code.
Use F8 for step by step evaluation, code should go into the lines If IsLike Then and If InStr(1,... .
At debugging see what is in w.LocationName (type in Immediate window ?w.LocationName and press Enter) - there should be something like is in Title variable

 

[suresh_m]

Wanted to thank you for your post. It came in very helpful today.

Just wanted to know is it possible to input data from excel in a webpage and submit the buttion and return to excel for multipule times.

Thank you

 

[mariod1049]

Hi & Welcome to the Board!
Try this:

' ZVI:2011-05-30 VBA Macro For Already Open IE Window
' Reference required: Tools - References - Microsoft Internet Controls
Sub GetIE()
  
  Dim shellWins As ShellWindows
  Dim IE As InternetExplorer

  Set shellWins = New ShellWindows

  If shellWins.Count > 0 Then
    ' Get IE
    Set IE = shellWins.Item(0)
  Else
    ' Create IE
    Set IE = New InternetExplorer
    IE.Visible = True
  End If

  IE.Navigate "http://support.microsoft.com/kb/q176792/"

  Set shellWins = Nothing
  Set IE = Nothing

End Sub

Regards,

I'm trying to convert this code to a function where I pass it two variables. However, the IE.Navigate line does not recognize the variables I pass to it. For example, passing partnum and cagecode variables like this does not work: IE.Navigate "http://www.dlis.dla.mil/webflis/pub/pub_search.aspx?part=" & partnum & "&cage=" & cagecode" Can anyone tell me how this can be done?

 

[ZVI]

this does not work: IE.Navigate "http://www.dlis.dla.mil/webflis/pub/pub_search.aspx?part=" & partnum & "&cage=" & cagecode

Hi,
1. Are you sure your question is about of Already Open IE Window? If not please start new thread.
2. What is the correct link in that example being typed manually?
Regards

 

[mariod1049]

Hi,
1. Are you sure your question is about of Already Open IE Window? If not please start new thread.
2. What is the correct link in that example being typed manually?
Regards

I think this is the right thread. Since I will be using the function multiple times in one session, I want to keep using the same open IE window (and tab, if possible) rather than launching IE for each iteration. Here is how I revised the code. (For testing purposes, you can use partnumber = B2A and cagecode = 71744 and pass them as variables from a sub.)

' ZVI:2011-05-30 VBA Macro For Already Open IE Window
' Reference required: Tools - References - Microsoft Internet Controls
Function GetIE(partnum As String, cagecode As String)
  
  Dim shellWins As ShellWindows
  Dim IE As InternetExplorer
 
  Set shellWins = New ShellWindows
  If shellWins.Count > 0 Then
    ' Get IE
    Set IE = shellWins.Item(0)
  Else
    ' Create IE
    Set IE = New InternetExplorer
    IE.Visible = True
  End If
  IE.Navigate "http://www.dlis.dla.mil/webflis/pub/pub_search.aspx?part=" & partnum & "&cage=" & cagecode"
  Set IE = Nothing
  Set shellWins = Nothing
End Function

 

[ZVI]

Well, I see your revising of the code is good!

Just delete an extra double quotes symbol at the end of the code line with IE.Navigate
was: IE.Navigate "http://www.dlis.dla.mil/webflis/pub/pub_search.aspx?part=" & partnum & "&cage=" & cagecode"
should be: IE.Navigate "http://www.dlis.dla.mil/webflis/pub/pub_search.aspx?part=" & partnum & "&cage=" & cagecode
Then it works:

Sub Test()
  GetIE "B2A", "71744"
End Sub

And welcome to the MrExcel!

 

[mariod1049]

I found my own error! I had an extra quote at the end of the URL that shouldn't have been there. Thank you anyway, Mr. Excel!

I think this is the right thread. Since I will be using the function multiple times in one session, I want to keep using the same open IE window (and tab, if possible) rather than launching IE for each iteration. Here is how I revised the code. (For testing purposes, you can use partnumber = B2A and cagecode = 71744 and pass them as variables from a sub.)