Pass environmental variables?

  • Thread starter Thread starter Gunkie
  • Start date Start date
G

Gunkie

Hi there.

Is there a way to pass the UNIX equivalent of PWD into a formula
automatically?

For example:

=INDIRECT(PWD&"\Tasks[file0.xls]Tasks!D25")

Where PWD = "C:\Documents and Settings\Scott\My
Documents\directory1\directory2"

I'm currently building [upgrading] a custom project tracking tool for a
client. Initially, they only wanted to use it on one computer. All I
had to do was hard code the address in that case. They like the tool
so much that they want to pass it out to 100 field staff. UGH!

Thanks!

Scott Ghiz
www.SGSystems.com
 
Scott,

You can create a simple UDF like so

Function EnvVar(var)
EnvVar = Environ(var)
End Function

and use like

=INDIRECT(EnvVar("PWD")&"\Tasks[file0.xls]Tasks!D25")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote...
You can create a simple UDF like so

Function EnvVar(var)
EnvVar = Environ(var)
End Function
Click to expand...

True, but environment variables are almost certain to remain static
during entire Excel sessions, so it makes more sense to use the
Workbook_Open event handler to create defined names from environment
variables.

Private Sub Workbook_Open()
Me.Names.Add Name:="PWD", RefersTo:=Environ("PWD")
End Sub
and use like

=INDIRECT(EnvVar("PWD")&"\Tasks[file0.xls]Tasks!D25")
Click to expand...

Here you miss the point. If file0.xls is already open in the Excel
session, then there's NOTHING GAINED by including the full pathname, so
NOTHING GAINED by dereferencing the environment variable. If the file
isn't open, the INDIRECT call will return an error no matter what the
value of PWD is, so again NOTHING GAINED by dereferencing the
environment variable.

You should also have pointed out that "\Tasks[file0.xls]...' is an
error. The OP either needs another backslash, "\Tasks\[file0.xls]...'
or needs to put the first 'Tasks' inside the square brackets,
"\[Tasksfile0.xls]...'.

Anyway, since it looks like the OP wants to access closed workbooks,
the OP needs to use a macro to replace placeholders for drive/directory
with actual drive/directory for each location, ideally using a table
lookup mapping location to drive/directory. It'd only need to be run
once.
 
Back
Top