AppActivate After opening Web Brower

[gmooney]

Hi,

I have the following code that essentially looks for a downloaded file before launching the URL and automatically downloading a file.

I want the Excel file to return to focus but this code is not and it is staying on the web browser, thus I have to click on Excel from the taskbar. Any ideas how to get Excel back to be displayed?

Sub GetURL()

    Dim NewURL As String
    Dim FollowURL As String

    NewURL = ThisWorkbook.Sheets("Category Review").Range("AP107").Value
    
    CheckforDuplicateDownloadFile
    
    ThisWorkbook.FollowHyperlink NewURL
    
    'Sheets("Instructions").Select
    
    AppActivate "Category Review Builder"
    
    WaitForFileDownload
    
    
    
    BuildMyCategoryReview

 

[johnnyL]

@gmooney Correct. I am asking you to open a totally new excel file and copy the code, unaltered, into a module that you insert and then run the Sub Test. To see if the code works before you start adding in your coding.

 

[gmooney]

@gmooney Correct. I am asking you to open a totally new excel file and copy the code, unaltered, into a module that you insert and then run the Sub Test. To see if the code works before you start adding in your coding.

Do I need to move all the worksheets from the existing file over or are we simply testing this with a blank excel file?

 

[gmooney]

@johnnyL

Ok. I started a new file and I am getting the same compile error as above.

 

[johnnyL]

If you have opened a new excel file, with only the code I suggested, what compile error are you getting?

Screenshot?

 

[gmooney]

If you have a new excel, with only the code I suggested, what compile error are you getting?

 

[gmooney]

 

[johnnyL]

Ok, that may be what @Dan_W was eluding to, I can't test the 64bit version.

 

[Jaafar Tribak]

Just tested the code JohnnyL gave you.

A couple of things:

1- SetForegroundWindow returns a Long not a LongPtr (This is probably the reason you are getting the compile error). So correct the API declaration.

2- Include a DoLoop in the test macro so the excel window is not brought to the front until the page is fully loaded.

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com"
        Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
    End With
  
    BringWindowToFront Application.hwnd

 

[gmooney]

Just tested the code JohnnyL gave you.

A couple of things:

1- SetForegroundWindow returns a Long not a LongPtr (This is probably the reason you are getting the compile error). So correct the API declaration.

2- Include a DoLoop in the test macro so the excel window is not brought to the front until the page is fully loaded.

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com"
        Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
    End With
 
    BringWindowToFront Application.hwnd

@Jaafar Tribak so I just replaced the test macro with your code and I am still getting the compile error. So is the API declaration something different than your new code?

 

[Jaafar Tribak]

Try this :

Option Explicit

#If VBA7 Then

    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" _
        (ByVal hwnd As LongPtr, _
        lpdwProcessId As LongPtr) As Long
    
    Private Declare PtrSafe Function AttachThreadInput Lib "user32" _
        (ByVal idAttach As Long, _
        ByVal idAttachTo As Long, _
        ByVal fAttach As Long) As Long  'all Long because thread IDs are still 32 bits wide in 64-bit Windows
    
    Private Declare PtrSafe Function GetForegroundWindow Lib "user32" _
        () As LongPtr
    
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" _
        (ByVal hwnd As LongPtr) As Long
    
    Private Declare PtrSafe Function IsIconic Lib "user32" _
        (ByVal hwnd As LongPtr) As Long
    
    Private Declare PtrSafe Function ShowWindow Lib "user32" _
        (ByVal hwnd As LongPtr, _
        ByVal nCmdShow As Long) As Long
      
#Else

    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
      
#End If
 
Private Const SW_SHOW = 5
Private Const SW_RESTORE = 9


Sub Test()

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "http://www.mrexcel.com"
        Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
    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