VBA from notepad

[Mahesh]

Instead of writing a vba code from VB within excel, can I write it in notepad?

If yes, if I run that notepad file will it work exactly like I am running the code within excel?

 

[Oorang]

You can definatly write it in notepad. Not sure if there is a way to run it. Might be able to automate importing it then executing it... Why would you want to though?

 

[AgentSmith]

There is nothing to stop you writing VBA in any text editor you want. However, AFAIK you will need to copy and paste it into a proper Excel module before it will work.

Are you trying to write some VB-style code that will run stand-alone (ie. without requiring Excel to be open)? If so then you might want to look up VBScript. There's a useful reference at http://www.devguru.com

 

[Mahesh]

Vbscript and Excel VBA

Following is the script from AVAYA CMS CenterVu Supervisor that opens CMS, runs a report and creates an excel file in the path specified. I think this code is in VBScript. What I want is, when this code is executed It should start executing a Excel VBA Macro without any manual intervention. I don't want to stop here and then go to Excel and run the VBA separately.

The other option would be, have the below code converted into Excel VBA and then I will continue running the other VBA macro. I can't do the converting part and if I paste the below code into Excel module, I get an error Argument not optional in the line "Set Log = CreateObject("AVSERR.cvsLog")"

'LANGUAGE=ENU
'SERVERNAME=xx.xx.xx.xx
'## cvs_cmd_begin
Public Sub Main()

On Error Resume Next
    cvsSrv.Reports.ACD = 1
    Set Info = cvsSrv.Reports.Reports("Historical\CMS custom\Custom Multi/Split")
    If Info Is Nothing Then
        If cvsSrv.Interactive Then
            MsgBox "The report Historical\CMS custom\Custom Multi/Split was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
        Else
            Set Log = CreateObject("AVSERR.cvsLog") 
            Log.AutoLogWrite "The report Historical\CMS custom\Custom Multi/Split was not found on ACD 1."
            Set Log = Nothing
        End If
    Else
        b = cvsSrv.Reports.CreateReport(Info,Rep)
        If b Then
            Rep.SetProperty "Split(s)","71"
            Rep.SetProperty "Date","0"
            Rep.SetProperty "Times","0-12:59"
            b = Rep.ExportData("M:\Maxi\abc.xls", 9, 0, True, True, True)
            Rep.Quit
            If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
            Set Rep = Nothing
        End If
    End If
Set Info = Nothing

'## cvs_cmd_end
End Sub
[code]

 

[AgentSmith]

CreateObject is normally a VBScript command, but VBA gives you a different way to do a simiar thing. In Excel I think you need to add a reference to whatever this "AVSERR" thing is (presumably a DLL file or something?). Then you might be able to change the code to something like this:

Dim Log as cvsLog
...
Set Log = New cvsLog
...
Set Log = Nothing

 

[Mahesh]

It gives an error
User-defined type not defined

 

[AgentSmith]

Did you add a reference to this DLL (or whatever it is) file?