Add VBA References Programmatically

Question

I am trying to correct a reference issue (references go missing due to different directories for different users) for various Avaya application versions. We have multiple versions running and need it to work with all possible versions, then give an error . I have created the below so far to define them inline in the beginning, but it keeps throwing a "Compile Error: Invalid Outside Procedure". I am new to coding and trying to add in this info to an already existing VBA Setup.

'Start Declarations
Dim cvsApp As New ACSUP.cvsApplication
Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim Rep As New ACSREP.cvsReport
Dim Info As Object, Log As Object, b As Object
Dim logged As Boolean

'Start My Code
If Dir("C:\Program Files (x86)\Avaya\CMS Supervisor R16", vbDirectory) <> 
vbNullString Then
    cvsApp.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS 
Supervisor R16\ascApp.exe"
    cvsConn.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R16\cvsCONN.dll"
    cvsSrv.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R16\ascSRV.exe"
    Rep.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R16\ascRep.exe"
    Catalog.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R16\cvsctlg.dll"
    Else
    If Dir("C:\Program Files (x86)\Avaya\CMS Supervisor R17", vbDirectory) <> vbNullString Then
        cvsApp.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R17\ascApp.exe"
        cvsConn.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R17\cvsCONN.dll"
        cvsSrv.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R17\ascSRV.exe"
        Rep.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R17\ascRep.exe"
        Catalog.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R17\cvsctlg.dll"
        Else
        If Dir("C:\Program Files (x86)\Avaya\CMS Supervisor R18", vbDirectory) <> vbNullString Then
            cvsApp.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R18\ascApp.exe"
            cvsConn.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R18\cvsCONN.dll"
            cvsSrv.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R18\ascSRV.exe"
            Rep.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS Supervisor R18\ascRep.exe"
            Catalog.References.AddFromFile "C:\Program Files (x86)\Avaya\CMS 
Supervisor R18\cvsctlg.dll"
            Else
                MsgBox ("ERROR: Avaya Files not found, Contact Admin")
        End If
    End If
End If

Sub that is called when button is pressed.

Sub Report_Run()

Call FixReference 'added this in - does it need to be?
Call Unhide
Call Clear_Report
Call Report_a
Call Hide

End Sub

Edit #2 - This is the original code i was given with references built on Tools section. Most of us have multiple servers up at once so it calls on a cms server order number (So 3rd server opened, etc). Setup window shown before code:

Setup
Start Date: 10/15/2017 End Date: 10/21/2017 Skills: **** Server order: 1 ACD: 6

Dim cvsApp As New ACSUP.cvsApplication
Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim Rep As New ACSREP.cvsReport
Dim Info As Object, Log As Object, b As Object
Dim logged As Boolean

Sub Clear_Report()

Sheets(Array("-1 (2)", "-2 (2)", "-3 (2)", "-4 (2)", "-5 (2)", "-6 (2)", "-7 (2)", _
    "-8 (2)", "-9 (2)", "-10 (2)", "-11 (2)", "-12 (2)", "-13 (2)", "-14 (2)", "-15 (2)", _
    "-16 (2)", "-17 (2)", "-18 (2)", "-19 (2)", "-20 (2)", "-21 (2)", "-22 (2)", "-23 (2)" _
    , "-24 (2)", "-25 (2)")).Select
Sheets("-1 (2)").Activate
Sheets(Array("-26 (2)", "-27 (2)", "-28 (2)", "-29 (2)", "-30 (2)", "-31 (2)", _
    "-32 (2)", "-33 (2)", "-34 (2)", "-35 (2)")).Select Replace:=False
Columns("A:AB").Select
Selection.ClearContents
Sheets("Auto").Select
End Sub

Sub Report_Run()
Call Unhide
Call Clear_Report
Call Report_a

End Sub



Public Sub Report_a()

Application.ScreenUpdating = 0


Call Move_down2


Sheets("Report Drop").Select
Range("b2:x4500").Select
Selection.ClearContents
Selection.Clear

Dim Serv As Integer
Dim Report As String
Dim Report2 As String
Dim B1 As String
Dim B2 As String
Dim B3 As String
Dim B1A As String
Dim B2A As String
Dim B3A As String
Dim ACDset As String
Dim B4 As String
Dim B4A As String
Dim Dropline As Integer

