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 |
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 |
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 |
All times are GMT +1. The time now is 06:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com