Find the index number for the active workbook?

[PolarBear]

Seems silly, but I can't find a way to get the index number of the active workbook. I have a file which will be renamed every day, so I can't use the filename as a method of referring to it (I need to switch back and forth between two files, so I need a way to refer to both; the other one has a constant name, so that's not a problem).

I thought something like:

intShtNum = ThisWorkbook.Index would work, but it doesn't.

Any thoughts?

 

[erik.van.geit]

Hi,

when you type
ThisWorkbook. (see the DOT) then you get a list
INDEX is NOT in the list and I do not see any property which seems to do what you want

workaround

Sub test()
Dim i As Integer
Dim wb As Workbook

Set wb = ThisWorkbook

    For i = 1 To Workbooks.Count
    If Workbooks(i).Name = wb.Name Then MsgBox i, 64, "INDEX of " & wb.Name
    Next i

End Sub

kind regards,
Erik

 

[Datsmart]

This code will list the Name, Index, and Code number of the active worksheet.

Sub GetActiveSheetNameandNumbers()
    MsgBox "SheetName = " & ActiveSheet.Name & vbCrLf _
         & "Index Num  = " & ActiveSheet.Index & vbCrLf _
         & "CodeName  = " & ActiveSheet.CodeName, , "WorkSheet Data"
End Sub

Be aware, the Index number can change as sheets are added, deleted, or moved in the workbook. Code numbers are static.

 

[erik.van.geit]

used as a function

Sub test()
MsgBox workbook_index(ThisWorkbook)
MsgBox workbook_index(ActiveWorkbook)
End Sub

Function workbook_index(wb As Workbook) As Integer
'Erik Van Geit
'060918
Dim i As Integer


    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = wb.Name Then
        workbook_index = i
        Exit For
        End If
    Next i

End Function

Hi, Datsmart, we are talking about workBOOKS

EDIT: called some guys and galls to check out if there is really no inbuiltfunction like ThisWorkBook.Index

 

[Datsmart]

Opps!
Ignore my post then.
(Must have a case of the Monday's)

 

[tusharm]

AFAIK, there is no such *persistent* property that you can rely on. If only two workbooks will be open and you know the name of one of them, you can figure out which the other one is. Something like the untested

set NewNameWB=iif(workbooks(1).name={known name}, _
    workbooks(2), workbooks(1))
    end if

Depending on how the name varies on a daily basis, there may be other alternatives such as examining the first n characters of the name.

Seems silly, but I can't find a way to get the index number of the active workbook. I have a file which will be renamed every day, so I can't use the filename as a method of referring to it (I need to switch back and forth between two files, so I need a way to refer to both; the other one has a constant name, so that's not a problem).

I thought something like:

intShtNum = ThisWorkbook.Index would work, but it doesn't.

Any thoughts?

 

[SydneyGeek]

Another option is to prompt the user to open the file, then assign a variable to the opened file. Something like:

Sub GrabFile()
    Dim sOtherFile As String
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    
    Set wbDest = ActiveWorkbook
    
    sOtherFile = ""
    
    Do While Len(sOtherFile) = 0
        sOtherFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , _
        "Open the file to process")
    Loop
    Workbooks.Open sOtherFile
    Set wbSource = ActiveWorkbook
    
    '...do stuff with wbSource
End Sub

Denis

 

[erik.van.geit]

thank you, tusharm and denis, for this assistance

which brings us to the question

PolarBear, why do you need this ?
perhaps there are still other available functions which can suit your needs

best regards,
Erik

 

[PolarBear]

I have users in another location who send me statistics each day in a file. They change the name of the file each day to reflect the date, but they don't do it consistently, and trying to educate them is a non-starter.

When I get their files, I have to open them, and copy the data to another master file. Currently, this is done manually. I'd like to automate it, and it would be trivial if I could refer to the users' file by workbooks(i). I'll try some of the suggestions, and let you know what happens.

But still... shouldn't this be as simple as Workbooks.Index? Or does the Index number change dynamically as other workbooks are open and closed?

 

[erik.van.geit]

I have users in another location who send me statistics each day in a file. They change the name of the file each day to reflect the date, but they don't do it consistently, and trying to educate them is a non-starter.

When I get their files, I have to open them, and copy the data to another master file. Currently, this is done manually. I'd like to automate it, and it would be trivial if I could refer to the users' file by workbooks(i). I'll try some of the suggestions, and let you know what happens.

But still... shouldn't this be as simple as Workbooks.Index? Or does the Index number change dynamically as other workbooks are open and closed?

No, as you have read "Index"property doesn't exist for workbooks
you can use code as Denis showed you
you might do a search for "open files in folder"

if you cannot "educate" your users you could check the date the file was created or modified or you could force them to use a button to save the file