How do I use environment variables in Excel?

[kfloyd]

I've defined some environment variables that I want to use to specify a path
to a directory. How do I use these environment variables in the "Save As"
box?

The variables are:

"ADCmath" set to "P:\blah"
"TERM" set to "Fall2004"

I want to save an Excel file in the directory P:\blah\Fall2004 by using
%ADCmath%\%TERM%\filename.xls

but it doesn't work.

(And how can something so simple in UNIX be so obtuse in Windows???)

Ken

 

[Bob Phillips]

Why do you expect Excel to have the same syntax as Unix? That is just
perverse

EnvString = Environ(Indx)

where indx can be the index number of the value, such as Term

 

[Jared]

Try ActiveWorkbook.SaveAs Filename:=ADCMath & "\" & Term & "\" &
ActiveWorkbook.Name

 

[sebastienm]

This only works in DOS, maybe Windows, (and UNIX as Bob mentioned), and
unfortunately not in Excel.
The Environ() function returns Local and System environment variable, with
Local taking precedence, ie returns Local if the variable exists at Local and
Sytem level, but i don't think it returns 'session' environment variables
(the ones you create for example in a Command Prompt session, which disappear
when you close it. However i am not completely sure about that.

You could create a function to substitute environment variables in any string:
'-----------------------------------------------------------
Function SubEnviron(Str As String) As String
Dim pos As Long
Dim strEnv As String
Dim v

SubEnviron = Str
pos = 1
strEnv = Environ(pos)
Do While strEnv <> ""
v = Split(strEnv, "=", 2)
v(0) = "%" & v(0) & "%"
SubEnviron = Application.WorksheetFunction.Substitute(SubEnviron,
v(0), v(1))
pos = pos + 1
strEnv = Environ(pos)
Loop
End Function
'------------------------------------------------------

try it:
msgbox "look for %OS% and %Windir%"

An enhancement could be to add a second optional parameter representing a
filename or registry section to search for instead of the environment
variable table.

Regards,
Sebastien