Set environment variable via Excel VBA

[Post=37831 cecil]

I would like to set an environment variable via Excel VBA. It only needs to be valid for the current session / instance.

Can someone point me to sample code. Thanks.

[StuartR]

I would like to set an environment variable via Excel VBA. It only needs to be valid for the current session / instance.

Can someone point me to sample code. Thanks.

A quick google found http://thedailyreviewer.com/office/view/can-you-set-a-windows-environment-variable-in-vb-114766638. Answer 2 to this question seems to do an adequate job.

Code: Select all

ption Explicit


Private Declare Function GetEnvironmentVariable Lib "kernel32" _
Alias "GetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpBuffer As String, _
ByVal nSize As Long) As Long


Private Declare Function SetEnvironmentVariable Lib "kernel32" _
Alias "SetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpValue As String) As Long


Sub xx()
SetEnvironmentVariable "Rob", "Nuzie!"
MsgBox Environ("Rob")
MsgBox GetEnvironmentVar("Rob")
End Sub


Function GetEnvironmentVar(Name As String) As String
GetEnvironmentVar = String(255, 0)
GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar)
GetEnvironmentVar = TrimNull(GetEnvironmentVar)
End Function


Private Function TrimNull(item As String)
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos > 0, Left$(item, iPos - 1), item)
End Function


[HansV]

Another option:

To create/set an environment variable:
Dim objUserEnvVars As Object
Set objUserEnvVars = CreateObject("WScript.Shell").Environment("User")
objUserEnvVars.item("Test") = "Blah"

To read the environment variable:

Dim objUserEnvVars As Object
Dim strVar As String
Set objUserEnvVars = CreateObject("WScript.Shell").Environment("User")
strVar = objUserEnvVars.item("Test")
MsgBox strVar

[Post=37880 cecil]

Thanks again folks. You all are fast!