Issues with Access and AdobePDFMakerForOffice

[Ancalima]

Ok so here's my issue. My department at my company seems unable to achieve consistency in software across our PCs, (ie. Some users have Acrobat Standard 7.0 installed, others have 8.0 and still others have 9.0, and others do not have Acrobat Standard installed at all). This is causing issues in my coding which would work in one version but not necessarily all of them. I have been trying to achieve code that will work regardless of the version of acrobat installed on the user's PC, but it is fighting me every step of the way. We all are using Office 2003 and this code is being run from an Access database. The code I am having issues with is below.

Public Sub ExportExcelToPDF(ByRef xlw As Excel.Workbook, strFileName As String)
    Dim PDFApp As Object
    Dim PDFSettings As Object
    Dim IndX As Long
    Dim Acro7 As Boolean
    
    If Dir(TEMP_FOLDER, vbDirectory) = "" Then MkDir TEMP_FOLDER
    
    xlw.SaveAs TEMP_FOLDER & "temp.xls"
    
    With xlw.Application
        Do
            IndX = IndX + 1
            If IndX > .COMAddIns.count Then Exit Do
        Loop Until .COMAddIns(IndX).Description = "Acrobat PDFMaker Office COM Addin"
        
        If IndX > .COMAddIns.count Then
            MsgBox "Unable to locate Acrobat PDFMaker COM Add-In for Microsoft Office." & vbCrLf & "Please contact an MIS associate for assistance", vbCritical, "Cannot Export PDF"
        Else
            Set PDFApp = .COMAddIns(IndX).Object
            If Dir(Left(Environ("WinDir"), 3) & "Program Files\Adobe\Acrobat 9.0\", vbDirectory) = "" Then Acro7 = True
            
            If Acro7 Then
                Set PDFSettings = PDFApp.GetCurrentConversionSettings
            Else
                PDFApp.GetCurrentConversionSettings PDFSettings
            End If
            
            With PDFSettings
                .IsConversionSilent = True
                .IsAutomation = True
                .ShouldShowProgressDialog = False
                If Not Acro7 Then .PromptForSheetSelection = False
                .AddTags = False
                .ConvertComments = False
                .AddBookmarks = False
                .AddLinks = False
                .OutputPDFFileName = strFileName
                .PromptForPDFFilename = False
                .ViewPDFFile = False
                
                .AttachSourceFile = False
            End With
            
            If Acro7 Then
                PDFApp.CreatePDFEx PDFSettings
            Else
                PDFApp.CreatePDFEx PDFSettings, IndX
            End If
        End If
        
        .Quit
    End With
    
    Set PDFApp = Nothing
    Set PDFSettings = Nothing
    
    If Dir(TEMP_FOLDER & "temp.xls") <> "" Then
        Kill TEMP_FOLDER & "temp.xls"
    End If
End Sub

What this code is trying to do is take an excel workbook, which is created in an earlier part of the code and convert it to a PDF using the AdobePDFMakerForOffice Add-In for Access. When i try to run the code above on a machine which has Acrobat 7.0 installed then it works perfectly fine. When I try to run it on a machine with 8.0 or 9.0 installed then i receive the error:

"Variable uses an automation type not supported in visual basic."

On the line:

PDFApp.CreatePDFEx PDFSettings, IndX

If i change the line:

Dim PDFApp As Object

to:

Dim PDFApp As AdobePDFMakerForOffice.PDFMaker

and add a reference to AdobePDFMakerForOffice then the code will work on all machines which have acrobat installed but it will fail with a missing reference error if any user opens the database who does not have acrobat installed on their PC. It is important to be able to use this database even if the user does not have acrobat installed as they are also given the option to simply create the excel workbooks by themselves. I even tried code that would test for missing references and remove them if any are found. The code I was using is below.

Public Sub CheckReferences()
    Dim Ref As Access.Reference
    Dim count As Integer
    
    For count = Application.References.count To 1 Step -1
        Set Ref = Application.References(count)
        If Not Ref.BuiltIn Then If Ref.IsBroken Then Application.References.Remove Ref
    Next
End Sub

However this code fails on the section:

Application.References.Remove Ref

with the error:

"Object library not registered"

If anybody could assist with this issue I would greatly appreciate it. Also, our machines are pretty heavily secured so manual manipulation of registry keys is very limited. (HKEY_LOCAL_MACHINE is pretty much inaccessible.) Thanks in advance for anyone who can help.