Toggle between Excel and IE

[bradstr]

I have searched for some time but can't seem to find the answer to my question. I would like to be able to switch back and forth between excel and the internet explorer using VBA. I can get the webpage open, but I don't know how to select excel to do some work and then switch back to Internet explorer. This is the code I used from the board to load the page.

'Sub Example()
Dim ie As Object


Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "www.google.com"
ie.Visible = True
ie.Toolbar = True
ie.AddressBar = True
ie.MenuBar = True
ie.StatusBar = True

 

[Centrican]

If you look on the title bar of the internet explorer it will tell you the name of the window. You can then switch to this window by using the code below:

Windows("test.xls").Select
Value = range("a1")
Windows("Test Website").Select
ie.document.all.Item("username")=Value
Windows("test.xls").Select

This will go back to the 'text.xls' spreadsheet and then take the value of A1, then switch back to the internet explorer window, which in this instance was called 'Test Website' and submits the value from cell A1 into a field called 'Username'

Of course, you would change that to be whatever process you wanted. The sheet then gets reselected .

 

[Oorang]

Hi Brad,
You haven't really posted enough information about the overall flow of work to get a complete answer but let me provide you with some general information. You can use VBA.APPActivate to set the focus to any particular window if you know that window's title. You can also take control of the excel application and use the visible method on it as well. The problem you are going to run into is once you have given IE the focus and made Excel invisible etc... How do you indicate that you want to go BACK to excel. The only way I know how to do that is to tap the events in internet explorer to return control to excel when the browser is closed. Sooooo. Here is what you will need to do:
First let’s set up the right references. You need to set a reference to:
Microsoft HTML Object Library
Microsoft Internet Controls
This will also have the benefit of turning on the intellisense for you.
Next insert a class module. It HAS to be a class module not a standard module.
Title it "IEEvents". Since we are going to refer to it by name later make sure you use that name.
Next paste this code in:

Option Explicit
Public WithEvents IEApp As SHDocVw.InternetExplorer
Dim blnIEOpen As Boolean
Private Sub Class_Initialize()
blnIEOpen = True
End Sub
Public Static Property Get BrowserOpen() As Boolean
BrowserOpen = blnIEOpen
End Property
Public Static Property Let BrowserOpen(ByVal vNewValue As Boolean)
blnIEOpen = vNewValue
End Property
Private Sub IEApp_OnQuit()
    blnIEOpen = False
End Sub

Now, insert a standard module:
And paste this code:

Option Explicit
Private Const strDsh As String = " - "
Sub StartIE()
    'To early bind with Microsoft Internet Explorer set a reference to
    'Microsoft Internet Controls
    Dim IE As SHDocVw.InternetExplorer
    Dim XL As Excel.Application
    Dim IEe As New IEEvents
    Set IE = New SHDocVw.InternetExplorer
    Set XL = Excel.Application
    Set IEe.IEApp = IE
    Dim RS As SHDocVw.tagREADYSTATE
    ToggleXLIEVisibility XL, IE
    IE.Navigate "www.google.com"
    Do Until IE.ReadyState = READYSTATE_COMPLETE
    DoEvents: Loop
    Do Until Not IEe.BrowserOpen
    DoEvents: Loop
    Excel.Application.Visible = True
    MsgBox "It Worked"
End Sub
Private Function ToggleXLIEVisibility(XL As Excel.Application, IE As SHDocVw.InternetExplorer)
    If XL.Visible Eqv IE.Visible Then
        IE.Visible = True
        Else
        IE.Visible = Not IE.Visible: XL.Visible = Not XL.Visible
        End If
    If XL.Visible Then
        VBA.AppActivate GetXLTitle(XL)
        Else
        VBA.AppActivate GetIETitle(IE)
        End If
End Function
Private Function GetXLTitle(XL As Excel.Application) As String
    GetXLTitle = XL.Application.Name & strDsh & XL.ActiveWorkbook.Name
End Function
Private Function GetIETitle(IE As SHDocVw.InternetExplorer) As String
    'To really bind with the HTML document set reference to
    'Microsoft HTML Object Library
    Dim IEDoc As MSHTML.IHTMLDocument
    On Error Resume Next
    Set IEDoc = IE.Document
    If Err.Number <> 0 Then
        Err.Clear
        GetIETitle = IE.Name
        Else
        GetIETitle = IEDoc.Title & strDsh & IE.Name
        End If
End Function

If you run the Main Sub, you will see that what it does is hide Excel, navigate to the page of your choice, and then Navigate BACK to Excel when the browser window is closed.

 

[Apeximius]

Looking through Oorang's answer I came up with this simplified version, that requires no classes or events, just a small piece of code:

        VBA.AppActivate IE.Document.Title & " - " & IE.Name, 0

That will focus on Internet Explorer as long as you have already navigated to a page (if you haven't, just remove this bit: "IE.Document.Title &").
It still requires reference to Microsoft Internet Controls, but you always need that when working with internet explorer, so I'm sure you already had that properly set up.

 

[Norie]

What 'work' do you need to do in Excel?