Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

AVAYA CMS Scripting through VBA


	VB:
	
 
    Dim cvsApp As CVS.cvsApplication 
    Dim cvsConn As CVSCN.cvsConnection 
    Dim cvsSrv As CVSUPSRV.cvsServer 
    Dim Rep As CVSREP.cvsReport 
    Dim Info As Object, Log As Object, b As Object 
     
    Set cvsApp = New CVSUP.cvsApplication 
    If cvsApp.CreateServer(getusername(sServerIP), "", "", sServerIP, False, "ENU", cvsSrv, cvsConn) Then 
        If cvsConn.Login(getusername(sServerIP), getpassword(sServerIP), sServerIP, "ENU") Then 
             
            CMSGetReport = False 
             
            On Error Resume Next 
            cvsSrv.Reports.ACD = iACD 
            Set Info = cvsSrv.Reports.Reports(sReportName) 
            If Info Is Nothing Then 
                If cvsSrv.Interactive Then 
                    MsgBox "The Report " & sReportName & " was not found on ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor" 
                Else 
                    Set Log = CreateObject("CVSERR.cvslog") 
                    Log.AutoLogWrite "The Report " & sReportName & " was not found on ACD" & iACD & "." 
                    Set Log = Nothing 
                End If 
            Else 
                b = cvsSrv.Reports.CreateReport(Info, Rep) 
                If b Then 
                    Debug.Print Rep.SetProperty(sProperty1Name, sProperty1Value) 
                    Debug.Print Rep.SetProperty(sProperty2Name, sProperty2Value) 
                     '            Debug.Print Rep.SetProperty(sProperty3Name, sProperty3Value)
                    b = Rep.ExportData(sExportName, 9, 0, True, False, True) 
                    Rep.Quit 
                    CMSGetReport = True 
                    If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID 
                    Set Rep = Nothing 
                End If 
            End If 
            Set Info = Nothing 
        End If 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I got the above fuction from http://www.dbforums.com/archive/inde...t-1003776.html. What it does is, it extracts reports from AVAYA CenterVu Supervisor 9.0 software used by call centers.

But when I used it in excel VBA, it gives me compile error "User-defined type not defined in the first line "Dim cvsApp As CVS.cvsApplication" when I try to call the function see my code below.

More info:

I searched few files on my comp in the C:Program FilesAvayaCentreVu Supervisor 9.0 directory and go to know that

cvsAPP is an .exe file
cvsConn is a .dll file
cvsSrv is an .exe file
CVSREP is an .exe file

	VB:
	
 a() 
    Call CMSGetReport("172.18.3.60", 1, "Upstream Stats Rpt", "Date(s)", "03/01/06--1", "Split Numbers", "71;72;74;79;82;83", "m:ReportsAutomationabc.xls") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How can I get rid of the error?

Note: The above function might not display properly due to word wrap


Post your answer or comment

comments powered by Disqus
Private Function CMSGetReport(sServerIP As String, iACD As Integer,
sReportName As String, sProperty1Name As String, sProperty1Value As
String, sProperty2Name As String, sProperty2Value As String,
sExportName As String) As Boolean
Dim cvsApp As CVS.cvsApplication
Dim cvsConn As CVSCN.cvsConnection
Dim cvsSrv As CVSUPSRV.cvsServer
Dim Rep As CVSREP.cvsReport
Dim Info As Object, Log As Object, b As Object