Serv = ThisWorkbook.Sheets("Auto").Cells(8, 2)
Report = ThisWorkbook.Sheets("Auto").Cells(2, 2)
'Report2 = ThisWorkbook.Sheets("Auto").Cells(51, 26)
'B1 = ThisWorkbook.Sheets("Auto").Cells(4, 2)
'B2 = ThisWorkbook.Sheets("Auto").Cells(5, 2)
'B3 = ThisWorkbook.Sheets("Auto").Cells(5, 26)
B1A = ThisWorkbook.Sheets("Auto").Cells(4, 3)
B2A = ThisWorkbook.Sheets("Auto").Cells(5, 3)
'B3A = ThisWorkbook.Sheets("Auto").Cells(8, 26)
Dropline = ThisWorkbook.Sheets("Auto").Cells(3, 2)


ACDset = ThisWorkbook.Sheets("Auto").Cells(9, 2)
Set cvsSrv = cvsApp.Servers(Serv) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call Report_b(Report, ACDset, B1, B2, B3, B1A, B2A, B3A, B4, B4A)
ThisWorkbook.Sheets("Report Drop").Cells(Dropline, 2).PasteSpecial
logout
Application.ScreenUpdating = 1

    Sheets("Report Drop").Select
Range("A1:ab5000").Select
Selection.Copy
Sheets("-1 (2)").Select
Range("A1").Select
ActiveSheet.Paste



Application.ScreenUpdating = 1
Call Rep2
Sheets("Output").Select
End Sub

Sub Report_b(sReportName As String, ByVal ACDset As String, ByVal B1 As String, B2 As String, B3 As String, B1A As String, B2A As String, B3A As String, B4 As String, B4A As String)

On Error Resume Next
cvsSrv.Reports.ACD = ACDset ' In server03 ACD1=AD2  ACD2=KC ACD3=Core
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not found on ACD 1"
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Rep.Window.Top = 0
Rep.Window.Left = 0
Rep.Window.Width = 0
Rep.Window.Height = 0


Debug.Print Rep.SetProperty("Split/Skill(s)", B1A)
Debug.Print Rep.SetProperty("Date(s)", B2A)
Debug.Print Rep.SetProperty("Times", "00:00-23:30")
'Debug.Print Rep.SetProperty(B4, B4A)
b = Rep.ExportData("", 9, 0, True, False, True)
Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
End If
Set Info = Nothing
End Sub

Sub Rep2()
If ThisWorkbook.Sheets("Auto").Cells(6, 3) = "Y" Then Call Report_a
Sheets("Output").Select
'Call Macro4
Sheets("Output").Select
End Sub

Sub Hide()
'
Sheets("Report Drop").Visible = False
Sheets("-1 (2)").Visible = False
Sheets("-2 (2)").Visible = False
..(And so on)
'
End Sub

Sub Unhide()
'
Sheets("Report Drop").Select
Sheets("-1 (2)").Visible = True
..(And so on)
'
End Sub

Sub logout()
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing
End Sub

Answer 1

Sorry, this is too much to put into a comment but it's not an "Answer". Try running this little procedure. Have the Immediate window open when you run it. Perhaps it will help you see what you need to do to fix your issue??

---

Note: if Dim oRef As Reference is not recognized:

• add reference to VBIDE: ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3
• trust the VBA project object model (Options > Trust Center > Macro Settings)

---

Public Sub getAppRef()
Dim oRef        As Reference
Dim sName       As String * 20
Dim sBroke      As String * 15
Dim sPath       As String * 150
Dim sVer        As String * 15
Dim sKind       As String * 10
Dim sGUID       As String * 45


    debug.print Now(), "Reference:"
    For Each oRef In Application.References
    'Clear Variables
        sName = ""
        sBroke = ""
        sPath = ""
        sVer = ""
        sKind = ""
        sGUID = ""
    'Populate Variables
        sName = "Name:" & oRef.Name
        sBroke = "IsBroken:" & oRef.IsBroken
        sPath = "Path:" & oRef.FullPath
        sVer = "Version:" & oRef.Major & "." & oRef.Minor
        sKind = "Kind:" & oRef.Kind
        sGUID = "GUID:" & oRef.Guid
        Debug.Print Now(), sName, sBroke, sVer, sKind, sGUID, sPath
    Next
End Sub

Answer 2

You can try replacing the 4 early binding declarations:

Dim cvsApp As New ACSUP.cvsApplication
Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim Rep As New ACSREP.cvsReport

with late binding and remove the Avaya CMS Supervisor references from the project:

Dim cvsApp As Object, cvsConn As Object, cvsSrv As Object, Rep As Object
Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")