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 & Welcome to the Board!
Try this:

' ZVI:2011-05-30 http://www.mrexcel.com/forum/showthread.php?t=553580
' 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,

 

[ZVI]

The same, but with more correct code lines order for memory releasing at the end of subroutine:

' ZVI:2011-05-30 http://www.mrexcel.com/forum/showthread.php?t=553580
' 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 IE = Nothing
  Set shellWins = Nothing

End Sub

 

[ZVI]

Late binding version, reference to Microsoft Internet Controls is not required:

Sub GetIE_LateBinding()
  
  Dim IE As Object
  
  With CreateObject("Shell.Application").Windows
    
    If .Count > 0 Then
      ' Get IE
      Set IE = .Item(0) ' or .Item(.Count - 1)
    Else
      ' Create IE
      Set IE = CreateObject("InternetExplorer.Application")
      IE.Visible = True
    End If
  
    IE.Navigate "http://support.microsoft.com/kb/q176792/"
  
    Set IE = Nothing
  
  End With
  
End Sub

P.S. Window of any MS Explorer can be catched, not only of MS Internet Explorer.

 

[nbraybrook]

Hello, new to the forums! I am trying to use this code to either open explorer to the page I want or if the user already has that page open in IE I want it to just bring it forward. Problem I am having is while this macro will open IE to the page, if say, I already have that page open it is just opening a new tab of the same page every time I click it.

 

[ZVI]

Hello, new to the forums! I am trying to use this code to either open explorer to the page I want or if the user already has that page open in IE I want it to just bring it forward. Problem I am having is while this macro will open IE to the page, if say, I already have that page open it is just opening a new tab of the same page every time I click it.

Hi & Welcome to the Board!
Try this code:

Function NavigateTo(Link As String, Optional WaitSeconds = 5) As Long
' ZVI:2011-08-04 http://www.mrexcel.com/forum/showthread.php?t=553580
' Navigate (Internet)Explorer to the Link with [WaitSeconds=5] timeout.
' If (Internet)Explorer is already navigated to the Link then it's just activated,
' else the new (Internet)Explorer is navigated to the Link.
' Returns  error number (zero at success)
' Note: comment "Application.StatusBar" lines for VB & VBScript compatibility

  Dim i As Long, t As Single, Url As String, w As Object, wUrl As String
  
  ' Uniform Link string
  Url = Trim(Replace(Replace(Link, "%20", " "), "\", "/"))
  ' Find "://" prefix
  i = InStr(Url, "://")
  If i > 1 And i < 7 Then Url = Mid(Url, i + 3)
  ' Delete "/" at the end
  If Right(Url, 1) = "/" Then Url = Left(Url, Len(Url) - 1)
    
  ' Seach Link in IE windows
  Application.StatusBar = "Finding link: " & Link & " ..."
  For Each w In CreateObject("Shell.Application").Windows
    ' Uniform LocationURL string
    wUrl = Trim(Replace(Replace(w.LocationURL, "%20", " "), "\", "/"))
    ' Find "://" prefix
    i = InStr(wUrl, "://")
    If i > 1 And i < 7 Then wUrl = Mid(wUrl, i + 3)
    ' Del 3d "/" in prefix for local Link
    If Mid(wUrl, 1, 1) = "/" Then wUrl = Mid(wUrl, 2)
    ' Delete "/" at the end
    If Right(wUrl, 1) = "/" Then wUrl = Left(wUrl, Len(wUrl) - 1)
    If StrComp(Url, wUrl, 1) = 0 Then
      ' Link is found - activate it's IE window
      w.Visible = True
      Exit For
    Else
       wUrl = ""
    End If
  Next
  
  ' If Link is not found then create new IE and navigate to the Link
  On Error Resume Next
  If Len(wUrl) = 0 Then
    With CreateObject("InternetExplorer.Application")
      ' Disable pop-up msgs
      .Silent = True
      ' Navigate
      Application.StatusBar = "Navigating to: " & Link & " ..."
      .Navigate Link
      ' Charge the timeout
      t = Timer + WaitSeconds
      ' Wait for "IE is ready" state
      Application.StatusBar = "Waiting for IE's complete state..."
      While .readyState <> 4 And Timer < t: DoEvents: Wend
      ' Wait for "IE.Document is completely downloaded" state
      If Timer < t Then
        Application.StatusBar = "Waiting for Document's downloaded state..."
        While .Document Is Nothing And Timer < t: DoEvents: Wend
      Else
        Err.Raise vbObjectError + 513, , "Timeout happens: " & WaitSeconds & " seconds"
      End If
      ' Activate IE
      Application.StatusBar = False
      If Err Then .Quit Else .Visible = True
    End With
  End If
  
  ' Release the memory of object variable
  Set w = Nothing
  
  ' Return error number (zero if successful)
  NavigateTo = Err.Number
  
  ' Show error message
  If Err.Number <> 0 Then
    Application.StatusBar = "NavigateTo: " & Replace(Err.Description, vbLf, " - ")
    ' Uncomment the line below to show error message
    'MsgBox Err.Description, vbExclamation, "NavigateTo"
  End If
  
End Function

Example of the calling:

Sub Test_NavigateTo()
  
  ' Define the test number
  Const TEST = 2
  
  Select Case TEST
    Case 1: NavigateTo "about:blank"
    Case 2: NavigateTo "www.mrexcel.com/forum/showthread.php?t=553580"
    Case 3: NavigateTo "http://www.google.com/"
    Case 4: NavigateTo "http://support.microsoft.com/kb/q176792/"
    Case 5: NavigateTo "C:\Temp"
  End Select
  
End Sub

 

[SpoolinUp]

Hello all!

I am trying to do similar to the above but cannot get an addition to the code working.

Basically I want excel to automatically open a browser window, then in the same instance navigate to a web page every 5 minutes or so. A simple refresh won't work on this page unfortunately, so I have to have it navigate to the root page every time rather than refresh. I've never used the timer function and am not having great luck, a little help would be much appreciated!

Thanks!!

-Matt

 

[ZVI]

Hello all!

I am trying to do similar to the above but cannot get an addition to the code working.

Basically I want excel to automatically open a browser window, then in the same instance navigate to a web page every 5 minutes or so. A simple refresh won't work on this page unfortunately, so I have to have it navigate to the root page every time rather than refresh. I've never used the timer function and am not having great luck, a little help would be much appreciated!

Thanks!!

-Matt

Hi Matt,
Welcome to the Board!
Seems it's a bit different task, hope you mean something like this:

' Code of Module1

Sub LoopIE(Optional Cancel As Boolean)
  
  ' --> Settings, change to suit
  Const Link = "www.google.com"
  Const RepeatMinutes = 5   ' Time interval in minutes for repeating of this macro
  Const WaitSeconds = 10    ' Waiting time interval in secondss for "IE is ready" state
  ' <-- End of settings

  Dim t As Single
  Static IE As Object, t1 As Double
  
  ' Cancel the repeating of this macro
  If Cancel Then
    ' Cancel schedule
    If t1 Then Application.OnTime t1, "LoopIE", Schedule:=False
    t1 = 0
    ' Reset IE object
    On Error Resume Next
    If Not IE Is Nothing Then IE.Quit
    Set IE = Nothing
    Exit Sub
  End If
  
  ' Trap error
  On Error GoTo exit_

  ' Main
  If IE Is Nothing Then Set IE = CreateObject("InternetExplorer.Application")
  With IE
    .Visible = True
    ' Disable pop-up msgs
    .Silent = True
    ' Navigate
    Application.StatusBar = "Navigating to: " & Link & " ..."
    .Navigate Link
    ' Charge the timeout
    t = Timer + WaitSeconds
    ' Wait for "IE is ready" state
    Application.StatusBar = "Waiting for IE's complete state..."
    While .readyState <> 4 And Timer < t: DoEvents: Wend
    ' Wait for "IE.Document is completely downloaded" state
    If Timer < t Then
      Application.StatusBar = "Waiting for Document's downloaded state..."
      While .Document Is Nothing And Timer < t: DoEvents: Wend
    Else
      Err.Raise vbObjectError + 513, , "Timeout happens: " & WaitSeconds & " seconds"
    End If
  End With

exit_:

  ' Inform about error
  If Err.Number <> 0 Then
    Application.StatusBar = "NavigateTo: " & Replace(Err.Description, vbLf, " - ")
    ' Show error message
    MsgBox Err.Description, vbExclamation, "Error"
    ' Reset IE object
    On Error Resume Next
    If Not IE Is Nothing Then IE.Quit
    Set IE = Nothing
  Else
    ' Clear StatusBar
    Application.StatusBar = False
    ' Charge schedule
    t1 = Now + TimeSerial(0, RepeatMinutes, 0)
    Application.OnTime t1, "LoopIE"
  End If

End Sub

' Macro for the Start button
Sub Start()
  LoopIE
End Sub

' Cancel schedule and quit IE
Sub Auto_Close()
  LoopIE Cancel:=True
End Sub

Regards

 

[SpoolinUp]

Works great Vladimir! Just one hiccup non-related to your great work. I use the program to keep me actively logged into a website that will auto-timeout the login after 10 minutes. On one system running Windows 7, it logs me out every time it refreshes. Any thoughts? Thanks again.

 

[ZVI]

Works great Vladimir! Just one hiccup non-related to your great work. I use the program to keep me actively logged into a website that will auto-timeout the login after 10 minutes. On one system running Windows 7, it logs me out every time it refreshes. Any thoughts? Thanks again.

You are welcome, Matt!
As to that program, if it's VBA program then we need to look onto its code.
If not then may be its support team helps.
Good luck!