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!

 

[rwhitmore90]

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

ZVI, I'm not sure you are still checking this thread but I have a few questions for you. I got this to work very well to check whether the title of the document matches. I have a question:

1) Can I get this to work declaring Dim IE as InternetExplorer? This will be in the sub I build. The reason I need this code to get the active IE session is because the URL to the internal web application that I'm trying to automate never changes. It is a series of javascript functions that eventually get to a form I am trying to automate. Unfortunately for me, the id tags of the web page also change.

I really appreciate your help. This thread and some of your other posts have been extremely helpful.

 

[ZVI]

Hi rwhitmore90,

Glad the code of this thread is helpful for you.

To declare Dim IE As InternetExplorer the reference to Microsoft Internet Controls is required in VBE-Tools-References.
It is so called early binding.

Your code then may look like this:

Sub Test_GetIeByTitle()
  Dim IE As InternetExplorer
  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

Best Regards

 

[bryanliang]

Hi ZVI,
May I know how to use the getelementbyid after the macro was active the web page which ady open?I would like to fill up the form in that webpage.tq..

 

[ZVI]

Hi ZVI,
May I know how to use the getelementbyid after the macro was active the web page which ady open?

Has not seen this message earlier...
But better late than never

Getting of IE.Document.GetElementById is as usual.
For example, open this web page in IE and try this code:

Sub Test_GetIeByTitle()
  Dim IE As Object, x
  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 IE.Document.GetElementById("pagetitle").InnerText, , "pagetitle"
    'MsgBox "Well done!" & vbLf & Title, 64
  End If
End Sub

 

[sugaprasad]

Hi ZVI,

I am trying to replace the existing URL in the address bar of already opened IE window. So far this is my code.

Sub Replace_URL()

  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 "javascript: $find('ReportViewerControl').exportReport('EXCEL');"








  Set shellWins = Nothing
  Set ie = Nothing


End Sub

Here its goes in to else part. But the navigate link is opening in a new tab. but i want this to replace the url in existing tab.
Is this can be done. Please help me.

 

[jackson89]

Need some help!!!! i've been trying to code a macro using twebst. But the problem with twebst is that it doesn't write a code when a link is clicked that open the webpage in a new window. Now how do i navigate my code which as been written for the login page to a new page. I want to download the data from the new page unto a excel sheet. Below is the code that was generated.
Sub OpenTwebstMacro()
Dim core As ICore
Set core = New OpenTwebstLib.core

Dim browser As IBrowser
Set browser = core.StartBrowser("http:xxxxx/login.aspx")

Call browser.FindElement("input text", "id=UserName").InputText("xxxx")
Call browser.FindElement("input password", "id=Password").InputText("xxxx")
Call browser.FindElement("input submit", "id=LoginButton").Click
Call browser.FindElement("a", "id=ctl00_TreeView1t5").Click
Call browser.FindElement("select", "id=ctl00_ContentPlaceHolder1_ddlAccyear").Select("2014-2015")
Call browser.FindElement("input submit", "id=ctl00_ContentPlaceHolder1_btnCurrentEnrollment").Click ' here the new window opens up
' I want to navigate to the new webpage that has been opened start extracting my required data

Sheets("Run").Select


End Sub

 

[Mepwnz2010]

This thread is pretty awesome. Thank you for the help ZVI, but now I have my own question. I of course am new to VBA. I've taken the script from thread and formed it with another script to check if all cells have been filled out by the user. Next I need to click a button and ultimately I will need to fill in some boxes, drop boxes, and of course click more boxes. I literally started messing with VBA only a week ago so I'm stuck. If I could just get past clicking this first button I know I could figure out the rest. I cant provide a link to the button I need to click cause its for work, but I will paste the script for the button and my macro script. Thanks in advance if I get any help, and if I don't thanks anyways just for this thread.

---------------------------------------------My macro------------------------------------------------------

' 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
                         '---------everything above here sets up rules for the macro---------------------

Sub CreateSFcase()
If IsEmpty(Range("a1")) Then
   MsgBox "No. of Bookings is blank" & """", 48
   Stop
End If

If IsEmpty(Range("a2")) Then
   MsgBox "Dispute Amount is blank" & """", 48
   Stop
End If

If IsEmpty(Range("a3")) Then
   MsgBox "Transaction Post Date is blank" & """", 48
   Stop
End If

If IsEmpty(Range("a4")) Then
   MsgBox "Accounting Assigned To is blank" & """", 48
Stop
End If

If IsEmpty(Range("a5")) Then
   MsgBox "Contact/Account Lookup Results is blank" & """", 48
   Stop
End If

If IsEmpty(Range("a6")) Then
   MsgBox "Contact/Account Lookup Results is blank" & """", 48
   Stop
End If

If IsEmpty(Range("a7")) Then
   MsgBox "Additional To is blank" & """", 48
   Stop
End If

'-------------------------------------Everything from to the above green line checks to make sure macro is completely filled out before running the marco---------------------------------------------------------


  Dim IE As Object
  Dim Title As String
  Title = "New Case: Select Case Record Type ~ Salesforce.com - Unlimited Edition"
  Set IE = GetIeByTitle(Title, True, True)
  If IE Is Nothing Then
    MsgBox "Please open the correct Salesforce window" & vbLf & """" & Title & """", 48
    Stop
     End If
'---------------------------------this pulls up SF---------------------------------------------


End Sub

------------------------------------And heres the button---------------------------------------

'<td class="pbTitle">...</td>                 'this looks like its just the bar the botton is on'<td class="pbButtonb" id="bottomButtonRow">  'this looks like its just the bar the botton is on
  '<input value="Continue" class="btn" title="Continue" name="save" type="submit">   'this is the botton

 

[ZVI]

But the navigate link is opening in a new tab. but i want this to replace the url in existing tab.
Is this can be done

As a workaround - read new URL, close the created extra tab and navigate old tab to that URL.

 

[ZVI]

i've been trying to code a macro using twebst.

Have no Twebst framework installed on my PC. Try asking Twebst Support

 

[ZVI]

If I could just get past clicking this first button I know I could figure out the rest.

Clicking button on web page is a bit out of the scope of this thread - better to create the new thread for this.
Commonly code of each web page is almost unique - there are no strict rules for the parsing.
Sometimes code for clicking button on web-page can look like these (depends on HTML code of web site):
IE.Document.GetElementsById("bottomButtonRow").Item(0).InvokeMember("submit")
or
IE.Document.Forms.GetElementsByName("save").Item(0).InvokeMember("submit")
or
IE.Document.GetElementsByName("save").Item(0).Click
or
IE.Document.GetElementsById("bottomButtonRow").Item(0).Click
...