Exposing System Secrets with VBA and WMI API

posted Dec 23, 2013, 6:23 AM by Craig Hatmaker   [ updated Aug 17, 2017, 5:04 AM ]
The Windows Management Instrumentation (WMI) API exposes a wealth of information about PCs, including remote PCs and Servers.  It is intended as a means to “automate administrative tasks on remote computers.”  Here is a very short list of things WMI can do.
  • Start a process on a remote computer.
  • Schedule a process to run at specific times on specific days.
  • Reboot a computer remotely.
  • Query the Windows event logs on a local or remote computer.
  • Get a list from local or remote computers of:
    • Applications
    • Hardware Components
    • Disk Drives, Capacities and Free space
    • Local and Domain Users
    • Printers and Printer Configurations
    • Network Settings
With VBA, Excel can access WMI and become a powerful tool for PC Technicians, Network Administrators, and System Auditors
Want a glimpse? 
Here is a small routine to list your PC's network adapter configuration. Load it into a module.  Run it.  Now consider this is ONLY looking at the Network Adapter.  WMI can do far more than just that (scary). 
Sub WMI()
    Dim oWMISrvEx       As Object   'SWbemServicesEx
    Dim oWMIObjSet      As Object   'SWbemServicesObjectSet
    Dim oWMIObjEx       As Object   'SWbemObjectEx
    Dim oWMIProp        As Object   'SWbemProperty
    Dim sWQL            As String   'WQL Statement
    Dim n               As Long     'Generic Counter
    sWQL = "Select * From Win32_NetworkAdapterConfiguration"
    Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")
    Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)
    For Each oWMIObjEx In oWMIObjSet
        'Put a STOP here then View > Locals Window to see all properties
        If Not IsNull(oWMIObjEx.IPAddress) Then
            Debug.Print "IP:"; oWMIObjEx.IPAddress(0)
            Debug.Print "Host name:"; oWMIObjEx.DNSHostName
            For Each oWMIProp In oWMIObjEx.Properties_
                If IsArray(oWMIProp.Value) Then
                    For n = LBound(oWMIProp.Value) To UBound(oWMIProp.Value)
                        Debug.Print oWMIProp.Name & "(" & n & ")", oWMIProp.Value(n)
                    Debug.Print oWMIProp.Name, oWMIProp.Value
                End If
        End If
End Sub
Want to see more?
Here is a workbook that facilitates exploring several hundred classes containing information about our CPU, disk drives, memory, operating system, applications, users, and much, much more.  And it can query remote PCs and servers too, assuming you have the credentials.
Uses for WMI Query.xlsm
  • Inventory all PCs in a network including all hardware and software using:
    • Win32_SystemEnclosure - PC's Manufacturer and Serial Number
    • Win32_LogicalDisk - Disks with capacities and free space.
    • Win32_Processor - CPU Specs
    • Win32_PhysicalMemoryArray - RAM/Installed Memory size
    • Win32_VideoController - Graphics adapter and settings
    • Win32_OnBoardDevice - Motherboard devices
    • Win32_OperatingSystem - Which version of Windows with Serial Number 
    • WIn32_Printer - Installed Printers
    • Win32_Product - Installed Software
  • WIn32_Account - List all User Accounts on a PC or Domain
  • Win32_ComputerSystem - See who is currently using a remote PC (also Win32_LoggedOnUser)
  • Win32_BaseService - List services running (or stopped) on any PC along with the service's path and file name.
  • And hundreds more!
Read the references below to learn more about what this API can provide.
WMI NetworkAdapterConfiguration Class Properties http://msdn.microsoft.com/en-us/library/aa394217(v=vs.85).aspx
NOTE! See what others are saying about this.
From: http://www.makeuseof.com/tag/see-pc-information-using-simple-excel-vba-script/  

Building Your WMI Modules

The inspiration for this code comes from a fantastic Google Sites resource called Beyond Excel. The example shown there is a subroutine called WMI(), which passes all of your computer’s network information to the debugging area of the Excel programming environment.