Find the index number for the active workbook?

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will list the Name, Index, and Code number of the active worksheet.
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
used as a function
Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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
Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Another option is to prompt the user to open the file, then assign a variable to the opened file. Something like:
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Sep 26, 2005
Messages
215
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,990
Messages
5,678,979
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top