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?

 

[tusharm]

Why don't you check XL VBA help and see whether an Index property even exists in the context of a workbook or the workbooks collection?

Now, that you've shared the real intent, here are a couple of ideas. First, redo the system so that it uses an Intranet approach. This is ideally suited to a webpage updating a database. You can whip one up in a matter of minutes. Whether your organization will allow you to share it on your network is another story.

It may also be possible to use SharePoint (if you have that) to do what you want. However, I have no experience with the product so I cannot be sure if it can be done through SharePoint.

Lastly, if you want to stay with XL, put all the incoming files in a folder. Automate the process of: open each file in the folder, process it, and then move it to a 'processed' folder.

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?

 

[PolarBear]

Thanks, Erik, the code you suggested worked fine. I like it because I'm not always sure how many workbooks might be open, so this solves the general case.

Thanks!

 

[erik.van.geit]

You're welcome

BUT
it's still a workaround compared to the other method in this thread

Set WB = ....