Set cvsApp = New CVSUP.cvsApplication
If cvsApp.CreateServer(getusername(sServerIP), "", "", sServerIP,
False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(getusername(sServerIP), getpassword(sServerIP),
sServerIP, "ENU") Then

CMSGetReport = False

On Error Resume Next
cvsSrv.Reports.ACD = iACD
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on
ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("CVSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not
found on ACD" & iACD & "."
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty(sProperty1Name,
sProperty1Value)
Debug.Print Rep.SetProperty(sProperty2Name,
sProperty2Value)
' Debug.Print Rep.SetProperty(sProperty3Name,
sProperty3Value)
b = Rep.ExportData(sExportName, 9, 0, True, False, True)
Rep.Quit
CMSGetReport = True
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove
Rep.TaskID
Set Rep = Nothing
End If
End If
Set Info = Nothing
End If
End If
End Function

I got the above fuction from
http://www.dbforums.com/archive/inde...t-1003776.html. What it does
is, it extracts reports from AVAYA CenterVu Supervisor 9.0 software
used by call centers.

But when I used it in excel VBA, it gives me compile error
"User-defined type not defined in the first line "Dim cvsApp As
CVS.cvsApplication" when I try to call the function see my code below.

More info:

I searched few files on my comp in the C:Program FilesAvayaCentreVu
Supervisor 9.0 directory and go to know that

cvsAPP is an .exe file
cvsConn is a .dll file
cvsSrv is an .exe file
CVSREP is an .exe file

Sub a()
Call CMSGetReport("172.18.3.60", 1, "Upstream Stats Rpt", "Date(s)",
"03/01/06--1", "Split Numbers", "71;72;74;79;82;83",
"m:ReportsAutomationabc.xls")
End Sub

How can I get rid of the error?

Note: The above function might not display properly due to word wrap

I have some Script (a .bat file) that, when run in the directory that it exists in, will delete all "old versions" of files (i.e.- the program creating the files {ProEngineer}, on each save, creates a new "version" of the file with a .1,.2,.3,.4,etc... appended to the end of it).
The script in the file is as follows: Code:
@echo off

Set MC = unset
if "%PROCESSOR%" == "INTEL_64" set MC=ia64_nt
if "%PROCESSOR%" == "INTEL_486" set MC=i486_nt
if "%PROCESSOR%" == "MIPS_R4000" set MC=r4000_nt
if "%PROCESSOR%" == "Alpha_AXP" set MC=alpha_nt
if "%PROCESSOR_ARCHITECTURE%" == "IA64" set MC=ia64_nt
if "%PROCESSOR_ARCHITECTURE%" == "x86" set MC=i486_nt
if "%PROCESSOR_ARCHITECTURE%" == "MIPS" set MC=r4000_nt
if "%PROCESSOR_ARCHITECTURE%" == "ALPHA" set MC=alpha_nt
if not "%winbootdir%" == "" set mc=i486_win95
if not "%MC%" == "unset" goto mc_done
echo ERROR Cannot detect what machine type you have.
echo Please make one of the following settings:
echo.
echo set PROCESSOR=Alpha_AXP     - For Windows NT Alpha based machines.
echo.
echo set PROCESSOR=MIPS_R4000    - For Windows NT MIPS based machines.
echo.
echo set PROCESSOR=INTEL_486     - For Windows NT Intel based machines.
echo.
echo set PROCESSOR=INTEL_64      - For Windows Intel Itanium based machines.
echo.
echo set winbootdir=   - For Windows 95/98 machines.
echo.
exit
: mc_done

if NOT "%mc%" == "i486_win95" goto postenv4096
  if "%env4096%" == "true" goto postenv4096
    Set env4096 = True
    command.com /E:4096 /C %0 %1 %2 %3 %4 %5 %6 %7 %8 %9
    set env4096=
    GoTo ptc_end
: postenv4096

set PRO_MACHINE_TYPE=%MC%

if "%PROOBJ_START_DIRECTORY%" == "" set PRO_DIRECTORY=
if NOT "%PRO_DIRECTORY%" == "" "%PRO_DIRECTORY%bin%MC%_ptc_setvars" %0 "purge" bat
if "%PRO_DIRECTORY%" == "" %MC%_ptc_setvars %0 "purge" bat
Call ptc_setvars.bat
del ptc_setvars.bat

if NOT "%mc%" == "i486_win95" set start_cmd=start ""
if "%mc%" == "i486_win95" set start_cmd=start

set PRO_DIRECTORY=%PRODIR%
if NOT "%PTCPATH%" == "" goto ptcpathset

if NOT "%mc%" == "i486_win95" goto not95
Set Path = "%PRO_DIRECTORY%bin;%path%"
GoTo pathdone
: not95
set path=%PRO_DIRECTORY%bin;%path%
: pathdone

Set PTCPATH = True
: ptcpathset


"%PRODIR%%MC%objpurge.exe" %1 %2 %3 %4 %5
: ptc_end
Currently, I am doing this by manually copying the purge.bat file to the directory that I want purged and double clicking it. Or by using an added right click action in Windows Explorer "C:WINDOWSsystem32cmd.exe /c cd %1 &"C:Program FilesproeWildfire 3.0binpurge.bat" *"

I would like to run this .bat file (as well as perform many other actions) from Excel VBA to purge directories based off of a value in Excel. I have tried to use VBA to copy the existing purge.bat file into the directory and run it through the shell command but this does not seem to work.

Note: I do not know much about the script except for what it does and that it was installed with the program (ProE).

I had been advised in a previous post http://www.mrexcel.com/board2/viewtopic.php?t=262396 to code the BAT file to accept a parameter and use %1 in the BAT file to do something with the passed string such as a subfolder but I am not sure how this would be done.

Would it be possible to re-write the script through VBA code and execute it?

Hello,

I had a look on the internet and came across numerous scripts for FTP uploading scripts through VBA.

Could someone please recommend the best one taking into account these two critical factors:

1. Compatibility with most computer systems i.e. excel 03/07 win XP, Vista
2. Minimum visibility of the uploading process to the user

thanking you in advance,
tasos

Hi guys,

I'm totally stumped on this one. I'm trying to run a report from Avaya CMS using excel. I've been told it can be done but I can't find much on it in the Avaya forums apart from some code which purports to do what I want. The code is as follows:

Public Sub Single_CMS_Report_Extract()

On Error Resume Next

' Add the files specified below to the References section:
' Tools -> References -> Browse to the CMS directory,
' e.g.: "C:Program FilesAvayaCMS Supervisor R14"
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

Dim myLog As String, myPass As String, myServer As String
Dim reportPath As String, reportName As String, reportPrompt(1 To 2, 1 To 2) As String
Dim exportPath As String, exportName As String

' Assigns Variables
myLog = "my CMS log in"
myPass = "my CMS password"
myServer = "server address"
reportPath = "HistoricalDesigner"
reportName = "Agent Group Daily Summary Report"
reportPrompt(1, 1) = "Agent Group"
reportPrompt(1, 2) = "IPS Team View"
reportPrompt(2, 1) = "Dates"
reportPrompt(2, 2) = "-1"
exportPath = "C:Documents and SettingsMy Documents"
exportName = "Test.TXT"

' Open the CMS Application, launches acsApp.exe
' If a CMS Supervisor console is already open,
' the existing acsApp.exe is used.
Set cmsApplication = CreateObject("ACSUP.cvsApplication")
Set cmsServer = CreateObject("ACSUPSRV.cvsServer")
Set cmsConnection = CreateObject("ACSCN.cvsConnection")
cmsConnection.bAutoRetry = True

' Connects to the server, launches acsSRV.exe & ACSTrans.exe (2x)
If cmsApplication.CreateServer(myLog, myPass, "", myServer, False, "ENU", cmsServer, cmsConnection) Then
If cmsConnection.login(myLog, myPass, myServer, "ENU", "", False) Then
End If
End If

' Gets collection of Reports available on cmsServer
Set cmsCatalog = cmsServer.Reports
If cmsServer.Connected = False Then cmsServer.Reports.ACD = 1

' Sets parameters for report, launches ACSRep.exe (2x)
cmsCatalog.CreateReport cmsCatalog.Reports.Item(reportPath & reportName), cmsReport
If cmsReport.SetProperty(reportPrompt(1, 1), reportPrompt(2, 1)) And cmsReport.SetProperty(reportPrompt(1, 2),
reportPrompt(2, 2)) Then
End If

' Runs report and extracts results
cmsReport.ExportData exportPath & exportName, 9, 0, False, False, True

' Kills active report & server
If Not cmsServer.Interactive Then
cmsServer.ActiveTasks.Remove cmsReport.TaskID
cmsApplication.Servers.Remove cmsServer.ServerKey
End If

' Logs out
cmsReport.Quit
cmsConnection.Logout
cmsConnection.Disconnect
cmsServer.Connected = False

' Releases objects
Set cmsReport = Nothing
Set cmsCatalog = Nothing
Set cmsConnection = Nothing
Set cmsServer = Nothing
Set cmsApplication = Nothing

End Sub
I've changed the variables so you don't have my log in details!

I keep on getting an error saying that excel is waiting for another application to perform an OLE action. Has anyone come across this before? Not sure whether this is a problem with the code, excel or CMS. If it's the latter then I apologise for posting to this forum.

Thanks, Chris

heloo
i want to print all charts in my sheet
and i want to se the margins of the sheet to all equal to =2 cm
and no footer no header

how through vba?
by the way i use a sub to print a sheet and specifiy the printarea for it
and when i use that
ActiveSheet.ChartObjects.PrintOut preview:=True
an error appears
how can i fix that??

I have an activex checkbox control that I have tied to a cell ( by checking "move and size with cell" option ). If I copy this cell in design mode and paste it somewhere else the checkbox also gets pasted along with the cell value. But if I do this in run mode then checkbox is not copied.

The problem is, I need to do this operation through VBA, but copying pasting a cell through VBA always seem to be occuring in Run mode, since the checkbox isn't getting copied.
How can I copy this cell along with the checkbox through VBA.??

Hello Wiz,

I would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date.

I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.

I have attached a sample in case am confusing with my question.

Thanks a lot for looking at my post and I appreciate your time to look at my post.

Hi,

I am trying to automatically create pivot tables through VBA. My data sheet is called MASTER.
I tried to create a report sheet REPORTSH and then create pivot tables in that using the data in the master.

I tried to record the macro and got the code as follows :


	VB:
	
 
 
Sub Macro11() 
    Sheets("Master").Select 
    Sheets.Add.Name = "Reportsh" 
    Range("A1").Select 
    Sheets("Master").Select 
     
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    "Master!R1C1:R12900C8").CreatePivotTable TableDestination:= _ 
    "[Perfomancemgmt_AMZN.xls]Reportsh!R1C1", TableName:="Perftable1", _ 
    DefaultVersion:=xlPivotTableVersion10 
     
     ' I get error in the above line
     
     
    ActiveSheet.PivotTables("Perftable1").AddFields RowFields:=Array("Name", _ 
    "startdate", "Function Name", "Total Paid Hours"), ColumnFields:="Date", _ 
    PageFields:=Array("Employee Type", "Manager") 
    ActiveSheet.PivotTables("Perftable1").PivotFields("EachStowed").Orientation = _ 
    xlDataField 
    Range("A1").Select 
    ActiveWorkbook.ShowPivotTableFieldList = False 
    Application.CommandBars("PivotTable").Visible = False 
    With ActiveSheet.PivotTables("Perftable1").PivotFields("Employee Type") 
        .PivotItems("(blank)").Visible = False 
    End With 
    Range("A2").Select 
    With ActiveSheet.PivotTables("Perftable1").PivotFields("Manager") 
        .PivotItems("(blank)").Visible = False 
    End With 
    Columns("B:B").EntireColumn.AutoFit 
    Range("A4").Select 
    ActiveSheet.PivotTables("Perftable1").PivotFields("Count of EachStowed"). _ 
    Function = xlSum 
        Range("A5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Name").Subtotals = Array( _ 
        False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("B5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("startdate").Subtotals = _ 
        Array(False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("C5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Function Name").Subtotals = _ 
        Array(False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("D5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Total Paid Hours"). _ 
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _ 
        False, False) 
        Range("E4").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Date").Subtotals = Array( _ 
        False, False, False, False, False, False, False, False, False, False, False, False) 
        Columns("A:A").EntireColumn.AutoFit 
        Columns("B:B").EntireColumn.AutoFit 
        Columns("C:C").EntireColumn.AutoFit 
        Cells.Select 
        With Selection 
            .HorizontalAlignment = xlCenter 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 
         
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The problem is it is not creating the pivot and throwing up some error . Is there any other way to automate this.

Thanks in advance

Hi,

I have many worksheets which ends with the word " data" . Is there a way to delete all the data sheets at one go through VBA code.

Thanks
Newbie

i have a excel workbook which i do have calculations daily manualy. i want to do it through VBA
1. i have 10 products (each have different serial nos) that are being sold
2. i do calculate using counta
3. I have 4 other office(NM,CTD,KVP,GB) if transfered to them it should not be added to the net total( now manuly doing it)
4. sold Items must be transfered to 'sold' sheet with date and amt ( now manuly doing it)
5. also i have low level idicator but formating only helps in colour change in single cell. i dont know how to make a group of the cell change colour

anybody can help me in VBA?

thanks in advance

Hey Group
Can anyone recommend a way to access Bloomberg data (or to perform their 'Export to Excel') through VBA code ?

Hi, am looking to set a column width and column formats through VBA from an Access form. Got all the creation etc set up okay. Did a search on this site and found the following code from a previous query:

#xlBook.Sheets("Sheet1").Range("5:5").EntireColumn.Autofit#

Here I'm looking to format the width of column 5 only. No matter what values I put in the Range criteria it still formats the width of every column that data is entered into. Does anyone know why? In addition, I would like to format certain columns to say, Currency. Is it possible to do this from VBA?

Cheers, Lol

Hi all

I am pretty new to VBA.
Please help me in this :

how do i send an email to the someone through VBA/macro.?
can anyone please explain me how do i do that?

Thanks
Pavan

Hi there guys. I currently have a macro that checks to see if a specific file name is found in any of several different folders. The code I have works wonderfully.

However, one of the folders is now password protected. I have the username and password to access the folder. What I'm wanting to know is if someone can help me with the code to automatically enter that username and password so that the macro will continue to run without error.

When I open the folder location in explorer, I'm prompted for the username/password. How would I acheive this through VBA?

Thank you for any and all help.

Regards,
Johnnybeck

Hi,

Attached herewith a sample file. I have been trying to copy selected columns from a large spread sheet onto another sheet through vba. Is there a way to do it. Please suggest me.

Thanks a lot for looking into my post.

Hi everyone,

My question is a little odd, but I'll try to portray it to make sense. Basically, I want the user to be able to click a "Browse..." button, choose a file, and import it into an Access table. Now, I've got this part down pact for Excel files, but I'm having a problem with the CSV file types. Is there a way that when the user selects a CSV file, code can be written to save it as an Excel file through VBA and import that instead of the CSV file. Ideally, I'd like it to be able to do that and then delete the Excel file it made for the import purpose. I know I can use the TransferText method, but its giving me problems because I only need a certain range fro the CSV files. Any ideas?

Let me know if this needs more clarification.

-beveritt

How can you set page breaks through VBA? I need to set a page break every 65 rows and for it to stop at a named range ("Bottom")at the bottom of the report. Thanks for any assistance as I am new to VBA....

Does anyone know how to open MS Word through VBA? I am developing a print macro for my monthend binder, and I have one file that is a 20 page word doc. I would like to be able to throw that in the macro if possible. Any help is greatly appreciated. Thanks.

Hello,
I am working on a project, where I need to export data from excel to DB2 on host. I tried establishing connection to DB2 using ADO objects. But VBA isn't recognizing these as valid datatypes (Connection).

'Create a connection to DB2 database by using the IBM OLE DB provider for DB2
Public Function ConnectOLEDB() As ADODB.Connection
'set up connection string
Dim ConnectString As String
ConnectString = "Provider=IBMDADB2;DSN=DB239;User Id=;Password=;"

'Open new connection with client side cursor
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open ConnectString

'Return the connection
Set ConnectOLEDB = con

'Release the handler
Set con = Nothing

End Function

Can you please send a link to some manuals, where we have information on DB2 connectivity through VBA? I appreciate your help.

Thanks,
Jay.

Hi

I am trying to implement an audit trail through VBA. I currently have an excel file that resides on a network drive. I have users going to modify it on occasions. What i want is an audit trail, a log file to keep track who went in and modified what. I have gotten the following code from the ozgrid website.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Locked Then
        TrackFile = "C:Tracker.txt"
        TargUser = Application.UserName
        TargAddr = Sh.Name & "!" & Target.Address(False, False)
        TargVal = Target.Resize(1, 1).Text
        TargDate = Format(Now, "yyyy/mm/dd hh:mm")
        x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal
        Open TrackFile For Append As #1
        Print #1, x
        Close #1
    End If
End Sub
It works brillantly. It does however have flaws in that it will not track cell copied and pasted. Also people can go in merge a load of cells and the audit trail will not picked this up. I am juts enquiring has anyone come across a similar VBA audit trail?

I m trying to stop user to change Sheets in excel through VBA code .. i want that Sheet bar or sheet pane by which sheets are changed get disbale or by any means user cant change it .. i thought its a command bar but i couldnt find its name ...

any one can tell ???

I would like to assign a text value to a cell that has two different fonts, or different font sizes of the same font, whatever. I know that you can do this manually by clicking on the cell and highlighting the text you want changed. Is there a way to do this through VBA?? Essentially, I want to have a text value in a cell and under certain circumstances have a smaller value right next to it in parentheses. It isn't practical to have two columns for this.

It seems lilke checkboxes, option buttons, etc. from the Form toolbar are not accesible through VBA. True?

Thanks very much,
Brett

Hello!

I'd like to activate an Excel window (SAP) through VBA and then use
SendKeys to input the selection criteria for a certain report, and then
run the report. I think this will be easier than explaining how to run
the report.

So, how can I activate the window?

Thanks!
Mark

---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


No luck finding an answer? You could always try Google.