finding the index # of a workbooks

[dstiefe]

I have severl open workbooks..and I want to loop through them

and when i find the one with the name "Mountain" i then want to know the
index number of that workbooks (i.e. what is the reference number of the
workbook)

Thank you

 

[Dave Peterson]

This seems like a pretty strange request to me. I don't think I've ever seen
anyone ask to use something like this.

I'm not sure what you're doing, but if you're trying to refer to the workbook
named mountain.xls, then you could use something like:

Dim MtnWkbk as workbook
....

set mtnwkbk = nothing
on error resume next
set mtnwkbk = workbooks("mountain.xls")
on error goto 0

if mtnwkbk is nothing then
msgbox "not open!"
else
'use it anyway you want.
mtnwkbk.worksheets(1).range("A1").value = "hi there"
end if

 

[Ryan H]

I don't believe Excel indexs workbooks when you open them. I wrote some code
that will do what you are wanting though. I would recommend building a
collection of all open workbooks then scan that collection for the workbook
"Mountain". At that point you could return the collection index number of
Mountain. You can do this by calling a user defined function (UDF). Hope
this helps! If so, let me know, click "YES" below.


Sub YourSub()

If GetWbkIndexNumber Then
MsgBox "Workbook collection index number is " & GetWbkIndexNumber
Else
MsgBox "Mountain workbook not found."
End If

End Sub



Function GetWbkIndexNumber() As Variant

Dim wbk As Workbook
Dim colWorkbooks As Collection
Dim i As Long

Set colWorkbooks = New Collection

For Each wbk In Workbooks
colWorkbooks.Add wbk
Next wbk

For i = 1 To colWorkbooks.Count
If colWorkbooks(i).Name = "Mountain" Then
GetWbkIndexNumber = i
Exit Function
End If
Next i

End Function