Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    How to refresh separate-instance workbooks?

    Hi All,

    is it possible to refresh all workbooks opened as separate instances at once?

    Thank you

    Best regards,

    Wolfgang
    Last edited by wolfgang; 2015-08-07 at 10:54. Reason: Added info

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi wolfgang

    ..do you mean Recalculate all open instances, or screen Refresh all separate instances???

    zeddy
    •Excel Fractured Worksheet Clinic

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Zeddy,

    many thanks for your reply.

    I like to be able to update (refresh) all instances.

    Best,
    Wolfgang

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Hi Wolfgang,

    The following code will update all open workbooks.

    Place in This Workbook_Open
    Code:
    Private Sub Workbook_Open()
    StartClock
    End Sub
    Place in a standard Module
    Code:
    Sub Refresh()
    num = Application.Workbooks.Count
    For I = 1 To num
        Application.Workbooks(I).RefreshAll
    Next I
    End Sub
    
    Sub StartClock()
    SaveTime = Now + TimeValue("00:10:00")
    Application.OnTime SaveTime, "StartClock"
    Refresh
    End Sub
    
    Sub StopClock()
    Application.OnTime SaveTime, "StartClock", , False
    End Sub
    HTH,
    Maud

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Wolfgang,

    The above code is set up to automatically refresh all workbooks but the OnTime command can be removed if you with to manually update all by just running the Refresh procedure.

    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2015-08-08)

  7. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Good Morning Maude,

    runs just fine.

    Thank you very much and have great weekend.

    Have a bottle of Schneider Weisse Dunkel on me - Cheers

    Best regards,

    Wolfgang

  8. The Following User Says Thank You to wolfgang For This Useful Post:

    Maudibe (2015-08-08)

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    H Maude/Wolfgang

    is it possible to refresh all workbooks opened as separate instances at once
    Unless I am missing something I think there is some confusion here as to what an 'instance' of Excel means.
    Perhaps you were just asking..
    "is it possible to refresh all workbooks opened at once" ?????

    My understanding is that an 'instance' of Excel means a separate 'session' of Excel. You can open (i.e. load ) multiple workbooks into a particular single Excel instance ('session')

    'Several open instances' of Excel is not the same as 'several open workbooks'.

    You can re-calculate all open workbooks just by pressing CTRL+ALT+F9.
    (in a single 'instance' of Excel)

    If I open Excel 2007, Excel 2010 then I have two 'instances' of Excel.
    If I open Excel 2010, I can open another Excel2010, and I have two instances of Excel.
    If I open Excel2003, Excel2007, Excel2010, Excel2013, Excel2016, Excel2007, Excel207, ..then I have 7 instances of Excel. I can open 1,2,3 or however many workbooks I like, in each instance.
    These Excel instances are independent of each other.
    So Maud's code will only update those workbooks that were opened in that particular single instance of Excel.

    It would require a more sophisticated vba routine to recalc all workbooks in all instances of Excel.
    You would need to determine first of all how many instances of Excel were currently open.
    And then..
    ..well you get the picture???

    (Opening multiple 'instances' of Excel is usually done when you want to display different workbooks on different monitors.) Using vba to switch between multiple Excel instances is complex, but can be done.
    (I often use Ctrl-J as my vba shortcut key to 'Jump' between Excel Instances)

    zeddy
    •Excel Pathology Department
    .
    Last edited by zeddy; 2015-08-09 at 08:10.

  10. #8
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi zeddy,

    I get the picture thank you.

    I had similar thoughts, of course not as profound as yours and I wanted to cancel my request.

    Then Maude came up with with a solution that serves my purpose well.

    I get workbooks from fellow Analysts with same defined names and even macros.
    So I sent Maude's macro to them and included it in my workbook.

    Now, whenever I get their workbooks I open them in separate instances and they don't interfere.

    Therefore, I am quite happy with the solution and so are they.

    I should have phrased my request somewhat differently but since you are working for the E-P-D you spotted it immediately!

    Best regards,

    Wolfgang
    Last edited by wolfgang; 2015-08-09 at 09:17. Reason: Typo remova

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    This little problem got me to thinking...I could do this with Powershell! Yes, I am a bit different!

    So here's a Powershell script that will refresh/recalc all INSTANCES of Excel. I've added a little touch that if the window is minimized it will leave it minimized (it has to be maximized and active to Send-Keys) after refreshing it. I like this approach as it doesn't require any code to be placed in the workbooks so it will always work.

    Code:
    <#
     +--------------------------------------------------------+
     | Computer Mentors Refresh All Excel Instances           |
     | Programmed by: The Computer Mentor                     |
     |           aka: RetiredGeek (WindowsSecrets.com)   )    |
     | License: Free to all but please include credits.       |
     | Version: 1.0                                           |
     | Notes  :                                               |
     | Updated:                                               |
     +--------------------------------------------------------+
    #>
    
    Clear-Host
    <#
    +---------------------------------------------------------+
    | Requires: Windows Automation Snapin for Windows (WASP)  |
    | D/L Here: https://wasp.codeplex.com/                    |
    |                                                         |
    | Notes: The zip file contains an install PS1 script      |
    |        if using PS 4.0+ you can just copy the wasp.dll  |
    |        file to your WindowsPowershell\Modules\WASP      |
    |        directory and import it as I have done below.    |
    +---------------------------------------------------------+
    #>
    
    Import-Module  WASP
    
    $SleepTime = 100
    Select-Window EXCEL | `
       ForEach-Object {
        $XTitle = $_.GetWindowText()
        $CurWinStatus = $_.GetIsMinimized()
        Write-Host "$XTitle is: $CurWinStatus"
        If ($CurWinStatus) {
          $_.Maximize()
          Start-Sleep -Milliseconds $SleepTime
        }
        Send-Keys -Window $_.Handle  -Keys '%MB'  > $Null
        Start-Sleep -Milliseconds $SleepTime
        If ($CurWinStatus) {
          $_.Minimize()
          Start-Sleep -Milliseconds $SleepTime
        }
    
       }
    You'll notice the $SleepTime variable. This is so you can adjust the module to your particular machine. Excel requires some time to receive the sent keys so a delay is necessary. I find that 100 milliseconds works fine on my main machine which is an i7 Haswell Quad Core. You can adjust this number higher or lower depending on the performance of your machine. It does cause some screen blinking but then you can't have everything.

    BTW: I almost forgot to add that this could easily be run from a shortcut on the desktop or via a key combo it setup in the shortcut.

    HTH
    Last edited by RetiredGeek; 2015-08-10 at 15:53.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-08-10)

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi RG

    ..using WASP in an Excel forum eh!
    You might be stirring up a hornets nest.
    Anyway, I shan't drone any longer - I don't have a bee in my bonnet about it.

    zeddy
    Excel Pest Control & Bug Exterminator
    .

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    I thought this would set you all a BUZZ! However, It's safe to use on a RAID!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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