?
Solved

VBA:  Compile error message

Posted on 2010-11-29
5
Medium Priority
?
2,687 Views
Last Modified: 2012-08-14
Experts:

I have a Excel spreadsheet that retrieves info from CMS Supervisor 16, using VBA.  My other co workers can run the same spreadsheet and it will work perfect for them.  I recently got a new computer so I think that is where the problem is.

When I run the macro VBA gives me the following error:
---------------------------------
Compile Error
Can't find project or library
---------------------------------
and it is highlighting the following:
Dim cvsRepProp As cvsReport

Does anyone know what I need to do get this run correctly?
Option Explicit

'Dim cmsApplication As ACSUP.cvsApplication 'acsApp.exe
'Dim CMSserver As ACSUPSRV.cvsServer 'acsSRV.exe
'Dim cmsConnection As ACSCN.cvsConnection 'cvsconn.dll
'Dim cmsCatalog As ACSCTLG.cvsCatalog 'cvsctlg.dll
'Dim cmsReport As ACSREP.cvsReport 'acsRep.exe

'Module variables
Dim cvsApp As cvsApplication
Dim cvsConn As cvsConnection
Dim cvsSrv As cvsServer
Dim cvsConnected As Boolean
Dim cvsLoggedIn As Boolean
Dim cvsLog As cvsLog
Dim cvsRepInfo As Object 'cvsReportInfo
Dim cvsRepProp As cvsReport
Dim cvsRepCreated As Boolean
Dim cvsRepExported As Boolean
   
