1

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
Cœur
  • 37,241
  • 25
  • 195
  • 267
user2907651
  • 23
  • 4
  • Your code cannot be placed in the `declaration` section of a module. It has to reside in a `Sub` or `Function` `Sub FixReference() ...If...Then...End IF End Sub` – KacireeSoftware Nov 07 '17 at 23:28
  • Okay so now its showing missing objects due to the references. With the below line highlighted. So are my references named wrong? How i can see what the names should be to define them? Set cvsSrv = cvsApp.Servers(Serv) '(cvsApp.Servers.Count) '''''' we set a reference to the server here – user2907651 Nov 07 '17 at 23:46
  • 1
    You can use late binding by removing the references and types and `Set cvsApp = CreateObject("acsup.cvsApplication")` http://www.iaug.org/p/fo/et/thread=8700 – Slai Nov 08 '17 at 00:14
  • Okay maybe im missing something but that part i listed above (set cvsSrv) is inside of the report run. The part i am trying to correct is only the references not a complete rebuild but in adding in my scripting (I listed the declarations which were defined at the top of the script, are these causing isseus also?) it is causing errors down the line like its missing something else being defined. Now that the Sub issue is fixed that part of the code doesnt throw errors, just late in the script it does. Is there something else i need to define to have it load those references? – user2907651 Nov 08 '17 at 03:07
  • To clarify the `Set cvsApp = CreateObject("acsup.cvsApplication")` part is in the Report_a sub. I can add in the whole script if that will help but trying to learn as i go through also as this will have to be implemented on a bunch of other different reports (No one person made all of these.....) – user2907651 Nov 08 '17 at 03:12
  • ah I just noticed .. it should be `VBProject.References.AddFromFile` https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically and the declarations should be after adding the reference, but I am guessing it can still have issues if you Compile or Run the code with a later version of the references. – Slai Nov 08 '17 at 03:25
  • I can try converting it to late binding If you can post the rest of the code. with late binding there is no need to add reference, but you also lose the compile time type checking, and the variables have to be declared as Object. For example: `Dim cvsApp As Object : Set cvsApp = CreateObject("acsup.cvsApplication")`. To reply to a comment, you can add @ in front of their name for the person to get a inbox notification. – Slai Nov 08 '17 at 03:25
  • @Slai I have added in the original code. Apologies on any formatting issues. Pastebin is blocked. – user2907651 Nov 08 '17 at 03:41
  • oh so you already are using late binding for `Log`. It's the same for the first 4 variables cvsApp, cvsConn, cvsSrv, Rep, but you also have to uncheck the Avaya CMS Supervisor references from the project (Tools menu > References...) – Slai Nov 08 '17 at 03:58

2 Answers2

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")
Slai
  • 22,144
  • 5
  • 45
  • 53
  • I would do this in the Report_Run Sub or up in the declarations section where it was initially? - and do i need to keep the cvscrlg.dll (Catalog DLL) in at all? @Slai Edit - Putting in the declarations gives a compile error of "Invalid Outside Procedure" – user2907651 Nov 08 '17 at 04:08
  • @user2907651 in all places where you have early binding .cvs declarations such as for example `As New ACSUP.cvsApplication`. Also, no need for `Call FixReference` if you use late binding. – Slai Nov 08 '17 at 04:14
  • So i think i got all of them corrected. I searched the module for all ones we listed and changed them but now this line `ThisWorkbook.Sheets("Report Drop").Cells(Dropline, 2).PasteSpecial` is giving a 1004 error. when looking around it seems that is related to the range not being right but the reporting didn't change, only what applications I am referencing. So why would this start to pop an error? – user2907651 Nov 08 '17 at 04:51
  • you can comment out the `On Error Resume Next` line in `Sub Report_b` to see what the error is. If you are using the above 4 cvs variables only in `Sub Report_a`, I recommend declaring them only inside `Sub Report_a` instead of as global variables outside of Sub. You can also try compiling the project (Debug menu > Compile VBAProject) to see if that gets any errors. – Slai Nov 08 '17 at 05:09
0

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
SlowLearner
  • 3,086
  • 24
  • 54
KacireeSoftware
  • 798
  • 5
  • 19