Solved

VBA:  Compile error message

Posted on 2010-11-29
5
2,523 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
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SUM every other column 10 35
problem with vb editor in excel 2016 10 24
Excel formula 6 20
Formula to copy cell and its "format" 3 23
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now