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

 

[Dan_W]

I usually find when I have difficulties with AppActivate (and switching windows/applications in any scenario) it's usually a result of VBA trying to do something too quickly.
The following will go to Chrome. Wait 5 seconds and then come back to Excel.

Sub GoToChromeAndComeBack()
    AppActivate "Chrome"
    Pause 5
    AppActivate ActiveWorkbook.Name
End Sub

Sub Pause(Seconds As Single)
    Dim CurrentTime As Single
    CurrentTime = Timer
    Do
        DoEvents
    Loop Until CurrentTime + Seconds < Timer
End Sub

 

[gmooney]

I usually find when I have difficulties with AppActivate (and switching windows/applications in any scenario) it's usually a result of VBA trying to do something too quickly.
The following will go to Chrome. Wait 5 seconds and then come back to Excel.

Sub GoToChromeAndComeBack()
    AppActivate "Chrome"
    Pause 5
    AppActivate ActiveWorkbook.Name
End Sub

Sub Pause(Seconds As Single)
    Dim CurrentTime As Single
    CurrentTime = Timer
    Do
        DoEvents
    Loop Until CurrentTime + Seconds < Timer
End Sub

How can I use this if i do not know the browser they will use? It will either be Chrome or Edge.

I also have a timer in this code that will at least wait until the file is downloaded before it moves to the next set of code. Where would I put this GoToChromeAndComeBack code within my code?

 

[Dan_W]

From what I can tell, it's not the part about going to the browser that you're having a problem with - or did misunderstand?

The GoToChromeAndComeBack is just an example.

How can I use this if i do not know the browser they will use?

I have no earthly no idea who "they" are, let alone what browser "they" will use. I don't see that you've explained that scenario above, save that you have said that you "had users".

So ignoring the Chrome part of the solution, you just need to add:

   ThisWorkbook.FollowHyperlink NewURL
    Pause 5
    AppActivate ActiveWorkbook.Name

I also have a timer in this code that will at least wait until the file is downloaded before it moves to the next set of code.

I don't see a timer reference in your code, save for I guess the WaitForFileDownload sub? But that's after the AppActive code and so isn't going to help here. My suggestion is making the computer pause between attempting to switch back to Excel.

Alternatively, JohnnyL's API suggestion works too (and doesn't seem to need to pause) - have you tried it? I would just say that there is a slightly change needed in order for it to work on 64 bit systems.

 

[gmooney]

From what I can tell, it's not the part about going to the browser that you're having a problem with - or did misunderstand?

The GoToChromeAndComeBack is just an example.


I have no earthly no idea who "they" are, let alone what browser "they" will use. I don't see that you've explained that scenario above, save that you have said that you "had users".

So ignoring the Chrome part of the solution, you just need to add:

   ThisWorkbook.FollowHyperlink NewURL
    Pause 5
    AppActivate ActiveWorkbook.Name

I don't see a timer reference in your code, save for I guess the WaitForFileDownload sub? But that's after the AppActive code and so isn't going to help here. My suggestion is making the computer pause between attempting to switch back to Excel.

Alternatively, JohnnyL's API suggestion works too (and doesn't seem to need to pause) - have you tried it? I would just say that there is a slightly change needed in order for it to work on 64 bit systems.

Hi, yes my WaitForFileDownload waits for the file to show in the downloads folder before continuing as download speeds vary. Removing the Chrome option from you above still didn't get it even if I ran that code before the WaitForFileDownload code to run.

Looking at JohnnyL API code where do I add that code? I copied it and pasted into my Module1 and I get red code after the Else command. I have an Option Explicit at the top of my Module1 but nothing else except of Subs after it.

 

[johnnyL]

Never mind the 'red code'. Just copy the entire code to a new workbook module and see if it works as it is written, ie. run the code in 'Sub Test'. The red code is just an indicator of 32bit or 64bit that will be executed, or not shall I say, as far as my understanding.

Edit: To further explain beyond the 'because I sad so' directions. I have 32bit and the top half of the API is in red for me, you indicated you use 64bit and the bottom of the API is in red for you.

 

[gmooney]

Never mind the 'red code'. Just copy the entire code to a new workbook module and see if it works as it is written, ie. run the code in 'Sub Test'. The red code is just an indicator of 32bit or 64bit that will be executed, or not shall I say, as far as my understanding.

Thanks @johnnyL . I moved the code to Module2 and ran Sub Test, it launched MrExcel.com in Internet Explorer and then got a Compile error: Type Mismatch on SetForegroundWindow in the Private Function BringWindowToFront

 

[johnnyL]

Try the entire code, unaltered, in a new workbook module.

A brand new workbook,not another module in same workbook.

 

[gmooney]

Try the entire code, unaltered, in a new workbook module.

Including my code or just the code you provided? You code is in a new module

 

[johnnyL]

completely new workbook, not involving your code

 

[gmooney]

completely new workbook, not involving your code

Sorry @johnnyL I am getting confused as I am a beginner/middle VBA person......Completly new workbook to me means a totally new Excel file?
Right now my code is in Module1 and yours is in Module2 and then I have some Workbook_Open code in ThisWorkbook so I am not sure what you are suggesting I do.

Thanks