Set environment variable via Excel VBA

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Set environment variable via Excel VBA

Post by 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.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Set environment variable via Excel VBA

Post by StuartR »

cecil wrote: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 ... -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
StuartR


User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set environment variable via Excel VBA

Post by 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
Best wishes,
Hans

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Re: Set environment variable via Excel VBA

Post by cecil »

Thanks again folks. You all are fast!