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 informationDid 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.

1. Monitor Serial Number Lookup

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.

1. 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.
2. I have been looking for a way of retrieving a monitor's serial number using WMI/DMI. By the looks of things it doesn't look possible, but I have found some articles that suggest you can get the serial number from the EDID value found in the registry (something like HKLM SYSTEM CurrentControlSet Enum DISPLAY DEL5062.

Note that this will only work with the desktop version of Excel, which you can get with a free trial of Office6 Ways You Can Get a Microsoft Office License for Free6 Ways You Can Get a Microsoft Office License for FreeFree Microsoft Office licenses are hard to get, but they do exist. This article covers six ways to get Word and Excel for free.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 systemDo I Have 32-Bit or 64-Bit Windows? Here's How to TellDo I Have 32-Bit or 64-Bit Windows? Here's How to TellHow can you tell if the Windows version you are using is 32-bit or 64-bit? Use these methods and learn why it is important too.Read More, and installed software.

I am using the vb.net code bellow to try to get the monitor serial number of any computer on the network. The code works well for a local computer but I got 0 as result for remote computers. However, with the same logic of the code, I can get other values on remote.

Best of all, the WMI is accessibleHow To Write A Windows Script To Change Network Settings On The FlyHow To Write A Windows Script To Change Network Settings On The FlyIn 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 VBAHow You Can Make Your Own Simple App With VBAHow You Can Make Your Own Simple App With VBAFor 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.

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

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.

Once you’re inside of the VBA code editorMonitor If Your VBA Apps Are Running With This Slick ScriptMonitor If Your VBA Apps Are Running With This Slick ScriptRead 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.

Monitor Serial Number Lookup

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.

It should look like this when you’re done:

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

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:

To this:

Change these four lines:

To this:

Change these two lines:

To this:

And these three lines:

To this:

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.

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

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:

Now, every time you open the workbook, each of your newly created functions will get called, pull all the dataVisualize Your Data & Make Your Spreadsheets User Friendly With An Excel DashboardVisualize Your Data & Make Your Spreadsheets User Friendly With An Excel DashboardSometimes, 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 dashboardBuild Your Own Weather Dashboard In This Google Spreadsheet Master ClassBuild Your Own Weather Dashboard In This Google Spreadsheet Master ClassWould 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 expertsIT 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 programmer4 Great Websites To Make You A Visual Basic Guru4 Great Websites To Make You A Visual Basic GuruWant to learn Visual Basic? Here are some great resources to get you started.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

Explore more about: Computer Maintenance, Microsoft Excel, Visual Basic Programming.

1. Code was very helpful today. But you asked to create 11 tabs at the bottom and only created code for 10. Accounts is missing.

   Thanks for the details. I thought it was easy to follow.

   John

2. You can easily simplify that code instead of just copy it. You just need to add a new object, a for loop and a case selection.

   By this way you can easily add new tabs and a new case to retrieve info. Also very useful information i was looking for this for so long.

   Thanks!

   • @Cesarmades,
     Your code seems to not work. I keep getting an error with
     Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)
     When you step through the code it never does the case. It selects
     Case 'Network' but then skips over
     sWQL = 'Select * From Win32_NetworkAdapterConfiguration'
     It does that for all of them. So the execute query has nothing to execute.

     I have ensured that the tabs are labeled correctly. Any thoughts?

   • I just added two properties to the method. One as 'WorksheetName' and the other as 'SqlName', or whatever that second field is that you have to change. Then you can just reuse the sub as many times as you want. Really you could just make the sheet names the same as what the 'SqlName' needs to be and only have one parameter.

3. 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

4. @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

5. 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

6. thanx a lot!

7. 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/

8. 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!

9. 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.

10. 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..???

    Weather clock 4.5.weather clock full version that. Weather clock 4.5 crack serial.tgz Here.istat menus 5 keygen mac istat menu 5 mac serial.weather clock 4 5 keygen crack.09. Weather clock 4 5 keygen crack. Share2downloads provides softwares and cracks.weather clock keeps you updated on the weather at your location, so you always know what to wear.it is.crack full download serial keygen torrent warez free.share, feel free to submit it to us here.weather clock 4.5 serial key weather clock 4.5 full details.leechers. You may contact us if you have.maya 3d animation software free download with crack.during these years we gathered the most comprehensive collection of reverse engineering art: cracks.if we find similar torrents, we normally show them right here.this account has been suspended.serial number weather clock, autodesk inventor series 9 keygen, bsnl about us. May 2017.you with an enlarged digital display.anuko world clock crack all free.eeather can start triumph america owners manual hopmvhb by clicking download link below.that might interest you: weather clock 4 3weather clock 4 5weather clock 4weather clock 4 2.to solve this issue, as an example just paste the tgz file in c drive and extract just inside the c drive not with in any other folder.weather alarm clock 4.5 torrent file may only be.weather clock 4.5 incl crack keygen,serial,patchposted on 3.share, feel free to submit it to us.or.

11. 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.

    • 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.