Managing Custom Environment Variables
Posted by Derek Mang on 3rd June 2008
I am in the process of developing a new MS Word form for our company. This is a work authorization form that is intended to be pretty generic, allowing the user to select the type of work to be performed and any attachments that should accompany the form. There are various fields that require completion, including the user's name, company, email address, and authorizations.
There is the potential for this form to be used many times over by many individuals there is the opportunity to provide some relief to the user audience each time the form is used.
Since all the users are on Windows / Office platforms, using environment variables seemed to be an option for retaining user information. Not knowing how to manage environment variables through VBA code, I went looking, and it did not take too long to find what I was after!!
The code I found is pretty simple, using WMI functions (Windows Management Instrumentation). There are routines to create, retrieve, and delete variables at the user-level.
So here's what happens:
When the user first opens the MS Word form, the system is checked for the specific environment variables. If not found, a userform is presented requesting user name, email address and company. Click OK and the user-level environment variables are created. This is a one-time activity, and the userform says so! Whenever the MS Word form is opened from this point forward, the username, company and email address form fields are automatically completed (unless there is data already in these form fields!)
A right-click on the "Client Authorization" form field - there are several - yields a dropdown menu that includes a new submenu group of functions specific to form fields (see my post from April 2007 -(Form Field Specific Menu Items in MS Word http://proofficedev.com/blog/2007/04/03/12/).
Clicking "Signoff" retrieves the supplied username from the appropriate environment variable for the selected form field.
Code Samples
The following is an example of the call to create a variable:
The following is an example of the call to retrieve a variable:
There is no form code that calls the delete subroutine. It's here for reference.
(The code is in a module)
Dim strcomputer As String
Dim objWMIService As Object
Dim objVariable As Object
strcomputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strcomputer & "\root\cimv2")
Set objVariable = objWMIService.Get("Win32_Environment").SpawnInstance_
objVariable.Name = strVarName
objVariable.UserName = Application.UserName 'user name from MS Word
objVariable.variablevalue = strVarValue
objVariable.Put_
End Sub
Function GetVar(strVar) As String
Dim strcomputer As String
Dim objWMIService As Object
Dim colitems As Object
Dim objitem As Object
GetVar = ""
strcomputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strcomputer & "\root\cimv2")
Set colitems = objWMIService.ExecQuery _
("Select * from Win32_Environment Where Name = '" & strVar & "'")
For Each objitem In colitems
GetVar = objitem.variablevalue
Next
End Function
Sub DeleteVar(strVar)
Dim strcomputer As String
Dim objWMIService As Object
Dim colitems As Object
Dim objitem As Object
strcomputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strcomputer & "\root\cimv2")
Set colitems = objWMIService.ExecQuery _
("Select * from Win32_Environment Where Name = '+ & strvar & +'")
For Each objitem In colitems
objitem.Delete_
Next
End Sub
This method suits this situation very well in my opinion. Hopefully others will be able to adapt this for their own needs.
The link below will take you to the Microsoft Developer Network page for the Scripting API for WMI:
http://msdn.microsoft.com/en-us/library/aa393258(VS.85).aspx
Posted in VBA | No Comments »