List of all open xls files

[bdbBear]

Can't seem to find an answer.

What I'd like to do is be able to generate a list (full path and file name) of all open xls files.

I'd like to be able to run this code in another xls file ... the fact that this sub would also list this file is fine.

thanks!

 

[PHV]

Have a look at the Workbooks collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

bdbBear,

Caveat to PHV's suggestion -- the Workbooks Collection only referrs to that instance of the Excel Application.

Each instance of an Excel Application has its own collection of open workbooks.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bdbBear]

I did come across other postings that the workbook class only worked on the open file.

I found the following:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/apr05/hey0427.mspx

it's a round-about way wherein I have to instantiate Word ... haven't tried it yet ... can't believe there's not an easier way!

 

[vbap]

bdbBear...are you trying to list all open workbooks in multiple instances of Excel that are running? ...or just get the multiple workbooks open in one Excel instance?

For one instance the workbooks collection is the way to go.

Sub tst()
For Each Workbook In Application.Workbooks
ThePath = Workbook.Path
TheFile = Workbook.Name
fname = ThePath & Application.PathSeparator & TheFile
Debug.Print fname
Next
End Sub

 

[vbap]

I think I understand...you want to list open files with the extension of .xls in the file system?

 

[bdbBear]

Hi vbap,

I want to be able to identify any (and all) xls file that is open.

Will try out your example a bit later.

Thx,

Bruce