VBA code to maximise/view activeworksheet after having IE navigation?

[kelevra_7]

I want my spreadsheet to be maximised after having used internet controls. That is

ie.visible = true

I've tried the following to no avail:

Application.Visible = True
ThisWorkbook.Activate
ActiveWorkbook.Activate
Application.WindowState = xlMaximized

 

[kelevra_7]

Sorry this post was a bit ambiguous. Basically I'm trying to alt tab twice without using sendkeys. Any ideas? On a side note, I can't seem to edit my post

EDIT: You can't edit your first post?

 

[John_w]

Alt+Tab doesn't maximise a window; it brings it to the foreground. Is that what you mean? If so, try this:

Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long

Sub Test()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com"
    End With
    
    SetForegroundWindow Application.hwnd
    
End Sub

 

[kelevra_7]

Yes bringing it to the foreground is what I meant. Apologies for the confusion. Is there any way to do it without using a function? (I've also tried AppActivate "Microsoft Excel" to no avail).

Edit: Also I tried your function and it didn't work.

Maybe more info is needed on what my sub does.

1. Navigates to site.
2. Waits for 4 seconds while user clicks 'save' when prompted to download a csv file.
3. Copies data from csv file using cell referencing.

Yet once the macro is finished running, the program in the foreground is still IE10/Chrome.

 

[John_w]

Did you try my code exactly as posted, without trying to incorporate it into your own code, to see if the basic code works?

SetForegroundWindow Application.hwnd is the line which brings the Excel window to the foreground (either the VBA window or the sheet window depending on which was active). Try adding a DoEvents statement before that line in your own code.

 

[kelevra_7]

Did you try my code exactly as posted, without trying to incorporate it into your own code, to see if the basic code works?

SetForegroundWindow Application.hwnd is the line which brings the Excel window to the foreground (either the VBA window or the sheet window depending on which was active). Try adding a DoEvents statement before that line in your own code.

Hi,

Tried it on it's own and it didn't work. All that happened was the excel sheet would shart flashing on my task bar at the bottom.

 

[Jaafar Tribak]

The SetForeGroundWindow API Function doesn't work in later versions of Windows ...The trick is to use the AttachThreadInput API

From MSDN : "By using the AttachThreadInput function, a thread can attach its input processing mechanism to another thread. Keyboard and mouse events received by both threads are processed by the thread specified by the idAttachTo parameter"

Here is a custom function BringWindowToFront which should accomplish the task:

Private Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hwnd As Long, _
lpdwProcessId As Long) As Long
 
Private Declare Function AttachThreadInput Lib "user32" _
(ByVal idAttach As Long, _
ByVal idAttachTo As Long, _
ByVal fAttach As Long) As Long
 
Private Declare Function GetForegroundWindow Lib "user32" _
() As Long
 
Private Declare Function SetForegroundWindow Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function IsIconic Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function ShowWindow Lib "user32" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
 
Private Const SW_SHOW = 5
Private Const SW_RESTORE = 9


Sub Test()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com"
    End With
    
    BringWindowToFront Application.hwnd
    
End Sub

Private Function BringWindowToFront _
(ByVal hwnd As Long) As Boolean
 
    Dim ThreadID1 As Long
    Dim ThreadID2 As Long
    Dim nRet As Long
    
    On Error Resume Next
    
    ' Nothing to do if already in foreground.
    If hwnd = GetForegroundWindow() Then
        BringWindowToFront = 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.
        Call AttachThreadInput(ThreadID1, ThreadID2, True)
        nRet = SetForegroundWindow(hwnd)
        
        'Restore and repaint.
        If IsIconic(hwnd) Then
            Call ShowWindow(hwnd, SW_RESTORE)
        Else
            Call ShowWindow(hwnd, SW_SHOW)
        End If
        
        'BringWindowToFront returns TRUE if success.
        BringWindowToFront = CBool(nRet)
        
    End If
End Function

 

[John_w]

Jaafar, thanks for your code. It works perfectly for me on Win XP.

kelevra_7, not sure why I didn't think of it before, but try the VBA AppActivate statement, like this:

Public Sub Test2()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com/forum/forum.php"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    End With
    
    AppActivate Application.Caption
    
End Sub

 

[kelevra_7]

Jaafar, thanks for your code. It works perfectly for me on Win XP.

kelevra_7, not sure why I didn't think of it before, but try the VBA AppActivate statement, like this:

Public Sub Test2()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com/forum/forum.php"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    End With
    
    AppActivate Application.Caption
    
End Sub

Thanks John. Worked a treat.

 

[Jaafar Tribak]

Jaafar, thanks for your code. It works perfectly for me on Win XP.

kelevra_7, not sure why I didn't think of it before, but try the VBA AppActivate statement, like this:

Public Sub Test2()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com/forum/forum.php"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    End With
    
    AppActivate Application.Caption
    
End Sub

Hi John- The AppActivate Method was easier and never occured to me ... we will still keep the BringWindowToFront handy for activating non office applications
Thanks.