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.
Set environment variable via Excel VBA
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Set environment variable via Excel VBA
A quick google found http://thedailyreviewer.com/office/view ... -114766638. Answer 2 to this question seems to do an adequate job.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.
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
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set environment variable via Excel VBA
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
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
Hans
-
- StarLounger
- Posts: 98
- Joined: 09 Sep 2010, 16:01
Re: Set environment variable via Excel VBA
Thanks again folks. You all are fast!