I am using VBA in Excel XP on Windows 2000.

I need to be able to identify particular files and sheets that reside in a
particular directory so they can be associated with information that will be
gathered programmatically. The users will be provided with a "base" file that
contains a certain sheet on which my key information will be entered and
stored by the users.

The users of course will likely change the file names, add new sheets to the
base file, change sheet names, add additional files in the target directory
and so on.

The users need the ability to use the basic Excel functions so instead of
trying to lock Excel down, I placed a custom document property in the
template file. My program will loop through all files in the directory and
when my program opens each file it will check to see if the file contains
this property, if so, then the program will know that this is a "good" file.

Next, I changed the "codename" of the target sheet so that the program could
ID that as well. Since most users only change the tab name of a sheet, I
think the code name is pretty safe.

Does anyone know of any other or better methods for marking these items in a
way that typical users will not notice and will not (un)intentionally alter?
Does anyone know of any drawbacks to using these methods I've described? Any
and all advice welcomed, please post your suggestions. Thanks very much in
advance.

Thanks a lot Jim.

It seemed like a good idea as I thought of it, but I never know when making
changes like this, if it will turn around and bite me later.

Thanks much for your valuable input. Also, I did not know I could directly
reference the sheets like that. Thanks again.

"Jim Thomlinson" wrote:

All sounds good to me... In terms of openeing each file that has some
overhead associated with it, but depending on the number of files it may be a
perfectly acceptabe solution. Using the code name is a great idea and it is
the only way to go in my opinion, for two reasons. One the user can't crash
the program by changing the tab name. Second and in my opinion just as
important it makes the coding a lot easier. In your code by typing the code
name you will get allof the properties and methods associated with the sheet
object which will shorten up your code and make the code a lot easier to
manage. no more typing "sheets("Start"). Instead you can just type
"shtStart." (Assuming the code name is shtStart)

HTH

"quartz" wrote:

I am using VBA in Excel XP on Windows 2000.

I need to be able to identify particular files and sheets that reside in a
particular directory so they can be associated with information that will be
gathered programmatically. The users will be provided with a "base" file that
contains a certain sheet on which my key information will be entered and
stored by the users.

The users of course will likely change the file names, add new sheets to the
base file, change sheet names, add additional files in the target directory
and so on.

The users need the ability to use the basic Excel functions so instead of
trying to lock Excel down, I placed a custom document property in the
template file. My program will loop through all files in the directory and
when my program opens each file it will check to see if the file contains
this property, if so, then the program will know that this is a "good" file.

Next, I changed the "codename" of the target sheet so that the program could
ID that as well. Since most users only change the tab name of a sheet, I
think the code name is pretty safe.

Does anyone know of any other or better methods for marking these items in a
way that typical users will not notice and will not (un)intentionally alter?
Does anyone know of any drawbacks to using these methods I've described? Any
and all advice welcomed, please post your suggestions. Thanks very much in
advance.