PDA

View Full Version : [SOLVED] Loop through all open Workbooks in all Instances



stranno
08-01-2019, 02:37 AM
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

stranno
08-26-2019, 03:16 AM
Nobody??

JKwan
08-27-2019, 08:01 AM
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

stranno
08-27-2019, 08:27 AM
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.

Kenneth Hobs
08-27-2019, 12:45 PM
http://www.vbaexpress.com/forum/showthread.php?60682-application-windows-object

stranno
08-27-2019, 01:58 PM
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?

Artik
08-27-2019, 02:10 PM
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/office/vba/excel/concepts/programming-for-the-single-document-interface-in-excel

Artik

stranno
08-27-2019, 11:34 PM
Thanks a lot for this article Artik. Problem solved.