Workbooks in multiple running Excel applications

  • Thread starter Thread starter wbl
  • Start date Start date
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
 
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
 
The problem is that I want to do it outside the existing instance !

The problem is to change between running Excel applications.

Regards,
Wbl
 
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.
 
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
'****************************************
 
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.
 
Back
Top