Get a List of Installed Programs Using VBA and PowerShell

Ever wanted to retrieve a listing of Installed Software on a PC?
Ever needed to confirm whether a application was installed on a PC?

If you Google the subject you can find discussions and post that get into using APIs, …

Today, however, I thought I’d continue investigating how PowerShell might be able to simplify this task.

Admin Privileges

When I first started out exploring PowerShell, trying to figure out how I could get this information, I was encouraged thinking it might be as simple as using a single WMI call, something like:

Get-WmiObject Win32_InstalledWin32Program

Sadly, this requires elevated privileges to run. 🙁 So I kept digging to see what other PowerShell CmdLets were available and might be able to provide the information I wanted without needing to run the command As An Administrator.

The Solution

Firstly, what you need to know with Windows now is that there are 2 distinct sets of programs, ‘Ordinary’ applications and ‘Store’ applications.

For ‘Ordinary’ applications, I ended up going back to reading the registry and processing the keys.

For ‘Store’ applications, PowerShell provided a CmdLet to get this information with ease.

Note that the functions provided below rely on my PS_GetOutput() function which is part of my post VBA – Run PowerShell Command.

Ordinary Applications

There appear to be 3 distinct registry locations that keep installation information:

  • HKLM:Software\Microsoft\Windows\CurrentVersion\Uninstall
  • HKCU:Software\Microsoft\Windows\CurrentVersion\Uninstall
  • HKLM:Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

Knowing this, we can then build a simple PowerShell command, such as:

Get-ChildItem -Path HKLM:Software\Microsoft\Windows\CurrentVersion\Uninstall, 
                    HKCU:Software\Microsoft\Windows\CurrentVersion\Uninstall, 
                    HKLM:Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall |
Get-ItemProperty |
Sort-Object -Property DisplayName -Unique |
Select-Object -Property DisplayName, DisplayVersion, InstallLocation

and then create a simple VBA wrapper function, like:

Public Function PS_GetInstalledSoftware() As String
    Dim sCmd                  As String

    sCmd = "$software = Get-ChildItem -Path HKLM:Software\Microsoft\Windows\CurrentVersion\Uninstall" & _
           ", HKCU:Software\Microsoft\Windows\CurrentVersion\Uninstall" & _
           ", HKLM:Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall|" & _
           "Get-ItemProperty|" & _
           "Sort-Object -Property DisplayName -Unique|" & _
           "Select-Object -Property DisplayName, DisplayVersion, InstallLocation" & vbCrLf & _
           "$combined = $software|ForEach-Object{" & _
           "$_.DisplayName + '--' + $_.DisplayVersion + '--' + $_.InstallLocation" & _
           "}" & vbCrLf & _
           "$result = $combined -join '~~'"
    PS_GetInstalledSoftware = PS_GetOutput(sCmd)
End Function

This function outputs a simple string with the list of software. We could then use it by simply doing:

Function Test_Software()
    Dim sSoftware             As String
    Dim aSoftwares()          As String
    Dim vSoftware             As Variant
    Dim aSoftware()           As String

    sSoftware = PS_GetInstalledSoftware()
    aSoftwares = Split(sSoftware, "~~")
    For Each vSoftware In aSoftwares
        aSoftware = Split(vSoftware, "--")
        Debug.Print aSoftware(0), aSoftware(1), aSoftware(2)
    Next vSoftware
End Function

Store Applications

As mentioned above, Store application are even easier to retrieve and the basic PowerShell command to do so looks like:

Get-AppxPackage | Sort Name, Version | Select Name, Version, InstallLocation -Unique

Hence we can then build a simple VBA wrapper function such as:

Public Function PS_GetInstalledStoreSoftware() As String
    Dim sCmd                  As String

    sCmd = "$storeSoftware = Get-AppxPackage | Sort Name, Version | Select Name, Version, InstallLocation -Unique" & vbCrLf & _
           "$combined = $storeSoftware | ForEach-Object{" & _
           "$_.Name + '--' + $_.Version + '--' + $_.InstallLocation" & _
           "}" & vbCrLf & _
           "$result = $combined -join '~~'"
    PS_GetInstalledStoreSoftware = PS_GetOutput(sCmd)
End Function

Once again, similarly to the previous function, this function also outputs a simple string with the list of software. We could then use it by simply doing:

Function Test_StoreSoftware()
    Dim sSoftware             As String
    Dim aSoftwares()          As String
    Dim vSoftware             As Variant
    Dim aSoftware()           As String

    sSoftware = PS_GetInstalledStoreSoftware()
    aSoftwares = Split(sSoftware, "~~")
    For Each vSoftware In aSoftwares
        aSoftware = Split(vSoftware, "--")
        Debug.Print aSoftware(0), aSoftware(1), aSoftware(2)
    Next vSoftware
End Function

So, now that you are armed with these 2 functions, you can generate an inventory of installed software, verify if a particular application is installed, output the list to a listbox, … and so much more!

Also, feel free to explore some of the documentation or play around with these commands directly in PowerShell to see what other properties are available to you.

A Few Resources on the Subject