VBA get Workbooks(index) number

[Werner]

Hi,

Im writing an autoSave routine thats saves localy and on our server as well.
I need to make sure that the right workbook gets saved if the user has
opened x amount of work books. So the autosave proggy might have to save an
workbook that is not "thisworkbook" or "activeworkbook" - thats why i need
the index number .. also the user might change the workbook.name by manually
using saveas so.. the workbook.name is no use either.. the only thing that
does not change is the workbook(index) so.. how would i retrive this one...

the solution i have so far (and it looks ugly, but it works is)

totalWorkbooks = workbooks.count
if totalWorkbooks > 1 then
for n = 1 to totalWorkBooks
if workbooks(n).name = activeWorkBook.name then
youGotTheWorkBookIndex = n
next n
else
youGotTheWorkBookIndex = 1
endif

There got to be a better way than this...

Werner

 

[Robin Hammond]

Werner,

Not sure if this fits your needs completely, but you can change the
"codename" property of the workbook and your users are very unlikely to mess
it up. In the VBA editor, click on ThisWorkbook, enter a new Name in the
properties pane. To check which workbook you are in, you can use

msgbox(thisworkbook.codename)

That should give you a unique key you can work with rather than worrying
about indexes.

Robin Hammond
www.enhanceddatasystems.com

 

[Werner]

Thnks a lot!

This i can use and it's a lot easier than my first index code.

Werner