Activating Excel After Running Explorer?

[jcpose]

Hello All,

With Norrie's help I've been able to locate the source of my problem.

I have a spreadsheet that runs an instance of explorer to grab some data. This part works.. however it appears that Excel does not gain focus after closing the browser.

How do I set focus to excel and open the spreadsheet?

I thought Workbooks("RB.xls").Activate would work, but it doesn't allow excel to get focus back after running the browser instance.

Rob.

 

[ChrisM]

Can't answer your specific question, just want to make sure you realize that Excel will query any table on a web page without opening a browser, using the same MS Query features you would use to query an external database. Does that solution not work for you?

I did this for a project and was able to modify the web query for whatever day's data I needed by changing the query source string in VBA. I would put my desired date in cell A1, then VBA would modify the web address to return that day's data. Does that make sense?

 

[jcpose]

Hey Chris,

Yes I had that working with 4 other workbooks, but this particular table is generated using javascript and as such the Web Query doesn't see the data when I pull it into excel using that method. The only method I've come up with so far is to open explorer itself and copy the values that way.

Thanks!
Rob.

 

[Jaafar Tribak]

Hi Rob,

Activating a Window of an Application via code is actually more difficult than it seems.

There is this Application.ActivateMicrosoftApp which does activate the Target App but the IE object model deosn't have this Method .

VBA.AppActivate activates the App but doesn't bring it to the Forground.It only makes the App Icon flash in the TaskBar.

I' ve done some researching and I found this nice ForceForegroundWindow Function which does the job we need via a few APIs.

In the code below, I am using Automation of Internet Explorer to shift focus to Excel when the IE is closed . This will be achieved via the IE OnQuit Event.

Before you can use this approach, you will need to set a Reference to the Microsoft Internet Controls library via Tools=Referencesin the VBE.



Create a Class Module , accept the default name Class1 and place this code in it :

Public WithEvents IE_Events As InternetExplorer

Private Sub Class_Initialize()

    Set objIE = New InternetExplorer
    Set Me.IE_Events = objIE
    objIE.Visible = True
    '\\ You can add more of your code here
    '\\ like navigating to a specific web page....

End Sub

Private Sub IE_Events_OnQuit()

    '\\ Activate XL after 1 Sec of closing IE
    Application.OnTime Now + TimeValue("00:00:01"), "ActivateXL"

End Sub

Place this in a Standard Module :

Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hWnd As Long, lpdwProcessId As Long) As Long

Declare Function AttachThreadInput Lib "user32" _
(ByVal idAttach As Long, ByVal idAttachTo As Long, ByVal fAttach As Long) As Long

Declare Function GetForegroundWindow Lib "user32" () As Long

Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

Declare Function IsIconic Lib "user32" (ByVal hWnd As Long) As Long

Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Const SW_SHOW = 5
Private Const SW_RESTORE = 9

Public objIEClass As Class1
Public objIE As InternetExplorer


'\\ Run this Proc
Public Sub CreateIE()

    Set objIEClass = New Class1

End Sub

Public Sub ActivateXL()

    ForceForegroundWindow FindWindow("XLMAIN", Application.Caption)

End Sub

Public Function ForceForegroundWindow(ByVal hWnd As Long) As Boolean

    Dim ThreadID1 As Long
    Dim ThreadID2 As Long
    Dim nRet As Long
    
    '\\ Nothing to do if already in foreground.
    If hWnd = GetForegroundWindow() Then
        ForceForegroundWindow = True
    Else
    
        '\\ First need to get the thread responsible for this window,
        '\\ and the thread for the foreground window.
        ThreadID1 = GetWindowThreadProcessId(GetForegroundWindow, ByVal 0&)
        ThreadID2 = GetWindowThreadProcessId(hWnd, ByVal 0&)
        
        '\\ By sharing input state, threads share their concept of
        '\\ the active window.
        If ThreadID1 <> ThreadID2 Then
            Call AttachThreadInput(ThreadID1, ThreadID2, True)
        nRet = SetForegroundWindow(hWnd)
            Call AttachThreadInput(ThreadID1, ThreadID2, False)
        Else
            nRet = SetForegroundWindow(hWnd)
        End If
        
        '\\ Restore and repaint
        If IsIconic(hWnd) Then
            Call ShowWindow(hWnd, SW_RESTORE)
        Else
            Call ShowWindow(hWnd, SW_SHOW)
        End If
        
        '\\ SetForegroundWindow return accurately reflects success.
        ForceForegroundWindow = CBool(nRet)
        
    End If

End Function

Now, just run the CreateIE Procedure to start a new Instance of IE.you may activate some other Applications that you may have running(Just to ensure that XL stays in the Background)

Now, when you close the IE App, the Focus will automatically shift to XL bringing it to the foreground.


Regards.