Hi all,

I am trying to have Excel pull out into one or more cells the name an
extension of ALL the workbooks open

I have found two ways but none is completely satisfactory so far:

- builda user form: shows the names but I cant find a way to paste the
into cells

-used the following formula, which only shows the last used workboo
within excel and not whatelse is open

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Has anyone come across something like that before?

Many thanks

B

--
bondcras
-----------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...fo&userid=2099
View this thread: http://www.excelforum.com/showthread.php?threadid=55445

This should get you started:
Private Sub CommandButton1_Click()
Dim WBCount As Long
For WBCount = 1 To Workbooks.Count
Range("A1").Offset(WBCount, 0).Value = Workbooks(WBCount).Name
Next
End Sub

NickHk

"bondcrash" wrote
in message ...

Hi all,

I am trying to have Excel pull out into one or more cells the name and
extension of ALL the workbooks open

I have found two ways but none is completely satisfactory so far:

- builda user form: shows the names but I cant find a way to paste them
into cells

-used the following formula, which only shows the last used workbook
within excel and not whatelse is open

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Has anyone come across something like that before?

Many thanks

BC


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile:
http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=554451

Supposing you want to paste the names of workbooks that are open into column
A, then try this:

Sub List_workbooks()
r = 1 'r represents row number
For i = 1 to Workbooks.Count
Cells(r, 1).Value = Workbook(i).Name
r = r + 1
Next i
End Sub

Cheers,
AP

"bondcrash" wrote
in message ...

Hi all,

I am trying to have Excel pull out into one or more cells the name and
extension of ALL the workbooks open

I have found two ways but none is completely satisfactory so far:

- builda user form: shows the names but I cant find a way to paste them
into cells

-used the following formula, which only shows the last used workbook
within excel and not whatelse is open

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Has anyone come across something like that before?

Many thanks

BC


--
bondcrash
------------------------------------------------------------------------
bondcrash's Profile:
http://www.excelforum.com/member.php...o&userid=20997
View this thread: http://www.excelforum.com/showthread...hreadid=554451

cheers dudes

B

--
bondcras
-----------------------------------------------------------------------
bondcrash's Profile: http://www.excelforum.com/member.php...fo&userid=2099
View this thread: http://www.excelforum.com/showthread.php?threadid=55445