Getting all open files in Excel

[J Streger]

I am trying to get a list of all files that Excel currently has open. I say
files in that it can be a mixture of .xla and .xls files (talking Excel 2003
here).

I can just loop through the workbooks collection to get .xls files.

I can then loop through the Addins to get the .xla files...except it doesn't
get them all. Below is the function I am using (ExistsInCollection just
detects if a key (2nd param) links to an index in a collection (1st param)).
What is interesting is I have some .xla files I add via the installer, and
others I open programatically when a .xls opens, thus they aren't in the
Addin list, nor do they show up in the Workbooks collection. I am trying to
do this WITHOUT going through the VBE collection since that opens a security
hole.

Function OpenAddins() As Collection

Dim inx As Integer
Dim colAddins As New Collection

For inx = 1 To Application.AddIns.Count

If ExistsInCollection(Workbooks, Application.AddIns(inx).Name) Then

colAddins.Add Application.AddIns(inx).Name

End If

Next inx

Set OpenAddins = colAddins

End Function

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Dan]

The .xla you open, not install, won't be in the workbooks collection. I
usually test if the addin is open through this:

Property Get CodeXLAisOpen(AddinName As String) As Boolean
'//////Checks if Addin is open

Dim FileNum As Integer
Dim ErrNum As Integer
Dim V As Variant
Dim Filename As String

On Error Resume Next


Filename = Range("PathCode") & AddinName

''''''''''''''''''''''''''''''''''''''''''''
' if the file doesn't exist, it isn't open
' so get out now
''''''''''''''''''''''''''''''''''''''''''''
V = Dir(Filename, vbNormal)
If IsError(V) = True Then
' syntactically bad file name
CodeXLAisOpen = False
Exit Property
ElseIf V = vbNullString Then
' file doesn't exist.
CodeXLAisOpen = False
Exit Property
End If

FileNum = FreeFile()
'''''''''''''''''''''''''''''''''''''''
' Attempt to open the file and lock it.
'''''''''''''''''''''''''''''''''''''''
Err.Clear
Open Filename For Input Lock Read As #FileNum
ErrNum = Err.Number
''''''''''''''''''''
' Close the file.
''''''''''''''''''''
Close FileNum
On Error GoTo 0

''''''''''''''''''''''''''''''''''''''
' Check to see which error occurred.
''''''''''''''''''''''''''''''''''''''
Select Case ErrNum
Case 0
''''''''''''''''''''''''''''''''''''''''''''
' No error occurred.
' File is NOT already open by another user.
''''''''''''''''''''''''''''''''''''''''''''
CodeXLAisOpen = False
Case 70
''''''''''''''''''''''''''''''''''''''''''''
' Error number for "Permission Denied."
' File is already opened by another user.
''''''''''''''''''''''''''''''''''''''''''''
CodeXLAisOpen = True
Case Else
''''''''''''''''''''''''''''''''''''''''''''
' Another error occurred. Assume closed.
''''''''''''''''''''''''''''''''''''''''''''
CodeXLAisOpen = False
End Select
End Property

Of course, you will need a list of the addins you would pass to Filename to
test for....

Dan