Set environment variable via Excel VBA

Set environment variable via Excel VBA

Postby cecil » 04 Jan 2011, 16:53

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.
Post=37831
cecil
StarLounger
 
Posts: 72
Joined: 09 Sep 2010, 16:01

Re: Set environment variable via Excel VBA

Postby StuartR » 04 Jan 2011, 17:04

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/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
StuartR
Post=37833
User avatar
StuartR
Administrator
 
Posts: 5632
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Set environment variable via Excel VBA

Postby HansV » 04 Jan 2011, 17:06

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
Regards,
Hans
Post=37835
User avatar
HansV
Clever Clogs
 
Posts: 30134
Joined: 16 Jan 2010, 00:14
Location: Leiden, The Netherlands

Re: Set environment variable via Excel VBA

Postby cecil » 04 Jan 2011, 21:45

Thanks again folks. You all are fast!
Post=37880
cecil
StarLounger
 
Posts: 72
Joined: 09 Sep 2010, 16:01


Return to VB/VBA/.Net

Who is online

Users browsing this forum: CCBot [Bot] and 0 guests