Maliki Hassani
asked on
VBA: Compile error message
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?
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
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.
ASKER
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?
missing:ACSlog- error routing and logging
Missing: Report Component
Missing: Catalog Component
Have any ideas on how to add this? I have no idea?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
However, my new machine is using CMS supervisor R16...
Is this the reason? How can I recreate the pathway, or change the name?
ASKER
Okay I think I figured it out... One sec It's a matter of unchecking the box and finding the new pathway given..