Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use environment variables in Excel?
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 a "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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use environment variables in Excel?
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 -- HTH ------- Bob Phillips "kfloyd" wrote in message ... 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 a "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use environment variables in Excel?
Try ActiveWorkbook.SaveAs Filename:=ADCMath & "\" & Term & "\" &
ActiveWorkbook.Name "kfloyd" wrote: 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 a "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use environment variables in Excel?
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(SubEnviro n, 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 "kfloyd" wrote: 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 a "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel General Environment | Excel Discussion (Misc queries) | |||
System Environment Variables | Excel Discussion (Misc queries) | |||
System Environment Variables | Excel Discussion (Misc queries) | |||
Can excel regognise asp environment? | Excel Discussion (Misc queries) | |||
Can I use Environment variables (such as %USERPROFILE%) in Excel . | Excel Programming |