Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the index # of a workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the index # of a workbooks
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 dstiefe wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding the index # of a workbooks
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 -- Cheers, Ryan "dstiefe" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the avg of several of cells meeting a criterion (index, ma | Excel Discussion (Misc queries) | |||
finding index | Excel Worksheet Functions | |||
Help in finding Value in Index Array | Excel Discussion (Misc queries) | |||
INDEX and workbooks | Excel Worksheet Functions | |||
finding the row index for the last value > than some value | Excel Worksheet Functions |