Sub Main()
    
    Dim EmailData As Variant
    Dim EmailDataItem As Integer
    Dim Reports As Variant
    Dim Headers As Variant
    Dim HeaderItemNum As Integer
    Dim ReportEntryNum As Integer
    Dim ReportDataNum As Integer
    Dim NumberReports As Integer
    Dim NumberDataColumns As Integer
    Dim Cnt1 As Integer
    Dim Cnt2 As Integer
    Dim MessageData As String
    Dim Response As String
    Dim ReportCMSserver As String
    Dim ReportCMSusername As String
    Dim ReportCMSpassword As String
    Dim ReportCurrentCMSusername As String
    Dim ReportCurrentCMSpassword As String
    Dim ReportName As String
    Dim ReportType As String
    Dim ReportInterval As String
    Dim ReportCMSReport As String
    Dim ReportGroupSkill As String
    Dim ReportDates As String
    Dim ReportTimes As String
    Dim LastRunColumn As String
    Dim LoginActive As Boolean
    Dim MailServerAddress, MailServerPort, MailUsername, MailPassword, MailSenderName, MailSenderAddress, MailTo, MailCc, MailSubject, MailTextBody As String
    
    Application.ScreenUpdating = False
    
    Sheets("Email Data").Activate
    Range("A1").Select
    NumberDataColumns = Range(Selection, Selection.End(xlToRight)).Count
    Debug.Print "Number of Email Data Columns: " & NumberDataColumns
    
    EmailData = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(0, NumberDataColumns - 1)).Value

    For EmailDataItem = 1 To NumberDataColumns
        Select Case EmailData(1, EmailDataItem)
            Case "Mail Server"
                MailServerAddress = EmailData(2, EmailDataItem)
            Case "Mail Server Port"
                MailServerPort = EmailData(2, EmailDataItem)
            Case "Mail User"
                MailUsername = EmailData(2, EmailDataItem)
            Case "Mail Password"
                MailPassword = EmailData(2, EmailDataItem)
            Case "Mail Sender Name"
                MailSenderName = EmailData(2, EmailDataItem)
            Case "Mail Sender Address"
                MailSenderAddress = EmailData(2, EmailDataItem)
            Case "Mail To"
                MailTo = EmailData(2, EmailDataItem)
            Case "Mail Cc"
                MailCc = EmailData(2, EmailDataItem)
            Case "Mail Subject"
                MailSubject = EmailData(2, EmailDataItem)
            Case "Mail Body"
                MailTextBody = EmailData(2, EmailDataItem)
            Case Else
                'Do Nothing
        End Select
    Next
    
    Sheets("Report Data").Activate
    Range("A1").Select
    NumberDataColumns = Range(Selection, Selection.End(xlToRight)).Count
    Debug.Print "Number of Report Data Values: " & NumberDataColumns
    
    Headers = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, NumberDataColumns)).Value
       
    Range("A2").Select
    NumberReports = Range(Selection, Selection.End(xlDown)).Count
    Debug.Print "Number of Report Entries: " & NumberReports
    
    Reports = Range(ActiveCell.Offset(NumberReports - 1, 0), ActiveCell.Offset(0, NumberDataColumns)).Value
    Range(ActiveCell.Offset(NumberReports - 1, 0), ActiveCell.Offset(0, NumberDataColumns)).Select
    
    Cnt1 = 0
    Cnt2 = 0
    
    ReportCMSserver = "XXXXXXXXXX"
    ReportCMSusername = "XXXXXXX"
    ReportCMSpassword = "XXXXXXX"
    
    'Check for Reports, Login into CMS, and Export
    If UBound(Reports) > 0 Then
        If Not cvsLoggedIn Then
            CMSLogin ReportCMSserver, ReportCMSusername, ReportCMSpassword
        End If
    
        If cvsLoggedIn Then
        
            'Loop the defined reports, collect data, and run
            For ReportEntryNum = 1 To UBound(Reports)
                MessageData = ""
                Cnt1 = Cnt1 + 1
                
                'MessageData = MessageData & Reports(ReportEntryNum, 1) & ": "
                        
                For ReportDataNum = 1 To NumberDataColumns
                    Cnt2 = Cnt2 + 1
                    
                    Select Case Headers(1, ReportDataNum)
                        Case "Report Name"
                            ReportName = Reports(ReportEntryNum, ReportDataNum)
                        Case "Report Interval"
                            ReportInterval = Reports(ReportEntryNum, ReportDataNum)
                        Case "Report Type"
                            ReportType = Reports(ReportEntryNum, ReportDataNum)
                        Case "CMS Report"
                            ReportCMSReport = Reports(ReportEntryNum, ReportDataNum)
                        Case "Group/Skill"
                            ReportGroupSkill = Reports(ReportEntryNum, ReportDataNum)
                        Case "Dates"
                            ReportDates = Reports(ReportEntryNum, ReportDataNum)
                        Case "Times"
                            ReportTimes = Reports(ReportEntryNum, ReportDataNum)
                        Case "CMS Server"
                            ReportCMSserver = Reports(ReportEntryNum, ReportDataNum)
                        Case "CMS Username"
                            ReportCurrentCMSusername = ReportCMSusername
                            ReportCMSusername = Reports(ReportEntryNum, ReportDataNum)
                            If ReportCurrentCMSusername <> ReportCMSusername Then
                                CMSLoginChange.txtMessage.Text = "Different login credentials are defined for this report (" & ReportName & _
                                    ")." & vbCr & vbCr & "Current User: " & ReportCurrentCMSusername & vbCr & "New User: " & ReportCMSusername & _
                                    vbCr & vbCr & "Re-initializing the CMS connection ..." & vbCr & vbCr & vbCr & _
                                    "Message will auto-close in 3 seconds."
                                CMSLoginChange.Show
                            End If
                        Case "CMS Password"
                            ReportCMSpassword = Reports(ReportEntryNum, ReportDataNum)
                        Case Else
                            'Do Nothing
                    End Select
                    
                    If Cnt2 > 1 Then
                        MessageData = MessageData & vbCr & Headers(1, ReportDataNum) & ": " & Reports(ReportEntryNum, ReportDataNum)
                    Else
                        MessageData = MessageData & Headers(1, ReportDataNum) & ": " & Reports(ReportEntryNum, ReportDataNum)
                    End If
                Next
                Cnt2 = 0
                
                'Response = MsgBox("Run the report defined below?" & vbCr & vbCr & "Report Data" & vbCrLf & "---------------" & vbCr & MessageData, vbQuestion + vbYesNo, "Run Report?")
                
                'If Response = vbNo Then
                    'Don't run the report
                    'MsgBox "The report (" & Reports(ReportEntryNum, 1) & ") has been cancelled." & vbCr & vbCr & "Report Data" & vbCrLf & "---------------" & vbCr & MessageData, vbInformation + vbOKOnly, "Run Report?"
                'Else
                    'Dim ReportStatus As Boolean
                    
                    'MsgBox "Calling Login: " & ReportCMSserver & ", " & ReportCMSusername & ", " & ReportCMSpassword, vbInformation + vbApplicationModal
                    'Call CMSLogin(ReportCMSserver, ReportCMSusername, ReportCMSpassword)
                    'MsgBox CMSLoginResult, vbInformation + vbApplicationModal + vbOKOnly, "Login Successful?"
                RunCMSReport ReportName, ReportType, ReportInterval, ReportCMSReport, ReportGroupSkill, ReportDates, ReportTimes
                    'CMSLogout True
                'End If
                
                ThisWorkbook.Sheets("Report Data").Activate
                LastRunColumn = Replace(Replace(Replace(ActiveSheet.Rows(1).Find("Last Run").Address, "1", ""), "$", ""), """", "")
                
                If cvsRepExported Then
                    Range(LastRunColumn & ReportEntryNum + 1).Value = Now
                    Range(LastRunColumn & ReportEntryNum + 1).Select
                Else
                    Range(LastRunColumn & ReportEntryNum + 1).Value = "Export Failed"
                    Range(LastRunColumn & ReportEntryNum + 1).Select
                End If
                
            Next
            
        End If
    
        If cvsLoggedIn Then
            CMSLogout True
        End If
    End If
    
    Application.ScreenUpdating = True
    
    Sheets("Report Data").Activate
    Range("A1").Select
        
    'Save the report as a new file and deliver via email
    SaveSendReport MailServerAddress, MailServerPort, MailUsername, MailPassword, MailSenderName, MailSenderAddress, MailTo, MailCc, MailSubject, MailTextBody
    
End Sub

Public Function CMSLogin(CMSserver As String, CMSusername As String, CMSpassword As String)
    Dim ServerID As Integer
    Set cvsApp = New cvsApplication
    Set cvsConn = New cvsConnection
    Set cvsSrv = New cvsServer
    cvsConnected = False
    
    'Check for an active server connection
    'For ServerID = 1 To cvsApp.Servers.Count
    '    Set cvsSrv = cvsApp.Servers(ServerID)
    '    If cvsSrv.ServerKey Like "*\" & CMSserver & "\" & CMSusername & "\*\*" Then
    '        cvsConnected = True
    '        cvsLoggedIn = True
    '        Exit For
    '    End If
    'Next ServerID

    'Initiate a connection to the server if not connected
    If Not cvsConnected Or Not cvsLoggedIn Then
        If cvsApp.CreateServer(CMSusername, CMSpassword, "", CMSserver, False, "ENU", cvsSrv, cvsConn) Then
            cvsConn.bAutoRetry = True
            If cvsConn.Login(CMSusername, CMSpassword, CMSserver, "ENU") Then
                cvsLoggedIn = True
            End If
        End If
    End If

    If cvsLoggedIn Then
        'Set ACD to number 1
        cvsSrv.Reports.ACD = 1
    End If
    
    'MsgBox CMSLoginResult, vbInformation + vbApplicationModal + vbOKOnly, "Login Result"
    
    CMSLogin = cvsLoggedIn
    
End Function
Public Sub CMSLogout(DoLogout As Boolean)
    On Error Resume Next
    
    If DoLogout = True Then
        If Not cvsSrv.Interactive Then
            cvsApp.Servers.Remove cvsSrv.ServerKey
        End If
        
        'There no active connection so a new non-interactive session was established
        If cvsConnected = False Then
            cvsConn.Logout
            cvsConn.Disconnect
            cvsSrv.Connected = False
        End If
    
        Set cvsConn = Nothing
        Set cvsSrv = Nothing
        Set cvsApp = Nothing
        
        cvsLoggedIn = False
        cvsRepCreated = False
        cvsRepExported = False
        
    End If

End Sub

Public Function RunCMSReport(RepName As String, RepType As String, RepInterval As String, RepLocation As String, RepGroupSkill As String, RepDates As String, RepTimes As String)
    
    Dim RepWorkbook As Workbook
    
    Set cvsRepProp = New cvsReport
    cvsRepExported = False
    
    'On Error Resume Next
    
    If cvsLoggedIn Then
            
        Set cvsRepInfo = cvsSrv.Reports.Reports(RepLocation)

        If cvsRepInfo Is Nothing Then
            If cvsSrv.Interactive Then
                MsgBox "The report " & RepLocation & " was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
            Else
                Set cvsLog = New cvsLog
                cvsLog.AutoLogWrite "The report " & RepLocation & " was not found on ACD 1."
                Set cvsLog = Nothing
            End If
        Else
            cvsRepCreated = cvsSrv.Reports.CreateReport(cvsRepInfo, cvsRepProp)
                
            If cvsRepCreated Then
                If RepType = "Agent Group" Then
                    cvsRepProp.SetProperty "Agent Group", RepGroupSkill
                End If
                    
                If RepType = "Splits/Skills" Then
                    cvsRepProp.SetProperty "Splits/Skills", RepGroupSkill
                End If
                    
                If RepInterval = "Single Day" Then
                    cvsRepProp.SetProperty "Date", RepDates
                ElseIf InStr(1, RepInterval, "Multiple Day", vbTextCompare) >= 1 Or InStr(1, RepInterval, "Multi Day", vbTextCompare) >= 1 Then
                    cvsRepProp.SetProperty "Dates", RepDates
                End If
                    
                If InStr(1, RepInterval, "Interval", vbTextCompare) >= 1 Then
                    cvsRepProp.SetProperty "Times", RepTimes
                End If
                   
                cvsRepExported = cvsRepProp.ExportData("", 9, 0, True, True, False)
                    
                Set RepWorkbook = ThisWorkbook
                RepWorkbook.Sheets(RepName).Select
                RepWorkbook.Sheets(RepName).Cells.Delete
                RepWorkbook.Sheets(RepName).Paste
                
                Dim NumberColumns As Integer
                Dim NumberRows As Integer
                
                If RepType = "Splits/Skills" Then
                    RepWorkbook.Sheets(RepName).Cells.Select
                    Selection.UnMerge
                    Selection.Borders.LineStyle = xlNone
                    
                    Range("A4").Select
                    NumberColumns = Range(ActiveCell, Selection.End(xlToRight)).Count
                    
                    Range("A1").Select
                    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, NumberColumns - 1)).Merge True
                    
                    Range("A4").Activate
                    Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-2, NumberColumns - 1)).Merge True
                    
                    Cells.Select
                    Cells.EntireColumn.AutoFit
                
                    Range("A1").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    
                    With Selection.Borders
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                ElseIf RepType = "Agent Group" Then
                    RepWorkbook.Sheets(RepName).Cells.Select
                    Selection.UnMerge
                    Selection.Borders.LineStyle = xlNone
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                    NumberRows = RepWorkbook.Sheets(RepName).Range(ActiveCell, Selection.End(xlDown)).Count
                    
                    RepWorkbook.Sheets(RepName).Range("A2").Select
                    NumberColumns = RepWorkbook.Sheets(RepName).Range(ActiveCell, Selection.End(xlToRight)).Count
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                    RepWorkbook.Sheets(RepName).Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, NumberColumns - 1)).Select
                    Selection.Merge True
                    Selection.HorizontalAlignment = xlLeft
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                    RepWorkbook.Sheets(RepName).Range(ActiveCell.Offset(NumberRows - 1, 1), ActiveCell.Offset(NumberRows - 1, NumberColumns - 1)).Select
                    Selection.Merge True
                    Selection.HorizontalAlignment = xlLeft
                    
                    RepWorkbook.Sheets(RepName).Cells.Select
                    RepWorkbook.Sheets(RepName).Cells.EntireColumn.AutoFit
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                    RepWorkbook.Sheets(RepName).Range(ActiveCell.Offset(NumberRows - 1, 0), ActiveCell.Offset(0, NumberColumns - 1)).Select
                    
                    With Selection.Borders
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                    
                    RepWorkbook.Sheets(RepName).Range("A1").Select
                End If
                
                Set RepWorkbook = Nothing
                
                cvsRepProp.Quit

                If Not cvsSrv.Interactive Then
                    If cvsSrv.ActiveTasks.Count > 0 Then
                        cvsSrv.ActiveTasks.Remove cvsRepProp.TaskID
                    End If
                End If
            End If
        End If
        
        Set cvsRepInfo = Nothing
    
    End If
    
    Set cvsRepProp = Nothing
    
    RunCMSReport = cvsRepExported
    
End Function

Sub SaveSendReport(vServerAddress, vServerPort, vUsername, vPassword, vSenderName, vSenderAddress, vTo, vCc, vSubject, vTextBody)
    'Saves the report data sheets to a new workbook and sends via email

    Dim strFileExt As String
    Dim numFileFormat As Long
    Dim wbSource As Workbook
    Dim wbSave As Workbook
    Dim strTempFilePath As String
    Dim strTempFileName As String
    Dim strAttachmentFile As String
    Dim strTextBody As String
    Dim sh As Worksheet
    Dim winActiveWindow As Window
    Dim winTempWindow As Window
    Dim i As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wbSource = ThisWorkbook

    'Copy the report data sheet to the new workbook
    'We add a temporary Window to avoid the Copy problem
    'if there is a List or Table in one of the sheets and
    'if the sheets are grouped

    'wbSource.Sheets(GetReportSheets).Copy
    With wbSource
        Set winActiveWindow = ActiveWindow
        Set winTempWindow = .NewWindow
        .Sheets(GetReportSheets).Copy
    End With

    'Close temporary Window
    winTempWindow.Close

    Set wbSave = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With wbSave
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            strFileExt = ".xls": numFileFormat = -4143
        Else
            'You use Excel 2007-2010, we exit the sub when your answer is
            'NO in the security dialog that you only see  when you copy
            'an sheet from a xlsm file with macro's disabled.
            If wbSource.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                'Select Case wbSource.FileFormat
                '    Case 51: strFileExt = ".xlsx": numFileFormat = 51
                '    Case 52:
                '        If .HasVBProject Then
                '            strFileExt = ".xlsm": numFileFormat = 52
                '        Else
                '            strFileExt = ".xlsx": numFileFormat = 51
                '        End If
                '    Case 56: strFileExt = ".xls": numFileFormat = 56
                '    Case Else: strFileExt = ".xlsb": numFileFormat = 50
                'End Select
                
                'Using xlsx as a static format
                strFileExt = ".xlsx": numFileFormat = 51
            End If
        End If
    End With


    '    'Change all cells in wbSave to values only
    '    For Each sh In wbSave.Worksheets
    '        sh.Select
    '        With sh.UsedRange
    '            .Cells.Copy
    '            .Cells.PasteSpecial xlPasteValues
    '            .Cells(1).Select
    '        End With
    '        Application.CutCopyMode = False
    '    Next sh
    '    wbSave.Worksheets(1).Select


    'Save the new workbook, mail, and delete
    strTempFilePath = Environ$("temp") & "\"
    strTempFileName = "NOC_CMS_Reports_" & Format(Now, "yyyymmdd-hhmmss")

    With wbSave
        .SaveAs strTempFilePath & strTempFileName & strFileExt, FileFormat:=numFileFormat
        .Close SaveChanges:=True
    End With

    strAttachmentFile = strTempFilePath & strTempFileName & strFileExt
    
    strTextBody = vTextBody
    
    If InStr(1, strTextBody, "<<REPORTDATE>>", vbTextCompare) Then
        strTextBody = Replace(strTextBody, "<<REPORTDATE>>", Format(Now, "mm/dd/yyyy hh:mm:ss"))
    End If
    
    If InStr(1, strTextBody, "<<ATTACHMENTNAME>>", vbTextCompare) Then
        strTextBody = Replace(strTextBody, "<<ATTACHMENTNAME>>", strTempFileName & strFileExt)
    End If
    
    If ThisWorkbook.Sheets("Run").chkEmailReport Then
        Debug.Print ThisWorkbook.Sheets("Run").chkEmailReport.Value
        'Call the procedure to email the report via CDO Mail
        EmailReport vServerAddress, vServerPort, vUsername, vPassword, vSenderName, vSenderAddress, vTo, vCc, vSubject, strTextBody, strAttachmentFile
    End If

    'Delete the file after sending, it's no longer needed
    Kill strTempFilePath & strTempFileName & strFileExt

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

Sub EmailReport(vServerAddress, vServerPort, vUsername, vPassword, vSenderName, vSenderAddress, vTo, vCc, vSubject, vTextBody, vAttachmentPath)

    'Define constants
    Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
    Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).
    
    Const cdoAnonymous = 0 'Do not authenticate
    Const cdoBasic = 1 'basic (clear-text) authentication
    Const cdoNTLM = 2 'NTLM
    
    Dim objMessage As Object
    Dim objConfig As Object
    Dim strBody As String
    Dim configFields As Variant

    Set objMessage = CreateObject("CDO.Message")
    Set objConfig = CreateObject("CDO.Configuration")
    
    objConfig.Load -1    ' CDO Source Defaults
    Set configFields = objConfig.Fields
    With configFields
        'Send via the local SMTP service (cdoSendUsingPickup = 1) or via remote SMTP server (cdoSendUsingPort = 2)
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
            
        'Name or IP of Remote SMTP Server
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = vServerAddress
            
        'Type of authentication, NONE, Basic (Base64 encoded), NTLM
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
            
        'Your UserID on the SMTP server
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = vUsername
            
        'Your password on the SMTP server
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = vPassword
            
        'Server port (typically 25)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = vServerPort
            
        'Use SSL for the connection (False or True)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
    
        'CDO SMTP server connection timeout in seconds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
            
        'Update the configuration information for the SMTP server.
        .Update
    End With
    
    With objMessage
        Set .Configuration = objConfig
        
        .From = Chr(34) & vSenderName & Chr(34) & "<" & vSenderAddress & ">"
        .To = vTo
        .CC = vCc
        .BCC = ""
        
        .Subject = vSubject
        .TextBody = vTextBody
        .AddAttachment vAttachmentPath
        
        .Send
    End With
    
    Set objMessage = Nothing
    Set objConfig = Nothing

End Sub

Sub TestFormMsgBox()
    CMSLoginChange.txtMessage.Text = "Different login credentials are defined for this report." & vbCr & vbCr & vbCr & _
        "Message will auto-close in 3 seconds."
    CMSLoginChange.Show
End Sub

Sub KillForm(FormName As String)
    Dim Form As Long
    For Form = VBA.UserForms.Count - 1 To 0 Step -1
        If VBA.UserForms(Form).Name = FormName Then
            Unload VBA.UserForms(Form)
            Exit For
        End If
    Next Form
End Sub

Sub FormatSplitSkillsReport()

    Cells.Select
    Selection.UnMerge
    Selection.Borders.LineStyle = xlNone
                    
    Range("A4").Select
    NumberColumns = Range(ActiveCell, Selection.End(xlToRight)).Count
                    
    Range("A1").Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, NumberColumns - 1)).Merge True
                    
    Range("A4").Activate
    Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-2, NumberColumns - 1)).Merge True
                    
    Cells.Select
    Cells.EntireColumn.AutoFit
                
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
                    
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
                    
    Range("A1").Select
    
End Sub

Sub FormatAgentGroupReport()
                    
    Dim NumberRows As Integer
    Dim NumberColumns As Integer
                    
    Cells.Select
    Selection.UnMerge
    Selection.Borders.LineStyle = xlNone
                    
    Range("A1").Select
    NumberRows = Range(ActiveCell, Selection.End(xlDown)).Count
                    
    Range("A2").Select
    NumberColumns = Range(ActiveCell, Selection.End(xlToRight)).Count
                    
    Range("A1").Select
    Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, NumberColumns - 1)).Select
    Selection.Merge True
    Selection.HorizontalAlignment = xlLeft
                    
    Range("A1").Select
    Range(ActiveCell.Offset(NumberRows - 1, 1), ActiveCell.Offset(NumberRows - 1, NumberColumns - 1)).Select
    Selection.Merge True
    Selection.HorizontalAlignment = xlLeft
                    
    Cells.Select
    Cells.EntireColumn.AutoFit
                    
    Range("A1").Select
    Range(ActiveCell.Offset(NumberRows - 1, 0), ActiveCell.Offset(0, NumberColumns - 1)).Select
                    
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
                    
    Range("A1").Select
    
End Sub


Function GetReportSheets(Optional vNumberOfReports As Integer)
    Dim aReportSheets As Variant
    Dim i, NumberReports As Integer
    
    ThisWorkbook.Sheets("Report Data").Select
    
    If IsNull(vNumberOfReports) Or vNumberOfReports < 1 Then
        ActiveSheet.Range("A2").Select
        Debug.Print Selection.Address
        NumberReports = ActiveSheet.Range(Selection, Selection.End(xlDown)).Count
        Debug.Print NumberReports
        vNumberOfReports = NumberReports
        Debug.Print vNumberOfReports
    End If
    
    ReDim aReportSheets(1 To vNumberOfReports)
    'For i = 1 To vNumberOfReports
    '    aReportSheets(i) = ThisWorkbook.Sheets(i).Name
    '    Debug.Print aReportSheets(i)
    'Next
    
    aReportSheets = Application.Transpose(Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(vNumberOfReports - 1, 0)).Value)
    
    'For i = 1 To vNumberOfReports
    '    Debug.Print aReportSheets(i)
    'Next
    
    GetReportSheets = aReportSheets
End Function

Open in new window

0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 34233161
cvsReport is not a standard variable type.  So it's either from a missing class module with that name, or a dll that you were using.  If it's a dll, then press Alt + F11 to open the VBE and click on Tools --> References.  See if you see anything "MISSING" in there.  If so, you'll have to add that dll to the new machine or re-point it to the correct new location.
0
 

Author Comment

by:Maliki Hassani
ID: 34233220
Okay I see that I am missing:

missing:ACSlog- error routing and logging
Missing: Report Component
Missing: Catalog Component
Have any ideas on how to add this?  I have no idea?
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 2000 total points
ID: 34233271
>>Have any ideas on how to add this?  I have no idea?

You have to point the references to the correct dll.  You said this, "My other co workers can run the same spreadsheet and it will work perfect for them.", so go to one of their machines and check the path to the file.  Then use that same path for your machine.  If your machine doesn't have the dll, then copy it from your co-workers machine.
0
 

Author Comment

by:Maliki Hassani
ID: 34233364
I see a location pathway that shows:  C:\Program Files\Avaya\CMS Supervisor R14\CVSLog.dll

However, my new machine is using CMS supervisor R16...

Is this the reason?  How can I recreate the pathway, or change the name?
0
 

Author Comment

by:Maliki Hassani
ID: 34233378
Okay I think I figured it out...  One sec  It's a matter of unchecking the box and finding the new pathway given..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question