I’ve previously touched upon this subject back in 2016 in my post entitled VBA – Determine the Installed OS.
That said, today, I thought I’d demonstrate 2 possible approaches to retrieving Windows OS information using:
- PowerShell via VBA
- Plain VBA
and dig in a little more into the subject to show various functions that can be used and how they compare to one another at the end of the day.
In both instances, we are calling WMI to get system information. In this instance, we can get Windows OS information by querying the Win32_OperatingSystem class as it exposes a whole slew of OS properties, such as:
| Property | Example of returned Value | Comment |
|---|---|---|
| Caption | Microsoft Windows 10 Home | There is also a Name property but include Harddrive info for example ‘Microsoft Windows 10 Home|C:\WINDOWS|\Device\Harddisk0\Partition2’, so IMHO Caption is what most peopl would be considered the name. |
| OSArchitecture | 64-bit | |
| Version | 10.0.19043 | |
| BuildNumber | 19043 | |
| OSLanguage | 1033 | |
| WindowsDirectory | C:\WINDOWS |
Check the official documentation, a link is provided below, for a complete listing of all the available properties.
Why?
Some people have asked me why we would need to gather such information. The reason is quite simple, this information can be useful when providing support to end-users and can assist in troubleshooting and isolating environment issues. I typically include this information as part of my error logging routine.
It makes sense to have this automatically retrieved through your application were it could be copy/paste, automatically e-mailed, … so your end-users don’t need to find a way to locate the information themselves. We always need to remember that not everyone is comfortable with computers and if we can simplify things for our end-users, we should take the opportunity to do so!
Retrieving Windows OS Information Using PowerShell
Since I’ve been doing a lot of work exploring extending VBA’s capabilities by using PowerShell through VBA, I thought it natural to see if such information could easily be accessed. As with my other VBA/PowerShell posts, such information is very easily retrieved.
These PowerShell functions all require a copy of my PS_GetOutput function which can be found in my post VBA – Run PowerShell Command.
So let’s explore how we can retrieve each property individually and then how we can get them all in a single call.
Windows OS Name
One thing to notice with this particular function is that what you and I call the Name of Windows, Microsoft called Caption in the class. As such, although I entitled my function Name, it is actually retrieving the Caption property.
Public Function PS_GetOSName() As String
Dim sCmd As String
sCmd = "(Get-CimInstance Win32_OperatingSystem).Caption"
PS_GetOSName = PS_GetOutput(sCmd)
End Function
Windows OS Architecture (Bitness)
Public Function PS_GetOSArchitecture() As String
Dim sCmd As String
sCmd = "(Get-CimInstance Win32_OperatingSystem).OsArchitecture"
PS_GetOSArchitecture = PS_GetOutput(sCmd)
End Function
Windows OS Version
Public Function PS_GetOSVersion() As String
Dim sCmd As String
sCmd = "(Get-CimInstance Win32_OperatingSystem).Version"
PS_GetOSVersion = PS_GetOutput(sCmd)
End Function
Putting It All Together
In the example below, I create a custom expression so I can get all three properties in a single call.
Public Function PS_GetOSInformation() As String
Dim sCmd As String
sCmd = "Get-CimInstance Win32_OperatingSystem | Select @{n='OS'; e={$_.Caption + ' ' + $_.OsArchitecture + ' ' + $_.Version}} | FT -HideTableHeaders"
PS_GetOSInformation = PS_GetOutput(sCmd)
End Function
and if you call it you will get back something along the lines of
Microsoft Windows 10 Home 64-bit 10.0.19043
Pick Your Property
Below is a function that accepts a single input arguments, the name of a Win32_OperatingSystem class property and returns it’s value. So instead of needing a different function for each property, we can have a single function now.
Public Function PS_GetOSProperty(sPropertyName As String) As String
Dim sCmd As String
sCmd = "(Get-CimInstance Win32_OperatingSystem)." & sPropertyName
PS_GetOSProperty = PS_GetOutput(sCmd)
End Function
Thus we can do things like
? PS_GetOSProperty("Caption")
Microsoft Windows 10 Home
? PS_GetOSProperty("OSArchitecture")
64-bit
? PS_GetOSProperty("Version")
10.0.19043
? PS_GetOSProperty("BuildNumber")
19043
? PS_GetOSProperty("OSLanguage")
1033
? PS_GetOSProperty("WindowsDirectory")
C:\WINDOWS
So as you can see, now you can access any property you wish.
Retrieving Windows OS Information Using WMI
The PowerShell functions all use the WMI object, and we can access WMI another way in VBA, so let’s see how we can get the exact same information without using PowerShell as an intermediary.
Windows OS Name
Like with the PowerShell function, I named my Function …Name even though Microsoft actually called the property we are retrieving Caption.
Public Function WMI_GetOSName() As String
On Error GoTo Error_Handler
Dim oWMI As Object 'WMI object to query about the PC's OS
Dim sWMIQuery As String 'WMI Query
Dim oCols As Object
Dim oCol As Object
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT Caption FROM Win32_OperatingSystem"
Set oCols = oWMI.ExecQuery(sWMIQuery)
For Each oCol In oCols
WMI_GetOSName = oCol.Caption
Next
Error_Handler_Exit:
On Error Resume Next
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetOSName" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Windows OS Architecture (Bitness)
Public Function WMI_GetOSArchitecture() As String
On Error GoTo Error_Handler
Dim oWMI As Object 'WMI object to query about the PC's OS
Dim sWMIQuery As String 'WMI Query
Dim oCols As Object
Dim oCol As Object
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT OSArchitecture FROM Win32_OperatingSystem"
Set oCols = oWMI.ExecQuery(sWMIQuery)
For Each oCol In oCols
WMI_GetOSArchitecture = oCol.OSArchitecture
Next
Error_Handler_Exit:
On Error Resume Next
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetOSArchitecture" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Windows OS Version
Public Function WMI_GetOSVersion() As String
On Error GoTo Error_Handler
Dim oWMI As Object 'WMI object to query about the PC's OS
Dim sWMIQuery As String 'WMI Query
Dim oCols As Object
Dim oCol As Object
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT Version FROM Win32_OperatingSystem"
Set oCols = oWMI.ExecQuery(sWMIQuery)
For Each oCol In oCols
WMI_GetOSVersion = oCol.Version
Next
Error_Handler_Exit:
On Error Resume Next
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetOSVersion" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Putting It All Together
In the example below, I concatenate the three properties of interest together so we can get all three properties in a single call.
Public Function WMI_GetOSInformation() As String
On Error GoTo Error_Handler
Dim oWMI As Object 'WMI object to query about the PC's OS
Dim sWMIQuery As String 'WMI Query
Dim oCols As Object
Dim oCol As Object
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT Caption, OSArchitecture, Version FROM Win32_OperatingSystem"
Set oCols = oWMI.ExecQuery(sWMIQuery)
For Each oCol In oCols
WMI_GetOSInformation = oCol.Caption & " " & oCol.OSArchitecture & " version " & oCol.Version
Next
Error_Handler_Exit:
On Error Resume Next
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetOSInformation" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
and calling this function will return the same value as with the PowerShell function, thus:
Microsoft Windows 10 Home 64-bit 10.0.19043
Pick Your Property
Similarily to PowerShell, below is a function that accepts a single input arguments, the name of a Win32_OperatingSystem class property and returns it’s value. So instead of needing a different function for each property, we can have a single function now.
Public Function WMI_GetOSProperty(sPropertyName As String) As String
On Error GoTo Error_Handler
Dim oWMI As Object 'SWbemServicesEx 'WMI object to query about the PC's OS
Dim sWMIQuery As String 'WMI Query
Dim oCols As Object 'SWbemServicesObjectSet
Dim oCol As Object 'SWbemObjectEx
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT " & sPropertyName & " FROM Win32_OperatingSystem"
Set oCols = oWMI.ExecQuery(sWMIQuery)
WMI_GetOSProperty = oCols.ItemIndex(0).Properties_(sPropertyName)
Error_Handler_Exit:
On Error Resume Next
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
If Err.Number = -2147217385 Then
'Invalid query - Property doesn't exist
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetOSProperty" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function
Thus we can do things like
? WMI_GetOSProperty("Caption")
Microsoft Windows 10 Home
? WMI_GetOSProperty("OSArchitecture")
64-bit
? WMI_GetOSProperty("Version")
10.0.19043
? WMI_GetOSProperty("BuildNumber")
19043
? WMI_GetOSProperty("OSLanguage")
1033
? WMI_GetOSProperty("WindowsDirectory")
C:\WINDOWS
What About Performance Here?
Both approaches are fast and occur in a question of milliseconds, but a direct call to WMI through VBA is substantially faster none the less.
On my PC, comparing the execution time of PS_GetOSProperty(“OSArchitecture”) vs. WMI_GetOSProperty(“OSArchitecture”) returns the following
PS_GetOSProperty("OSArchitecture") -> 532 milliseconds
WMI_GetOSProperty("OSArchitecture") -> 16 milliseconds
which represents a 516 millisencond increase in execution time which equates to a whopping 3225% increase.
To be sure this wasn’t some fluke, I the compared the execution time of PS_GetOSProperty(“Caption”) vs. WMI_GetOSProperty(“Caption”) returns the following
PS_GetOSProperty("Caption") -> 563 milliseconds
WMI_GetOSProperty("Caption") -> 7 milliseconds
which represents a 556 millisencond increase in execution time which equates to a whopping 7942% increase.
Does this make any sense? Of course it does. A VBA call to WMI is direct, but the call to PowerShell requires launching (hidden) a PowerShell console window, executing the command, pushing the output to the clipboard and the retrieving the clipboard item. So yes, the results jive perfectly with common sense here.
So needless to say, WMI via plain VBA is the way to go here!