Consulting

Results 1 to 8 of 8

Thread: Loop through all open Workbooks in all Instances

  1. #1
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    214
    Location

    Loop through all open Workbooks in all Instances

    Hi fellow programmers,

    Sometimes I need to know which workbooks are active (In all Excel instances). For this I use The code in the attached workbook. At my office we udated our Windows and Microsoft Office versions recently. Nowadays we use Windows 10 and Office 2016. Since this update I noticed that all opened workbooks appear double in different instances. See the printscreen on Blad3 (Sheet3)
    With the previous versions (Windows 7 and Office 2010) I got the wanted and expected results, see the printscreen on Blad2 (Sheet2)
    Does anyone know why this happens?
    To test one and other you may want to create 3 workbooks with, for example, the following names: Test1.xlsx, Test2.xlsx and Test 3.xlsx
    Store these workbooks in a folder and open them. Then execute the code behind the Blue and green buttons.

    Thanks in advance for your time.
    Stranno
    Attached Files Attached Files

  2. #2
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    214
    Location
    Nobody??

  3. #3
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    is this what you are after, just create however many workbooks:
    Sub foo()
      Dim wb As Workbook
      For Each wb In Application.Workbooks
        If wb.Name = "Book3" Then
            MsgBox wb.Name & " - found"
            
        End If
      Next wb
    End Sub

  4. #4
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    214
    Location
    Yes JKwan, but will this code loop through every excel instance in search of a given workbook? Because that is what it should do. Right now I am no able to check your code myself because I am moving and haven't unpacked my computer yet.

  5. #5

  6. #6
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    214
    Location
    Thanks Kenneth. So GetWorkbookByName is the way to get it done. Have you looked at my code in the attached workbook. I always used this code and it worked fine. But since the upgrade to a newer version of Excel (2016) the result is undesireable. how come? have you any idea?

  7. #7
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    I haven't analyzed your code, but I'm pretty sure the problem is caused is caused by the windows display change from version 2013. Read about SDI: https://docs.microsoft.com/en-us/off...rface-in-excel

    Artik
    Last edited by Artik; 08-27-2019 at 02:26 PM.

  8. #8
    VBAX Tutor
    Joined
    Jun 2005
    Posts
    214
    Location
    Thanks a lot for this article Artik. Problem solved.

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
  •