Use VBA to automatically add VBProject References to PowerPoint Library, dependent on the Office Version PC is running

Question

Introduction: I have a VBA application ~ 5000 lines that manipulates data in Excel, builds automatic charts and then exports the charts from each sheet to different PowerPoint Slides. This has been working fine for the past 5 months on many computers that are running this Code.

Problem: Some of the PCs in my organization have been upgraded from Office 2010 to Office 2013 and Office 2016. In my code I am using Early Binding on my PowerPoint handling, this is resulting in Errors since the References of the VB Project have changed from "Microsoft PowerPoint 14.0 Object Library" to "Microsoft PowerPoint 15.0 Object Library".

Target: how do I change the References automatically by code according to the PowerPoint version of the PC running my code ?

Status: My current code is able to loop through all enabled VBProject References, check if any of them is "Missing", and remove them to avoid the error message. I am also able to add PowerPoint 14.0 library by code.

My Question: how do I add the PowerPoint library according to the PowerPoint running on the PC of current user running this code, when PowerPoint is not running ? How can I read the PowerPoint version installed on a PC when it's not running ?

Also, is the folder location of "Microsoft PowerPoint 15.0 Object Library" and "Microsoft PowerPoint 16.0 Object Library" and other versions change if you run the PC on different Windows Versions ? Like Win7 32-bit is different than Win10 32-bit ? and different than Win10 64-but ?

"Bonus" Question: I couldn't find anywhere online a chart (or a list) that stores all the data for folder locations of the "MSPPT.OLB" file, for different operating systems, and different Office versions.

---

My Current Code

Option Explicit

Sub RemoveMissingReferences_AddReference()

'    display Windows Version installed on this PC
'    Win7.      (=6.1, 64bit)
'    Win8       (=6.2, 64bit)
'    Win8.1     (=6.3*)
'    Win10      (=10.0*)
'
MsgBox "Windows Version is: " & getVersion
MsgBox "Excel Version is: " & Application.Version

Dim theRef As Variant, i As Long
Dim ProjRef() As String

ReDim ProjRef(1 To ThisWorkbook.VBProject.References.count)
'Remove any missing references
For i = ThisWorkbook.VBProject.References.count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    ProjRef(i) = theRef.FullPath ' read path to string array , will use it later

    ' if reference is "Missing" >> remove it to avoid error message
    If theRef.isbroken = True Then
        ThisWorkbook.VBProject.References.Remove theRef
    End If
Next i

' add a Reference to PowerPoint , according to version running on the PC
Dim NewRef_FullPath As String
NewRef_FullPath = "C:\Program Files (x86)\Microsoft Office\Office14\MSPPT.OLB" ' Add PowerPoint 2010 Windows 7-32Bit

' loop through all existing references and check if new requested reference already checked
For i = 1 To UBound(ProjRef)
    If InStr(1, ProjRef(i), NewRef_FullPath) > 0 Then  ' reference already installed on PC >> pevious code runs
        MsgBox "New Ref already installed"
        Exit Sub
    End If
Next i

On Error GoTo CanNotAddPowerPoint
Application.VBE.ActiveVBProject.References.AddFromFile NewRef_FullPath  'try to add a reference to PowerPoint
MsgBox "New Ref successfully installed"

Exit Sub
CanNotAddPowerPoint:
MsgBox "Can not reference PowerPoint"

End Sub

Answer 1

It just occurred to me that you can use the Windows Installer objects to do this - it gives access to the information for pretty much anything that was registered with the MSI installers.

The first step is to locate the Office installation information. Unfortunately this is String based, so you need to filter the output. I used a simple Like comparison to glob match the Office version (Professional, Home, etc.). That's about the only thing that might require some tweaking. Note that while you can early bind to the Microsoft Windows Installer Object Library, it's pretty pointless for anything other than the initial development - you can't create a WindowsInstaller.Installer with Set whatever = New WindowsInstaller.Installer. You have to use CreateObject.

Private Function FindPowerPointPath() As String
    With CreateObject("WindowsInstaller.Installer")
        Dim prod As Variant
        For Each prod In .Products
            Dim id As String
            id = .ProductInfo(prod, "ProductName")
            If id Like "Microsoft Office * ####" Then
                Dim location As String
                location = FindPowerPointLibrary(.ProductInfo(prod, "InstallLocation"))
                If location <> vbNullString Then
                    FindPowerPointPath = location
                    Exit Function
                End If
            End If
        Next
    End With
End Function

The second step is pretty simple - just recurse subdirectories of the installation directory until you find a MSPPT.OLB:

Private Function FindPowerPointLibrary(startPath As String) As String
    With New Scripting.FileSystemObject
        Dim cwd As Scripting.Folder
        Set cwd = .GetFolder(startPath)
        Dim test As String
        test = .BuildPath(startPath, "MSPPT.OLB")
        If .FileExists(test) Then
            FindPowerPointLibrary = test
            Exit Function
        End If
        Dim subdir As Scripting.Folder
        For Each subdir In cwd.SubFolders
            Dim found As String
            found = FindPowerPointLibrary(subdir.Path)
            If found <> vbNullString Then
                FindPowerPointLibrary = found
                Exit Function
            End If
        Next
    End With
End Function

This should work for any Windows version (XP or later?). Note that this currently only returns the first instance found. If you're worried about the possibility of multiple Office version installations, you should be able to just verify that the returned path contains Office## where ## matches the major version of Application.Version.