Public constant in many workbooks

[MD]

How or is it possible to declare a Public Const that will be aplied to ALL
workbooks opened (just in one workbook). The reason is I have 25 workbooks
that are all interlinked somehow. I declare my paths in each of these
workbooks. But if I modify this path, I then need to correct all 25
workbooks.

I've tried to incorporate an import of a text file via VBE with the
modifications in it, but I need to restart the application in order for the
Public Const to be activated.

TIA

MD

 

[Tom Ogilvy]

Why does it have to be a constant. You can have public variables. Since
the values really aren't constant, why not have public variables and
populate them each time from your file.

 

[MD]

The variables are in fact constant. The only reason they change is if the
installation of the program is done in an other drive.
Say that I do Public Const Drive = "C:\"

In a sub I would have something like this
Workbooks.Open Filename:=Drive + "\MyFolder1\MyFile.xls",
UpdateLinks:=0, ReadOnly:=True
If the program is installed on a D Drive , I need to change the value of my
Drive variable to D: and repeat this on all 25 workbooks.

The value of the variable is changed only after the initial installation.

 

[MD]

I was thinking, perhaps if there is a way to inclued in a auto_open sub a
search for the "root" directoryof the program, after it has found that
directory isolate the drive value and declare it as a Public Const then.!!!!

So each time Myfile.xls is called, no matter where it is, when the code is
executed, the auto_open will have found the drive where the program is
installed in.

Hummmm....any suggestions!!!! LOL

 

[Tom Ogilvy]

workbooks("Myfile.xls").Path

should show you where the file is. Or if the code is in Myfile.xls, use

thisworkbook.path everywhere you would have used a constant.