Pinterest Stumbleupon Whatsapp
Ads by Google

Have you ever needed to know your computer’s CPU or memory details, serial or model number, or installed software versions, but weren’t sure where to look? With just a bit of simple code in Excel, you can extract a whole library of your Windows PC information Did You Get The PC You Ordered? Find Out With These System Information Tools [Windows] Did You Get The PC You Ordered? Find Out With These System Information Tools [Windows] Have you recently purchased a PC? If yes, was it customized? By customized, I mean assembled by a local computer shop, or even just by someone you know who builds and sells computers. If you... Read More .

Sounds too good to be true? Well, that’s Windows, if you know how to use it.

You may need to get your hands a little dirty, but in this article we’ll provide you with all of the code you need, and how to put it into Excel and make it work. Once you’re done, you’ll have an Excel sheet that, every time you open it, tells you everything you ever wanted to know about your computer system.

Note that this will only work with the desktop version of Excel, which you can get with a free trial of Office 6 Ways You Can Use Microsoft Office Without Paying For It 6 Ways You Can Use Microsoft Office Without Paying For It Read More .

The Magic of WMI

On any computer running a Microsoft operating system, you have access to a powerful set of extensions called Windows Management Instrumentation (WMI), which provides you with a very powerful and extensive method to access information and specifications about your computer, operating system 4 Easy Ways to Know If You're on a 64-Bit Version of Windows 4 Easy Ways to Know If You're on a 64-Bit Version of Windows Are you using a processor that supports a 64-bit operating system and are you running a 64-bit operating system? The average off-and-on PC user probably doesn't know the answers to these questions, although they should.... Read More , and installed software.

wmiwindows

Ads by Google

Best of all, the WMI is accessible How To Write A Windows Script To Change Network Settings On The Fly How To Write A Windows Script To Change Network Settings On The Fly In many offices or work environments, you might need to redefine your network settings to connect to different networks. I found myself in this situation often enough, and got so tired of browsing to the... Read More from programming languages like VBA How You Can Make Your Own Simple App With VBA How You Can Make Your Own Simple App With VBA For those of you that would really love to be able to write your own application, but have never typed a single line of code before, I'm going to walk you through making your very... Read More , available in nearly all Microsoft Office products.

Setting Up Your Automated Spreadsheet

First, create your new Excel workbook and call it something like MyComputerInfo.xlsm (Macro enabled). Open it up, skip Sheet1, and rename the next 11 sheets as follows:

  • Network
  • LogicalDisk
  • Processor
  • Physical Memory
  • Video Controller
  • OnBoardDevices
  • Operating System
  • Printer
  • Software
  • Accounts
  • Services

These will hold all of your computer details, and will get updated every time you open this Excel spreadsheet.

wmi1

Next, go to the Developer menu item and under the Controls section click View Code.

wmi2

If you don’t see the Developer menu item, click on File > Options > Customize Ribbon, change the Choose commands from dropdown to All Tabs, select Developer and press the Add>> button to add it to the Customized Ribbon side. Make sure the Developer checkbox on that side is selected once you’ve added it.

wmi3

Once you’re inside of the VBA code editor Monitor If Your VBA Apps Are Running With This Slick Script Monitor If Your VBA Apps Are Running With This Slick Script Read More , all you have to do is add the scripts below to a module. These scripts will do all of the heavy lifting. You don’t have to write this code yourself, just copy and paste them in as shown below.

Once you’re done copying and pasting using the code in the next section, all you have to do is add a bit more code to load up your sheets, and you’re done.

Okay, ready to copy and paste? Let’s get to it.

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.

Of course, it doesn’t do us much good there, so I’ve modified the code to instead output all of the details to one of the sheets that you created in the first step of this guide.

In the coding navigation area, you’ll also see a section called Modules and a component under called Module1. Double click on this to open it. If you don’t see the Modules folder, expand Insert from the menu and select Module.

wmi5

This area is where all of the subroutines will be that will use WMI to pull all important information about your computer, and load it into the sheets you created.

Place the following lines at the very top of the code window all by itself.

Public oWMISrvEx As Object 'SWbemServicesEx
Public oWMIObjSet As Object 'SWbemServicesObjectSet
Public oWMIObjEx As Object 'SWbemObjectEx
Public oWMIProp As Object 'SWbemProperty
Public sWQL As String 'WQL Statement
Public n

It should look like this when you’re done:

wmi5

Paste the following code into Module1 underneath the lines you just created:

Sub NetworkWMI()

sWQL = "Select * From Win32_NetworkAdapterConfiguration"
Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")
Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)
intRow = 2
strRow = Str(intRow)

ThisWorkbook.Sheets("Network").Range("A1").Value = "Name"
ThisWorkbook.Sheets("Network").Cells(1, 1).Font.Bold = True

ThisWorkbook.Sheets("Network").Range("B1").Value = "Value"
ThisWorkbook.Sheets("Network").Cells(1, 2).Font.Bold = True

For Each oWMIObjEx In oWMIObjSet

For Each oWMIProp In oWMIObjEx.Properties_
If Not IsNull(oWMIProp.Value) Then
If IsArray(oWMIProp.Value) Then
For n = LBound(oWMIProp.Value) To UBound(oWMIProp.Value)
Debug.Print oWMIProp.Name & "(" & n & ")", oWMIProp.Value(n)
ThisWorkbook.Sheets("Network").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).Value = oWMIProp.Value(n)
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft
intRow = intRow + 1
strRow = Str(intRow)
Next
Else
ThisWorkbook.Sheets("Network").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).Value = oWMIProp.Value
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft
intRow = intRow + 1
strRow = Str(intRow)
End If
End If
Next
'End If
Next
End Sub

Now, you’re going to create an identical function to this for every sheet you created in the first part of this guide with some minor differences.

For example, next you would copy that code above for NetworkWMI(), paste it underneath the end of that code, and then replace “NetworkWMI()” with “LogicalDiskWMI()”

There’s only a few sections you need to change so that this code fills in the correct sheet.

Change this:

sWQL = "Select * From Win32_NetworkAdapterConfiguration"

To this:

sWQL = "Select * From Win32_LogicalDisk"

Change these four lines:

ThisWorkbook.Sheets("Network").Range("A1").Value = "Name"
ThisWorkbook.Sheets("Network").Cells(1, 1).Font.Bold = True

ThisWorkbook.Sheets("Network").Range("B1").Value = "Value"
ThisWorkbook.Sheets("Network").Cells(1, 2).Font.Bold = True

To this:

ThisWorkbook.Sheets("LogicalDisk").Range("A1").Value = "Name"
ThisWorkbook.Sheets("LogicalDisk").Cells(1, 1).Font.Bold = True

ThisWorkbook.Sheets("LogicalDisk").Range("B1").Value = "Value"
ThisWorkbook.Sheets("LogicalDisk").Cells(1, 2).Font.Bold = True

Change these two lines:

ThisWorkbook.Sheets("Network").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).Value = oWMIProp.Value(n)

To this:

ThisWorkbook.Sheets("LogicalDisk").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).Value = oWMIProp.Value(n)

And these three lines:

ThisWorkbook.Sheets("Network").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).Value = oWMIProp.Value
ThisWorkbook.Sheets("Network").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft

To this:

ThisWorkbook.Sheets("LogicalDisk").Range("A" & Trim(strRow)).Value = oWMIProp.Name
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).Value = oWMIProp.Value
ThisWorkbook.Sheets("LogicalDisk").Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft

Now, you’re almost finished!

Repeat the above for every tab in your workbook. The functions will be as follows:

  • “ProcessorWMI()” for the “Processor” sheet.
  • “PhysicalMemWMI()” for the “Physical Memory” sheet.
  • “VideoControlWMI()” for the “Video Controller” sheet.
  • “OnBoardWMI()” for the “OnBoardDevices” sheet.
  • “PrinterWMI()” for the “Printer” sheet.
  • “OperatingWMI()” for the Operating System sheet.
  • “SoftwareWMI()” for the “Software” sheet.
  • “ServicesWMI()” for the “Services” sheet.

The special “Win32_” objects you need to use to access this information about your computer are as follows:

  • Win32_NetworkAdapterConfiguration – All of your network configuration settings
  • 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_BaseService – List services running (or stopped) on any PC along with the service’s path and file name.

Finish copying/pasting and tweaking each of those functions in the Module1 area of the code. When you’re done, move on to the next section of this guide.

Remember to save your code in the VB view! If you initially saved your workbook with the .xls file type, Excel will now ask you to use a Macro enabled file type, such as .xlsm.

Automatically Loading the Workbook

Now that you have all of those powerful functions created, all that’s left is to run them every time the workbook is opened. Doing this is really easy.

In the left object browser, under Microsoft Excel Objects, you should see ThisWorkbook. Double click on that object to open it.

wmi6

At the top of the code area, there are two dropdown boxes, change the left one to Workbook and the right one to Open.

wmi7

You’ll see a function automatically generated for you called Private Sub Workbook_Open().

Inside here, type the following lines of code so the function looks like this:

Private Sub Workbook_Open()
NetworkWMI
LogicalDiskWMI
ProcessorWMI
PhysicalMemWMI
VideoControlWMI
OnBoardWMI
PrinterWMI
SoftwareWMI
OperatingWMI
ServicesWMI
End Sub

Now, every time you open the workbook, each of your newly created functions will get called, pull all the data Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More from the computer, and load it into the appropriate sheet.

To see it in action, save the code, close the code window, save the workbook, and close that too. Re-open the file you just closed, and you’ll discover that every single sheet holds volumes of data about your computer.

Since the workbook contains Macros, which can be disabled for security reasons, you might have to click Enable Content at the top of the sheet for data to be collected.

Warning – it collects a LOT of data, so expect to wait a few minutes for it to load when you first open the file.

Collecting Computer Information

The amazing thing about using WMI for this is that the data above is only the tip of the iceberg. There are hundreds more WMI classes available containing information about every aspect of your computer system.

If you were adventurous and wanted to take this a step further, you could turn the first sheet of the workbook into a dashboard Build Your Own Weather Dashboard In This Google Spreadsheet Master Class Build Your Own Weather Dashboard In This Google Spreadsheet Master Class Would you like to turn your Google Drive account into an intelligent weather analysis system, and live out a Star Trek fantasy? Ok! Read More , and use the data from the other sheets to feed that dashboard.

The bottom line is that using this new tool, you’ll be able to collect and display more information than most IT experts IT Knowledge Exchange - A free version of Experts Exchange ! IT Knowledge Exchange - A free version of Experts Exchange ! Read More even realize is available about a computer system, and you’ll be able to do it in a fraction of the time those same experts spend digging through the control panel and Administrative areas searching for those details.

Do you see yourself building and using a tool like this? Are you a VBA programmer 4 Great Websites To Make You A Visual Basic Guru 4 Great Websites To Make You A Visual Basic Guru Read More and have you ever used WMI classes? Share your own experiences and ideas in the comments section below!

Image Credits: Adriano Castelli via Shutterstock.com, Mclek via Shutterstock

  1. Jake
    June 16, 2016 at 2:32 pm

    The Code at the top of the post is missing data....the "Public n" is the last item listed to get copied and posed but the screen print shows more after such as "Public n As Long 'Ge....." Would like the rest of that code to finish the top of the script. Not knowing VBA myself, that would be a great help.

    The other code window allows the horizontal scroll bar to work so all the text is available and able to be copied and pasted. The scroll bar in the original (first) code item at the top does not.

    Any help is appreciated. Thanks.

    Jake

  2. Walter Cook
    April 19, 2016 at 2:05 pm

    @Ryan,

    I like this! Could it be modified so I could enter a ComputerName on our network and get the same information remotely? Would be a great troubleshooting tool.

    WKCook

  3. Stephan
    March 21, 2016 at 2:45 pm

    You might also create a base function by passing the arguments..
    Sub ListInfo(Aspect$, Win32_From$)
    Dim sh As Worksheet

    'sWQL = "Select * From Win32_NetworkAdapterConfiguration"
    sWQL = "Select * From " & Win32_From

    Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")
    Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)

    intRow = 2
    strRow = Str(intRow)

    On Error Resume Next
    Set sh = Sheets(Aspect)
    If Err Then
    Err.Clear
    Set sh = Sheets.Add()
    sh.Name = Aspect
    End If
    With sh
    .Activate

    .Range("A1").Value = "Name"
    .Cells(1, 1).Font.Bold = True

    .Range("B1").Value = "Value"
    .Cells(1, 2).Font.Bold = True

    For Each oWMIObjEx In oWMIObjSet

    For Each oWMIProp In oWMIObjEx.Properties_
    If Not IsNull(oWMIProp.Value) Then
    If IsArray(oWMIProp.Value) Then
    For n = LBound(oWMIProp.Value) To UBound(oWMIProp.Value)
    'Debug.Print oWMIProp.Name & "(" & n & ")", oWMIProp.Value(n)

    .Range("A" & Trim(strRow)).Value = oWMIProp.Name
    .Range("B" & Trim(strRow)).Value = oWMIProp.Value(n)
    .Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft

    intRow = intRow + 1
    strRow = Str(intRow)
    Next
    Else
    .Range("A" & Trim(strRow)).Value = oWMIProp.Name
    .Range("B" & Trim(strRow)).Value = oWMIProp.Value
    .Range("B" & Trim(strRow)).HorizontalAlignment = xlLeft

    intRow = intRow + 1
    strRow = Str(intRow)
    End If
    End If
    Next

    intRow = intRow + 1
    strRow = Str(intRow)
    Next
    .Columns("A:B").EntireColumn.AutoFit
    End With
    End Sub

  4. mohsen
    December 7, 2015 at 5:29 am

    thanx a lot!

  5. Thomas K
    July 3, 2015 at 12:25 pm

    Nice overview Ryan. Not sure you are aware but Microsoft shares a neat tool called WMI Code Creator which allows you to generate VBscript to get the code above without necessary hassle.

    Wrote a similar post about how to use the WMI Code Creator to generate necessary VBA to get performance stats recently:
    http://www.analystcave.com/excel-measuring-cpu-usage-in-vba-and-other-performance-metrics/

  6. rose boy
    June 25, 2015 at 4:52 pm

    Instructions are missing.
    In the first code box the last line is: "Public n" without defining it as long...

    The rest of the instructions are not so clear for anyone not really versed in VBA.

    Including a finished sheet with all the code, would have appreciated it. (or at least a text of the full code to enter in Module1)

    Thanks!

    • Ryan Dube
      June 26, 2015 at 12:04 am

      Hi Rose boy. The NetworkWMI() function is essentially the full code.

  7. Cláudio Roberto Gonçalves
    June 25, 2015 at 9:00 am

    For me the instructions are not very clear, I'm not an advanced user of Excel but I can see the potential of this.
    A little more clear could help a lot.

    • Ryan Dube
      June 26, 2015 at 12:04 am

      Okay - thanks for the feedback!

  8. Aakash Kaushal
    June 24, 2015 at 6:02 pm

    Nice, but the instructions are missing some points... After you are done pasting all the stuff in the Module1, how do you get out of it? How do you open the module for the next sheet, how do you save each module...???

    • Ryan Dube
      June 26, 2015 at 12:07 am

      Just like you normally would save anything else in Excel. Just go to File-Save and everything you've done in the code view is saved.

  9. Randy Niven
    June 24, 2015 at 5:13 pm

    Thanks, this is great. Set it all up but when I open the workbook I get a "subscript out of range (error 9)" when it gets to the physical memory portion of the sheet (i.e., it doesn't pull any info after the Processor sheet). I checked the code and it's set up properly as are all sections.

    Any help would be appreciated.

    • Randy Niven
      June 24, 2015 at 5:49 pm

      Figured it out. Need to make sure it has "Win32_PhysicalMemoryArray" and the correct sheet name "Physical Memory". You also need to do this for each item.

      Also, the Accounts tab doesn't appear to be used for anything.

      Once again, thanks. Nice job.

      • Ryan Dube
        June 26, 2015 at 12:12 am

        Great - glad you figured that out! Oh - I originally had the code calling up al of the Accounts on the computer, but found that it was so much information it just took the code too long to process (I was too impatient). Forgot to remove that tab. However, you can add the module for Accounts if you wanted to (it's in the library list on that URL link I included). There's so much information you can potentially pull in about your computer, it's amazing.

Leave a Reply

Your email address will not be published. Required fields are marked *