opening could trigger recalcs and recompiles ..,
maybe adox wouldnt be that bad..

Following is a starter for the ADOX way...
collects all books with sheets from current directory

around 2 seconds on 75 mostly smaller files...
and may bug out on protected workbooks..
not fully tested..


Function BooksAndSheets() As Collection

Dim col As Collection
Dim fil As String
Dim i As Integer
Dim cat As Object

Set cat = CreateObject("ADOX.Catalog")
Set BooksAndSheets = New Collection

fil = Dir$("*.xls")

While fil < vbNullString
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0"";Data Source=" & fil & ";"
Set col = New Collection
For i = 0 To cat.Tables.Count - 1
col.Add Array(fil, Replace(cat.Tables(i).Name, "$", vbNullString))
Next
BooksAndSheets.Add col, fil
fil = Dir$()
Wend
cat.ActiveConnection.Close
Set cat = Nothing

End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

You could using ADOX, but there is a performance overhead there as
well. IMO, it is simpler to just open them, get your data, then close
them, one at a time.