Show all workbooks open

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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

BC