In our last article we learned that we can access the power of scripting with VBA. This article will show how to get vital information from WMI Classes by listing Class Property names and values.
The Win32_OperatingSystem Class grants us access to a wealth of useful information about the Computer Operating System. It also enables us to set the computer time and safely reboot or shutdown a computer. The image of the Form below demonstrates the information that is available including the Registered User, Windows XP Product Serial Number, the Service Pack Version, the Build Number, Computer Name, Free Physical Memory, etc.
The free sample database containing this form can be downloaded at my Gaining Access Free Downloads page.
On this computer WMI has over 500 Classes that allow me to perform actions using the Class’s Methods and get information from the Class’s Properties. But how do we know what Methods and Properties are available and the meaning of the Property Values? In the form above I converted several numeric values to their values in text so they could be understood. How can we get the information we need about a Class?
One way is by using Microsoft’s online documentation of the Win32 Classes. After the web page loads, we can then scroll down the left pane and select the Win32_OperatingSystem page. This page contains documentation about the Methods and Properties of the Class. We need the information to understand what why the CurrentTimeZone Property for my computer is -240 and what is the meaning of the numbers returned by LargeSystemCache, OSLanguage, OSType, etc.
How Do We Get the Win32_OperatingSystem Information?
It is easy to use VBA to get a list of Property names from a WMI Class. But to get the values of those Properties we need to do the following:
1) Learn the name of the Property that is a “Key” Property (also known as a Qualifier).
2) Get the Property Value from an “instance” of the Win32_OperatingSystem Class key Property.
3) Use the Key Property Name and Value in a WQL Query.
Since your Operating System may be different from mine, you need the Key Property Value to make the code in this article work for you.
The Key to Getting the Data: Get the Key Property Value
First we must learn which Property is the Key Property. Occasionally Microsoft does not provide the full documentation we need which is the case with the Win32_OperatingSystem Class web page. Fortunately there is another way to get both the key Property Name and its Value. We can use the Windows Management Instrumentation Tester (wbemtest.exe). This is a very helpful and powerful tool which can do far more than we have time to mention. In a later article we will discuss some of the many tools that are available.
Use the Windows Management Instrumentation Tester
To open the WMIT we click “Start”, then “Run” and type “wbemtest” at the command prompt. When the dialog appears we click the “Connect” button.
We then change “root\default” to “root\cimv2″ which will connect us to the latest version of the CIM repository on our computer.
We click “Connect” again.
We now need to connect to an instance of the Class so we click the “Enum Instances” button.
A Class Info dialog box appears and we enter the name of our Class, Win32_OperatingSystem, and click “OK”.
The Query Result dialog appears containing a line for each instance of the Class showing the key Property and its value. The Win32_OperatingSystem Class only has one instance. On my computer I see Win32_OperatingSystem.Name=”Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition1″. Your Name value may be different but it should start with Win32_OperatingSystem.Name=. This shows us that “Name” is the key Property of the instance.
Next, double-click the instance value line in the Query Result dialog and this will open the Object Editor.
To find the key Property we scroll down until we see the “Name” Property and double-click on that. This opens the Property Editor dialog.
Now we can get the value of the key Property that we need. Right-click on the value in the “Value” box and select “copy”. You can now paste that value somewhere safe so you can have it to use in your code.
Using VBA to Get the Information
Now that we have the key Value we can use the code below to get all of the Property Names and their Values from the Win32_OperatingSystem Class. We start by calling the procedure using the Name Value we got from the Windows Management Instrumentation Tester.
Call GetOperatingSystemInfo(“Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition1″)
Sub GetOperatingSystemInfo(strKeyValue As String) ' We are using late binding Dim objWMIService As Object Dim colItems As Object Dim objItem As Object Dim strWMINamespace As String Dim strComputer As String Dim strWMIQuery As String strComputer = "." strWMINamespace = "\root\CIMV2" ' We use strKeyValue to specify the value of the Key Property to get the "instance" ' of the Win32_OperatingSystem Class in order to get the Property Values strWMIQuery = ":Win32_OperatingSystem.Name='" & strKeyValue & "'" Set objWMIService = GetObject("winmgmts:\" & strComputer & strWMINamespace & strWMIQuery) For Each objItem In objWMIService.Properties_ Debug.Print objItem.Name & ": " & objItem.Value Next ' Release Memory Set objItem = Nothing Set colItems = Nothing Set objWMIService = Nothing End Sub
We can also use code to get the Value of the Key Property if we know its name.
Function GetOSName(strClassName As String) As String Dim objWMIService As Object Dim colItems As Object Dim objItem As Object Dim strComputer As String Dim strOSName As String Dim strWQL As String strComputer = "." strWQL = "SELECT * FROM " & strClassName Set objWMIService = GetObject("winmgmts:\" & strComputer & "\root\CIMV2") Set colItems = objWMIService.ExecQuery(strWQL, , 48) For Each objItem In colItems ' Get the Value of the Name Property strOSName = objItem.Name Next GetOSName = strOSName Set objItem = Nothing Set colItems = Nothing Set objWMIService = Nothing End Function
Since we can now get the Key Value through code, we can now use these two procedures to list the Properties and Values by modifying our first procedure:
Sub ListWMIPropsAndValues(strClassName As String, strKeyName As String) ' We are using late binding Dim objWMIService As Object Dim colItems As Object Dim objItem As Object Dim strWMINamespace As String Dim strComputer As String Dim strWMIQuery As String Dim strKeyValue As String strComputer = "." strWMINamespace = "\root\CIMV2" ' strKeyValue holds the Key Property's Value so we can get an "instance" ' of the Win32 Class in order to get all the Property Values strKeyValue = GetOSName(strClassName) strWMIQuery = ":" & strClassName & "." & strKeyName & "='" & strKeyValue & "'" Set objWMIService = GetObject("winmgmts:\" & strComputer & strWMINamespace & strWMIQuery) For Each objItem In objWMIService.Properties_ Debug.Print objItem.Name & ": " & objItem.Value Next ' Release Memory Set objItem = Nothing Set colItems = Nothing Set objWMIService = Nothing End Sub
Now we can list all of the Property Names and Values without knowing the key Value since we are getting that through code. We call the procedure like this:
Call ListWMIPropsAndValues(“Win32_OperatingSystem”, “Name”)
We have more good news: You can easily copy and modify these Procedures to get information from other WMI Classes. Once you discover the name of the key Property you can change the code accordingly.
You can download the free sample database, WMISample.zip which contains the form and the code used in this article.
We thank Mitch Tulloch who wrote a very helpful article on this subject.
We look forward to discovering more ways to access WMI with VBA in our next article.
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.
Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.
Patrick (Pat) Wood