Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts

    Updating files from the Internet - a couple of tweaks

    I have written code which (successfully) gets a file from a website and then updates my (Excel 2003) Workbook.

    I'd like to implement a couple of tweaks to improve it and am stumped:

    1. The download takes a few seconds - this time is variable.
    To pause the routine until the file is downloaded I've inserted this code:
    Code:
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 10
    WaitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait WaitTime
    This works, but is dependent on the download time being under 10 seconds.
    Question - is there a way to get the routine to wait until the download is complete, then continue ?

    Sincce writing this, I suppose one (clumsy) way would be to test for the downloaded file in a loop until it appears. I could limit the number of iterations to prevent an infinite loop. I was hoping for something more elegant !

    2. Getting the download shifts the focus to my browser and I cannot get it back

    The code I am using is:
    Code:
    ActiveWorkbook.FollowHyperlink "http://www. etc"
    I have tried stopping screen updating but the focus still goes to the browser.
    Question - how do I get Excel back as the active window (or, better, stop the focus going to the browser at all) ?

    Thanks, Martin

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Add the following code immediately following your navigation command

    Code:
    Do While .ReadyState <> READYSTATE_COMPLETE  'WAIT UNTIL IE FINISHED LOADING
          	Application.Wait Now + TimeValue("0:00:01")  'OPTIONAL
    Loop
    To for code execution while the web page loades, add the following code after your navigation command:
    Code:
    While IE.Busy
        DoEvents
    Wend
    HTH,
    Maud

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks Maud,

    I should have said that Chrome is the browser - sorry to have omitted that vital information !
    Last edited by MartinM; 2016-11-14 at 14:58.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    OK, I've implemented the "loop" solution to see when the downloaded file is present. That works fine (generally about 30,000 iterations !!)

    That leaves the question of getting the focus back to Excel . . .
    Last edited by MartinM; 2016-11-14 at 17:21.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    I found this, which should do what I want (give the focus to Excel):

    http://www.cpearson.com/excel/ActivateExcelMain.aspx

    but I couldn't work out where to put the declarations.

    Also, as the Declarations are preceded by "Private", how will they be available to the subroutine ?

    Help please.

    PS I can get the focus back to Excel a number of ways, including AppActivate Application.Caption: it flashes orange in the taskbar showing that it is indeed active.
    The problem is getting this window on top.
    Last edited by MartinM; 2016-11-14 at 18:37.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Martin

    Try sticking this in a new module:
    Code:
    Option Explicit
    
    Private Declare Function SetForegroundWindow _
                      Lib "user32.dll" _
                    (ByVal hWnd As Long) As Long
    
    Public Sub bringWindowOnTop()
    
    Call SetForegroundWindow(ActiveWorkbook.Application.hWnd)
    
    End Sub
    ..now just add the line
    bringWindowOnTop
    ..into your code

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks Zeddy,

    That makes the Excel icon in the task bar turn orange and flash, but doesn't bring the window to the front :-(

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Martin

    Bringing a window to the front means that the window is currently behind other windows.
    This isn't the same as 'waking up the file from the taskbar'

    So, now that I know your Excel file has been minimised to the taskbar, the following should work. And, just in case you have another workbook already visible, I made sure the focus returns to the workbook which has this code in it:

    Code:
    Public Sub bringWindowOnTop()
    
    Application.WindowState = xlMaximized
    Application.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlMaximized
    ThisWorkbook.Activate                         '<< needed in case other workbooks open
    Windows(ThisWorkbook.Name).Visible = True
    Call SetForegroundWindow(ActiveWorkbook.Application.hWnd)
    
    End Sub
    As before, just include the line
    bringWindowOnTop
    ..into your code

    zeddy

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thankz for that, but - the window isn't "minimised to the taskbar". It seems to simply be behind the Chrome window. The icon on the taskbar is there whether it is minimised or not, and I just noted that the previous code makes it flash - it seems to have the focus but not to be on top.
    If I minimise the Chrome window - hey presto! there's the Excel window.

    Anyway, I substituted your revised code and got the following result:

    The Excel window remains behind the Chrome window, the taskbar icon flashes and, rather surprisingly, if I click on it (or do Alt Tab) to bring the Excel window to the top, it is not maximised but some intermediate size.

    Is that a clue to what's going on ?

    If I run my update without the call to bringWindowOnTop, the Excel window stays maximised (albeit behind the Chrome window).

    For the avoidance of doubt, this is the code I'm using:

    Code:
    Option Explicit
    
    Private Declare Function SetForegroundWindow _
                      Lib "user32.dll" _
                    (ByVal hWnd As Long) As Long
    
    Public Sub BringWindowOnTop()
    
    Application.WindowState = xlMaximized
    Application.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlMaximized
    ThisWorkbook.Activate                         '<< needed in case other workbooks open
    Windows(ThisWorkbook.Name).Visible = True
    Call SetForegroundWindow(ActiveWorkbook.Application.hWnd)
    
    End Sub
    Last edited by MartinM; 2016-11-16 at 10:21.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Martin

    I was testing this on my dual-monitor setup and it works OK, but then I'm not using Chrome.
    I 'minimised' my workbook and used a timer function to bring my Excel workbook to the 'front' on my second screen, which already had other apps open.

    Would it help to have your Excel workbook always visible and always in front? i.e. if you 'drag' any other app, say Word, then that app 'slides under' the front Excel window.

    zeddy

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Good thought Zeddy.

    I'll give this a go later and report back.

    https://www.extendoffice.com/documen...ow-on-top.html

    Martin

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Try modifying zeddy's code to:

    Code:
    Public Declare Function SetForegroundWindow Lib "user32" _
       (ByVal hWnd As Long) As Long
    
    Public Sub GetExcelBack()
        SetForegroundWindow Application.hWnd
    End Sub
    Run the GetExcelBack sub.

    HTH,
    Maud

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks Maud - a small step closer but not all the way yet !

    Your code unhides the taskbar to bring the flashing Excel icon into view . . . but not the Excel window.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Strange that it works perfectly for me. Will keep on pounding.

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Try running this subroutine

    Code:
    Public Sub BringExcelToFront()
        AppActivate ("Microsoft excel")
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •