Using ENVIRON to find the XLB and QAT files

I am currently updating the FastExcel profiler to run with 64-bit Excel. This involves the rather tedious conversion of a large number of Windows API statements to use conditional compilation, VBA7 and WIN64.

Whilst doing this I discovered the VBA ENVIRON function, which gives you an easy way to get some information about the, well, environment.

For example I wanted to show the size of the XLB file, which stores toolbar customisations even in Excel 2007 and later, and the QAT file which stores QAT customisations. The reason for this is that some poor coding practices cause the size of these files to ballon and become corrupt: then Excel starts crashing but does not tell you why!

You could do this by hardcoding the paths to the files in your code, but thats a bad idea because the paths are different for different versions of Windows.
Or you could do this by using Windows API calls to find the directories, and handle the 32-bit/64-bit coding etc.

Or you can use ENVIRON, which is MUCH easier!

The path to the XLB file under Windows 7 on my system is something like:
E:\Users\your username\Appdata\Roaming\Microsoft\Excel\Excelnn.xlb Using the ENVIRON function in a Windows and Excel version-independent way it looks like this:

strGetXLBPath = Environ(“AppData”) & “\Microsoft\Excel\Excel” & CStr(CLng(Val(Application.Version))) & “.xlb”

The path to the QAT file under Windows 7 on my system is something like:
E:\Users\your username\AppData\Local\Microsoft\Office\Excel.QAT
And using ENVIRON in VBA:

strGetQATPath = Environ(“LocalAppData”) & “\Microsoft\Office\Excel.qat”

My XLB file is currently about 12KB and my QAT is less than 1KB, and I reckon anything over about 30KB is asking for trouble.

If they get corrupt you can delete or rename these files and Excel will happily recreate fresh copies (But of course you lose your customisations).
The easiest way to navigate to the directories is to enter %AppData% or %LocalAppData% in the Windows search programs and files box (Windows button).

Other things I use ENVIRON for include:

  • Getting the path to the Temp Files folder using ENVIRON(“TEMP”)
  • Getting the number of processors using ENVIRON(“NUMBER_OF_PROCESSORS”)
  • Getting the computer name using ENVIRON(“COMPUTERNAME”)

If you want to see all the environment variables active on your system (in the VBE Immediate window) you can use this code which I found on StackOverflow

Sub EnumSEVars()
 Dim strVar As String
 Dim i As Long
 For i = 1 To 255
 strVar = Environ$(i)
 If LenB(strVar) = 0& Then Exit For
 Debug.Print strVar
 End Sub

You can find 2 excellent articles on ENVIRON here (Win XP) and here (Win 7 8).

OK so how many of you use ENVIRON, or like me you did not even know it existed?

This entry was posted in VBA. Bookmark the permalink.

5 Responses to Using ENVIRON to find the XLB and QAT files

  1. Gary says:

    I use it. most of the time it’s just to test for username, so different code executes if I’m running at home or my client’s location.

  2. Graham says:

    Yes, I use it for getting the USERNAME and USERDOMAIN, handy for adding into reports / views / headers & footers created via VBA

  3. Doug Glancy says:

    To think that for a while there I knew something you didn’t! 🙂

    I use ENVIRON to specify the temp directory as a place to create … temporary files.

  4. You can also see all the environment variables active on your system by calling up a “Command Prompt” (the replacement for the old DOS) window, typing the word “set” (without the quote marks) and hitting the Enter Key.

  5. It just occurred to me that there is a simple way to list the environment variables in the Immediate Window without using a code procedure like you posted, you can simply execute this code line directly in the Immediate Window…

    Shell Environ(“comspec”) & ” /k set”, vbMaximizedFocus

    If you haven’t already done so in the past, click the window icon in the upper left corner of the “Command Prompt” window, select “Properties” from the popup menu and set the “Command History” (located on the Options tab of my Windows 8 system… not sure if it is in the same location on other versions) and change the Buffer size to a larger number (I set mine to 50 lines), dismiss the “Command Prompt” window and then rerun the above code line to get a larger display.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s