Excel Macro To List All Computer Software

I finally reached to the conclusion that this month was full of changes. After spending a lot of hours renewing the blog’s appearance, an unexpected event come up and caused my computer crash. Since I regularly back-up my files (using this tool) I didn’t lose any of my important files. However, I had to spend almost two days formatting my hard disk, installing the Windows and all the applications that I had installed before the crash. As you can easily imagine this was quite boring and frustrating experience because apart from the applications installation I had to change a lot of settings on my computer.

The last format on my hard disk was at 2009 (3.5 years ago). Through the years I have installed several applications on my computer. The problem after the format was my incapability to remember the name of some of these applications. Of course I didn’t forget which Office or AutoCAD version I had installed, but there were some applications that I was using once a month or even more rarely, which was quite difficult to remember their name and their version.

After finishing with the installation, which made me fall behind on my daily work, I thought that I should make something in order to avoid this situation come up in the future. So, I wrote the VBA procedure that you will find below, which creates a list of all installed software at your computer. It uses a WMI script in order to get the installed software and then it writes some basic information for each application on the main sheet. Finally it sorts the applications alphabetically based on their name.

VBA code  

Option Explicit

Sub ListAllSoftware()
    
    'Creates a list of all the installed applications on the user's computer.
    'It uses a WMI script directly through VBA.
    
    'By Christos Samaras
    'https://myengineeringworld.net/////
    
    'Declaring the necessary variables.
    Dim StrComputer As String
    Dim objWMIService As Object
    Dim objSoftware As Object
    Dim objAllSoftwares As Object
    Dim i As Integer
    
    'Stop screen flickering.
    Application.ScreenUpdating = False
    
    'Activate the main sheet.
    shMySoftware.Activate
    
    'Here is the tricky part with the WMI script.
    'First the rootcimv2 namespace is used to access the Win32_Product class.
    'Then a select query is used to get the list of all installed applications.
    StrComputer = "."
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\" & StrComputer & "rootcimv2")
    Set objAllSoftwares = objWMIService.ExecQuery("Select * from Win32_Product")
    
    'Start right below sheet headers.
    i = 2
                
    'This is to ensure that applications were retrieved.
    If objAllSoftwares.Count > 0 Then
        
        'At the main sheet write the necessary object/software properties (name, version, installed date and location).
        For Each objSoftware In objAllSoftwares
            With objSoftware
                shMySoftware.Cells(i, 1).Value = .Caption
                shMySoftware.Cells(i, 2).Value = .Version
                'The date is acquired in the form of yyyymmdd, so the
                'Right, Mid and Left functions are used to transform the date to dd-mm-yyyy.
                shMySoftware.Cells(i, 3).Value = Right(.InstallDate, 2) & "-" & Mid(.InstallDate, 5, 2) & "-" & Left(.InstallDate, 4)
                shMySoftware.Cells(i, 4).Value = .InstallLocation
            End With
            'Go to the next row.
            i = i + 1
        Next
            
        'Rearrange/sort the results in ascending order based on software's name.
        With shMySoftware
            .Range("A:D").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
            .Columns("A:D").EntireColumn.AutoFit
            .Range("A2").Select
        End With
        
        'Re-enable the screen.
        Application.ScreenUpdating = True
        
        'Inform the user that the macro finished.
        MsgBox "All applications from this computer were retrieved successfully!", vbInformation, "Done"
        
    'In case of error, inform the user.
    Else
    
        'Re-enable the screen.
        Application.ScreenUpdating = True
        
        'Inform the user that the macro encountered an error.
        MsgBox "Unfortunatelly, applications from this computer could not be retrieved!", vbCritical, "Error"
    
    End If
    
End Sub

Sub Clear()

    'A simple macro to clean up the main sheet.
    
    'By Christos Samaras
    'https://myengineeringworld.net/////
    Dim LastRow As Integer
    
    'Find the last row and delete sheet's contents (apart from headers).
    With shMySoftware
        .Activate
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If LastRow > 1 Then
            .Range("A2:D" & LastRow).Clear
        End If
        .Columns("A:D").EntireColumn.AutoFit
        .Range("A2").Select
    End With

End Sub

Alternative way

Apart from the VBA code someone can use a much simpler solution which involves writing DOS-like commands in command prompt window. Here are the necessary steps:

1. Open a command prompt window: Go to Start → write on the search box “cmd” → click on the icon cmd. Alternatively you can go to Start → All programs → Accessories → Command Prompt.

2. In the command prompt write: wmic and press enter.

3. Next, write: product get Name, Version, InstallDate, InstallLocation and press enter. You will get the list of programs on the command prompt.

4. If you want to export this list to a text file on your hard disk (path: C:SoftwareList.txt) instead of step 3, you can write on command prompt the following command: /output: C:SoftwareList.txt product get Name, Version, InstallDate, InstallLocation and press enter.

Epilogue 

I know that some of you might wonder why I didn’t use the command line. Well, because is much more convenient to have an Excel Workbook than a text file. This workbook for example is already sorted and can be further processed quite easily. Moreover, the workbook constitutes a “one click solution”, so, by clicking to the funny icon the list is created and sorted automatically. Even an amateur user can run it!

Download it from here

The zip file contains two workbooks, one for Excel 2003 and another for Excel 2007 or newer. Please remember to enable macros before using them.