Workbooks in multiple running Excel applications

W

wbl

Hi,

How can i list in VBA all open workbooks in multiple running
applications ?

I want to list them in a listbox, and activate a workbook by clicking
the name in de listbox.

Thanks,

wbl
 
T

Tom Ogilvy

I believe you would have difficulty doing it outside the existing instance
of Excel, but your terminology may be imprecise and you are only talking
about the current instance. For a single instance of Excel:

for each bk in Application.workbooks
Userform1.Listbox1.AddItem bk.name
Next
 
W

wbl

The problem is that I want to do it outside the existing instance !

The problem is to change between running Excel applications.

Regards,
Wbl
 
T

Tom Ogilvy

To get a listing of workbooks open in additional instances of the Excel
application would require an automation link to the instance I believe. I
haven't seen any code or method to do this. You can enumerate windows and
identify processes, but I haven't seen any code that shows how to convert
this to an automation reference to the excel application in that instance.
 
J

Jim Cone

In my limited testing, the following code from Ivan Moala will tell you if a
file is open in any instance of Excel. It will not tell you which instance
it is open in...

'----------------------------------------
'// Ivan F Moala
'// http://www.xcelfiles.com
Sub TestVBA()
If IsFileOpen("C:\Data.xls") Then
MsgBox "File is open"
Else
MsgBox "File is not open"
End If
End Sub

Function IsFileOpen(strFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Error is generated if you try
'// opening a File for ReadWrite lock >> MUST BE OPEN!

Dim hdlFile As Long
On Error GoTo FileIsOpen:
hdlFile = FreeFile
Open strFileName For Random Lock Read Write As hdlFile
IsFileOpen = False
Close hdlFile
Exit Function
FileIsOpen:
'// Someone has it open!
IsFileOpen = True
Close #1
End Function
'-----------------------------------------------------
Regards,
Jim Cone
San Francisco, CA
'****************************************
 
T

Tom Ogilvy

or any of these that show the same technique:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open


but none of that has much relevance to the question asked by the OP.
 
Top