List all currently open xls files

P

paul

Hi

I've got a form with a list box on that i want to display a list of
all excel files the user currently has open so that they can specify
one before running another procedure.

Any ideas how i should go about this?
Thanks for any suggestions
Paul
 
D

Dave Peterson

Option Explicit
Private Sub CommandButton1_Click()

With Me.ListBox1
If .ListIndex < 0 Then
'nothing chosen
Beep
Else
MsgBox .Value
End If
End With

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim wkbk As Workbook

With Me.ListBox1
For Each wkbk In Application.Workbooks
.AddItem wkbk.FullName '.name????
Next wkbk
End With

End Sub
 
A

Alan McQuaid via OfficeKB.com

Hi Paul,

You could use the below on the UserForm_Initialize sub.

Private Sub UserForm_Initialize()

Dim xlsBook As Workbook

For Each xlsBook In Application.Workbooks
ListBox1.AddItem xlsBook.Name
Next xlsBook

End Sub

Hope this helps,

Alan
 
Top