Create an array of all open workbooks

[Guest]

I've never created an array, and after some research have decided I need help.

I need to make a list of all open workbooks (with the exception of
personal.xls) and populate a listbox (on a userform) with this list of open
workbooks.

Then I need to be able to select multiple workbooks from the listbox and
capture the name of each of the select workbooks as a different variable for
later use.

Thanks for the help!

 

[Dave Peterson]

I put this in a General Module:

Option Explicit
Public WkbkNames() As String
Public SomeWkbkWasSelected As Boolean

I created a userform with 2 buttons (ok/cancel) and a listbox.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim wkbkCtr As Long
SomeWkbkWasSelected = False
wkbkCtr = -1
With Me.ListBox1
ReDim WkbkNames(0 To .ListCount - 1)
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
SomeWkbkWasSelected = True
wkbkCtr = wkbkCtr + 1
WkbkNames(wkbkCtr) = .List(iCtr)
End If
Next iCtr
End With
ReDim Preserve WkbkNames(0 To wkbkCtr)

Unload Me

End Sub
Private Sub UserForm_Initialize()
Dim wkbk As Workbook
Dim myWin As Window

Me.ListBox1.MultiSelect = fmMultiSelectMulti
For Each wkbk In Application.Workbooks
For Each myWin In wkbk.Windows
If myWin.Visible = True Then
Me.ListBox1.AddItem wkbk.FullName
Exit For
End If
Next myWin
Next wkbk
End Sub

=======
I could use this in any subsequent routine:

Option Explicit
Sub testme()

Dim iCtr As Long

If SomeWkbkWasSelected = True Then
For iCtr = LBound(WkbkNames) To UBound(WkbkNames)
MsgBox WkbkNames(iCtr)
Next iCtr
End If

End Sub

I'm not sure if you wanted to hold those values or just use them in the ok
button's click event.

 

[Guest]

Dave-

Thank you. That is a great start. I think with a bit of tweaking this will
work perfectly for me.

As a side note, do you know of a good web resource where I can learn more
about creating arrays? Also, one on ReDim and Preserve? I've never heard of
those two before.

Thanks again.

 

[Dave Peterson]

I don't know of any web sites like that, but VBA's help may be useful.

And there's always books.

Debra also has a list of books:
http://www.contextures.com/xlbooks.html

Lot's of people swear by John Walkenbach's books.

Dave-

Thank you. That is a great start. I think with a bit of tweaking this will
work perfectly for me.

As a side note, do you know of a good web resource where I can learn more
about creating arrays? Also, one on ReDim and Preserve? I've never heard of
those two before.

Thanks again.