Get Developer Power with the Microsoft Access VBA Environ Function



In this article I will show you the easiest way to use VBA to get a username, the name of the user’s computer, the Program Files Folder, the user’s My Documents folder, the Start Menu folder, the user’s Desktop folder, or the Shared Documents folder. This information will enable you to install, save, or export files to these folders on a user’s computer. You can easily save a shortcut to both the user’s Desktop and the Start Menu. And if you distribute a database online or by other media, you can install the database in any of these places. For instructions on how to use a setup database to move files, including other database files, to a particular folder, see our previous article Get a Computer’s Program Files Folder Path with Access VBA and WMI.


I decided to write an article on this subject after JP, who authors Code For Excel And Outlook, commented about our last post and showed us a procedure that is an easier way to get the Program Files Folder in VBA code using the Environ Function:

Function GetProgramFilesFolder() As String
  GetProgramFilesFolder = Environ("ProgramFiles")
End Function


I replied, “Thanks JP that is excellent code. And since you brought it up I think the subject of using the Environ Function deserves an article of its own.” This is that article.


The Environ Function is very easy to use. You just use Environ and in parenthesis, use a Environment Variable or the Environment ordinal number which is an integer. The ordinal number is useful in listing all of the Environment Variables so you can then know which Variable to use to get the information you want.


You can list all of the Environment Variables on your computer with the following code:

' Lists all Environment Variables
Sub ListEnvironVariables()
    Dim i As Long

    ' Prepare to loop through all the Environment Variables
    i = 1
    ' Start the Loop and run until nothing is returned
    Do Until Environ(i) = ""
        Debug.Print Environ(i)
        ' Increment to the next number
        i = i + 1
    Loop

End Sub


The following procedure numbers the Environment Variables:

' Lists all Environment Variables and numbers them
Function GetEnvironmentVariables() As String

    Dim i As Integer
    Dim strEnviron As String

    i = 1
    Do Until Environ(i) = vbNullString
        strEnviron = strEnviron & i & vbTab & Environ(i) & vbCrLf
        i = i + 1
    Loop

    Debug.Print strEnviron
    GetEnvironmentVariables = strEnviron

End Function


The nice thing about these lists is that they return both the Evironment Variable and the value of the variable. Now you can get the value of any of the Variables by simply using, for example, Environ(“USERNAME”) in your code.


If you are like me and cannot remember all the Variables you can create procedures and save them in a Standard Code Module that you can access at any time. Here are a few that I find helpful:

' Returns "C:\Program Files" on my computer
Function GetProgramFilesFolder() As String
    Dim strValue As String

    strValue = Environ("ProgramFiles")

    GetProgramFilesFolder = strValue

End Function

 

' Returns the current User Name
Function GetUserName() As String

    Dim strValue As String

    strValue = Environ("USERNAME")

    GetUserName = strValue

End Function

 

' Returns the "C:\Documents and Settings\UserName" folder
' In this folder is the Start Menu folder, Desktop folder, My Documents folder, 
' and the Application Data folder
Function GetUSERPROFILE() As String

    Dim strValue As String

    strValue = Environ("USERPROFILE")

    GetUSERPROFILE = strValue

End Function

 

' Returns the "C:\Documents and Settings\UserName\My Documents\" folder
Function GetMyDocumentsDir() As String

    Dim strValue As String

    strValue = Environ("USERPROFILE") & "\My Documents"

    GetMyDocumentsDir = strValue

End Function

 

' Returns "C:\Documents and Settings\UserName\Application Data" folder
Function GetAppDataFolder() As String

    Dim strValue As String

    strValue = Environ("APPDATA")

    GetAppDataFolder = strValue

End Function

 

' Returns the "C:\Documents and Settings\All Users" folder
' In this folder is the All Users Start Menu folder, Shared Documents folder,
' and Favorites folder
Function GetALLUSERSPROFILE() As String

    Dim strValue As String

    strValue = Environ("ALLUSERSPROFILE")

    GetALLUSERSPROFILE = strValue

End Function

 

' This procedure returns the Shared Documents folder
' Returns "C:\Documents and Settings\All Users\Documents"
Function GetSharedDocsFolder() As String

    Dim strValue As String

    strValue = Environ("ALLUSERSPROFILE") & "\Documents"

    GetSharedDocsFolder = strValue

End Function

 

' Returns x86 Family 15 Model 6 Stepping 5, GenuineIntel on my computer
' Not the Processor ID!!!
' Tells you the type of Processor: x86
Function GetPROCESSOR_IDENTIFIER() As String

    Dim strValue As String

    strValue = Environ("PROCESSOR_IDENTIFIER")

    GetPROCESSOR_IDENTIFIER = strValue

End Function

 

' Returns C: on my computer
Function GetHOMEDRIVE() As String

    Dim strValue As String

    strValue = Environ("HOMEDRIVE")

    GetHOMEDRIVE = strValue

End Function

 

' Returns the name of a computer
Function GetComputerName() As String

    Dim strValue As String

    strValue = Environ("COMPUTERNAME")

    GetComputerName = strValue

End Function

 

' Returns C: on my computer
Function GetSystemDrive() As String

    Dim strValue As String

    strValue = Environ("SYSTEMDRIVE")

    GetSystemDrive = strValue

End Function


As you can see, the Environ Function is very helpful, especially if you develop databases that are distributed to others. We will see more useful code in the articles to come as we learn more about how VBA can manage the Registry using WMI.


You can download the free sample database, WMISample.zip which contains the code used in this article plus other code samples. The code used in this article is located in the PAWEnviron Module.

Other free downloads include our new Microsoft Access and SQL Azure and demonstration application which uses Microsoft Access and SQL Azure to leverage the power of cloud computing.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples


Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.


Happy computing,

Patrick (Pat) Wood
Gaining Access

About these ads

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: