Run VBScript Module

This module will run a VBScript or ECMAScript / JScript.

Result Variables can be used throughout the script. 

After starting the script VoiceGuide can:

Continue, without waiting for script to complete.
Wait until script completes.
Wait until script and sound file completes.

 

Continue, without waiting for script to complete option

VoiceGuide will immediately continue down the "Success" path once script was started successfully. If script could not be started successfully then the "Fail" path is taken. If the path that VoiceGuide should be following is not defined then VoiceGuide will hang up the call.

 

Wait until script completes option

VoiceGuide will wait until the script finishes running, or a response from the executing script is received.

Script can send responses back to VoiceGuide while it is executing by calling one VoiceGuide's ActiveX/COM functions. The functions which are considered to return a result back to VoiceGuide are: Run_ResultReturn(), Script_Gosub(), Script_Goto(), Script_Return(). For more information on VoiceGuide's ActiveX/COM interface please see the COM Interface section of Help file.

Once VoiceGuide detects that a script has completed and no COM response was received beforehand then VoiceGuide will see if a "Result File" has been created by the script. If one has been created then VoiceGuide will read in it's contents and then determine what to do next based on the contents of the file.

The syntax of the Result File is the same as that used by the Run Program module. Please refer to the Run Program module Help file's section for more information.

A "Success" or Result Variable list must be returned to VoiceGuide (either though a Run_ResultReturn COM function or a Result File) in order for it to go down the Success path.
If no result is returned then the "Fail" path is taken after the VBScript completes.
Calls to Script_Gosub(), Script_Goto() and Script_Return() functions result in immediate running of the next module.

Any sound files still playing when VBScript completes will be stopped.

 

Wait until script and sound file completes option

VoiceGuide will wait until the script finishes running (or a response from the executing script is received) and until the sound file playing completes. Sound file to play can be specified either using the "sound file to play" text box or can be started from with the script itself using the Play_Start COM function.

Other functionality is similar to the "Wait until script completes" option above.

 

Limiting maximum execution time

To limit the length of time the script is allowed to run for, a Timeout Path should be defined. If the script does not finish before the timeout occurs the script will be terminated and the timeout path will be taken. The timeout value is in seconds and should not be set to 0 - a value of 0 will result in the script being aborted immediately after it is started, without giving it any chance to run. Minimum timeout value used should be 2 seconds.

 

Play Tab

If "Wait Until script Finishes" option is selected, the sound file specified in the Play tab will be played while the started program is executing.

 

Writing VBScript

A pretty good book on VBScript is: "VBScript in a Nutshell" by Ron Petrusha, Matt Childs, Paul Lomax.

VBScript editors which you can use to develop your scripts before moving them over the the Run VBScript module can be found at: www.vbsedit.com.

Following are some examples of VBScripts which can be ran in the Run VBScript module. Extensive use of VBScripting is also made by the VoiceGuide's Voicemail system. See VoiceGuide's \system\vm\ directory.

 

Using ECMAScript / JScript instead of VBScript

To have the module execute JScript the first line of the script must begin with this:

//ECMAScript or

//JScript

This indicates to VoiceGuide that the contents of this module are JScript and not the default VBScript.

 

 

Using Result Variables in VBScripts

Before running the VBScript VoiceGuide will first see if there are any Result Variables specified within the script, and if there are then VoiceGuide will first replace them with their current values and run the resulting script then.

If the Result Variable is not defined then VoiceGuide will replace it with an empty string. For this reason it is often desirable to have the Result Variables used with quotes specified around them.

Eg: If Caller ID information did not arrive then VoiceGuide will replace $RV_CIDNUMBER  with an empty string before letting the VBScript run.

So, if Caller ID information did not arrive this code:

If $RV_CIDNUMBER = "" Then
   CallerID = "Unknown"
Else
   CallerID = $RV_CIDNUMBER
End if

would be changed to

If = "" Then
   CallerID = "Unknown"
Else
   CallerID =
End if

Which you can see is not valid VBScript and hence and error will occur.

But if you write the code using quotes around the RVs, like this:

If "$RV_CIDNUMBER" = ""
   Then CallerID = "Unknown"
Else
   CallerID = "$RV_CIDNUMBER"
End if

and Caller ID information did not arrive then the resulting VBScript which VoiceGuide actually runs would be:

If "" = "" Then
   CallerID = "Unknown"
Else
   CallerID = ""
End if

Which is a valid VBScript and when running it no errors will be generated and the script will work as expected.

Result Variables should not be directly used in places where replacement with a blank would cause a syntax error. Instead a variable which gets set to the RV value beforehand should be used in those situations. That way there is no syntax error and the check for whether RV holds any data can be made earlier in the script and the appropriate action taken if RV does not hold a value. Notes:

After the Result Variables have been replaced in the VBScript, the new VBScript can be optionally saved in VoiceGuide's \temp\ sub-directory, named as vbs_LineId_ThisCallScriptIndex.vbs Viewing this file will allow you to confirm that Result Variable replacement was done correctly. Saving to .vbs file can be set in VG.INI, in [moduleRunScript] section.

VBScript .vbs files can be executed by by using the Windows' wscript.exe utility.

On 32-bit systems the .vbs files can be ran by just double clicking on them.

On 64-bit systems please open a command prompt (may need to be an 'Administrator Command Prompt') then change directory to the VoiceGuide \temp\ sub-directory and run this command line:

C:\Windows\SysWow64\WScript.exe vbs_x_y.vbs

Where vbs_x_y.vbs is the filename of the .vbs file which you wish to run.

VoiceGuide is 32 bit software so any VBScripts that use the VoiceGuide COM interface need to be ran using the 32 bit version of VBScript interpreter.

On 64-bit systems if you change the Registry Key Computer\HKLM\SOFTWARE]\Classes\VBSFile\Shell\Open\Command to use the 32-bit C:\Windows\SysWow64\WScript.exe then the .vbs files can be ran by just being double-clicked.

 

Example : Save information to a file

The VBScript below will append a line of text to the file C:\LogCall.txt - the line of text will contain information about start of the call, caller's telephone number and information entered by caller in module "EnterClientNumber". Result Variables are used in this script to allow information from VoiceGuide to be visible to the VBScript.

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
set fso = CreateObject("Scripting.FileSystemObject")
set tsFile = fso.OpenTextFile("C:\LogCalls.txt", ForAppending, True)
tsFile.WriteLine "
$RV_STARTTIME, $RV_CIDNUMBER, $RV[EnterClientNumber]"
tsFile.Close
set tsFile = Nothing
set fso = Nothing

 

Example : Read information from a file

The VBScript below will read the contents of file C:\CurrentPrices.txt and will assign them to VoiceGuide Result Variable $RV[ReadInPrice].

set fso = CreateObject("Scripting.FileSystemObject")
set fileUnitPrice = fso.OpenTextFile("C:\CurrentPrice.txt")
sEntireFile = fileUnitPrice.ReadAll
set fileUnitPrice = Nothing  'always deallocate after use...
set fso = Nothing
set vg = CreateObject("vgServices.CommandLink")
vg.RvSet $RV_LINEID, "ReadInPrice", sEntireFile
vg.Run_ResultReturn $RV_LINEID, "success"
set vg = Nothing 'always deallocate after use...

 

Example : Read information from Excel

The VBScript below retrieves information from an Excel spreadsheet.

Dim xlApp, xlBook, xlSht 
Dim filename, value1, value2, value3, value4

filename = "c:\Warehouse.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

value1 = xlSht.Cells(2, 1)
value2 = xlSht.Cells(2, 2)

'the MsgBox line below would be commented out in a real application
'this is just here to show how it works...

msgbox "Values are: " & value1 & ", " & value2 

xlBook.Close False
xlApp.Quit

'always deallocate after use...
set xlSht = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
 

 

Example: Save information to Excel

The VBScript below saves information to an Excel spreadsheet.

Dim xlApp, xlBook, xlSht 
Dim filename, value1, value2, value3, value4

on error resume next

filename = "c:\warehouse.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

xlApp.DisplayAlerts = False

'write data into the spreadsheet
xlSht.Cells(2, 2) = "New Data"

xlBook.Save
xlBook.Close SaveChanges=True
xlApp.Close
xlApp.Quit

'always deallocate after use...
set xlSht = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

 

Example : Creating Result File

The VBScript below demonstrates how the $RV_LINEID Result Variable is used to generate a Result file from which the data is read back into VoiceGuide. Please note that using the COM function Run_ResultReturn() is a preferable way of returning information to VoiceGuide (it's faster) - but a result file can be used if there is no other way.

Dim iIndexDow, iIndexNasdaq, iIndexSP500

'Do some work here to retrieve the data and initialize
'the iIndexDow, iIndexNasdaq and iIndexSP500 variables

strResultVariables= "[IndexDow]{" & iIndexDow & "}" & _
"[IndexNasdaq]{" & iIndexNasdaq & "}" & _
"[IndexSP500]{" & iIndexSP500 & "}"

iRet = WriteResultFile(strResultVariables)

Function WriteResultFile(strResult)
  Const ForReading=1, ForWriting=2, ForAppending=8
  filename = "VGRUNRESULT_$RV_LINEID.TXT"
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.OpenTextFile(filename, ForWriting, True)
  ts.WriteLine(strResult)
  ts.Close
  WriteResultFile=0

  'always deallocate after use...
  set ts = Nothing
  set fso = Nothing
end function


It is recommended that the full path to the result file be specified, otherwise the Windows' current 'default' path will be used by the file subsystem - and that does not always point to the same path as the script's.

 

Example : MS Access Database read/write

Retrieving data from an MS Access database using an SQL query, and then updating the same record with new values:

'ADO related const values from ADOVBS.INC file, usually found in: C:\Program Files\Common Files\System\ado
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'other related const values
const vbGeneralDate = 0
const vbLongDate = 1
const vbShortDate = 2
const vbLongTime = 3
const vbShortTime = 4

set vg = CreateObject("vgServices.CommandLink")
set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\Ads.mdb"

if cn.State = 1 then
  vg.Admin_TraceLogAdd $RV_LINEID, 5, "vbs   connection to Ads.mdb made OK"
  set rs.ActiveConnection = cn
  rs.Open "SELECT TOP 1 AdID, Filename, PlayCount FROM AdList WHERE PlayCount < PlayCountMax ORDER BY LastPlayTime", cn, adOpenStatic

  if rs.RecordCount > 0 then
    vg.Admin_TraceLogAdd $RV_LINEID, 5, "vbs   records found OK"
    iAdID = rs.Fields("AdID").Value
    sFilename = rs.Fields("Filename").Value
    iPlayCount = rs.Fields("PlayCount").Value

    'Do some other processing here. eg: vg.Play_Start $RV_LINEID, sFilename

    'now update the database
    iPlayCount = iPlayCount + 1
    strDateTime = "#" & FormatDateTime(Now, vbShortDate) & " " & FormatDateTime(Now, vbLongTime) & "#"
    cn.Execute "UPDATE AdList SET PlayCount=" & iPlayCount & ", LastPlayTime=" & strDateTime & " WHERE AdID=" & iAdID
  else
    'no records could be retrieved, we can specify to play something instead here
    vg.Admin_TraceLogAdd $RV_LINEID, 5, "vbs   no records found"
  end if

else
  vg.Admin_TraceLogAdd $RV_LINEID, 5, "vbs   connection to Ads.mdb could not be made"
end if

cn.Close
Set rs = Nothing
Set cn = Nothing
Set vg = Nothing

 

Example : MS SQL Server Database read/write

A script like this can be used :

set vg = CreateObject("vgServices.CommandLink")
set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=SQLOLEDB;Server=DBSERVER1;UID=user;PWD=user;Database=Ads" 'MSSQL authentication

if cn.State <> 1 then
  vg.Admin_TraceLogAdd iLineId, 5, "login LeadingAd connection to database could not be made"
  vg.Run_ResultReturn iLineId, "fail"
else

  set rs.ActiveConnection = cn
  sSQL = "SELECT TOP 1 AdID, Filename, PlayCount, LastPlayTime FROM AdList WHERE   PlayCount < PlayCountMax AND Active <> 0 ORDER BY LastPlayTime"
  rs.Open sSQL, cn, 3

  if rs.RecordCount <= 0 then
    rs.Close
    vg.Admin_TraceLogAdd iLineId, 5, "no records retrieved"
  else
    iAdID = rs.Fields("AdID").Value
    sFilename = rs.Fields("Filename").Value
    iPlayCount = rs.Fields("PlayCount").Value
    dateLastPlayTime = rs.Fields("LastPlayTime").Value
    rs.Close
 
    sSQL = "UPDATE AdList SET PlayCount=" & iPlayCount & ", LastPlayTime=" &     strDateTimeNow & " WHERE AdID=" & iAdID
    vg.Admin_TraceLogAdd iLineId, 5, "sql=[" & sSQL & "]"
    cn.Execute sSQL

    sSQL = "INSERT INTO PlayLog (AdID, CallID, PlayDateTime, PlayedFrom)     VALUES (" & iAdID & ", 0, " & strDateTimeNow & ", 'AnswerCall')"
    vg.Admin_TraceLogAdd iLineId, 5, " sql=[" & sSQL & "]"
    cn.Execute sSQL
  end if

  cn.Close
end if
set rs = Nothing
set cn = Nothing
set vg = Nothing

 

Example : Stored Procedure defined in the database (no parameters).

Let's assume that you have defined in your database a stored procedure names RetrieveUserNames that returns a recordset but takes no parameters.  (note this example assumes that the database connection cn and VG.CommandLink are defined outside this function) The following code will retrieve the records.

set cmd = CreateObject("ADODB.Command")
set rs = CreateObject("ADODB.Recordset")
With cmd
   .ActiveConnection = cn
   .CommandType = adCmdStoredProc
   .CommandText = "RetrieveUserNames"
   Set rs = .Execute
   'you can now access the individual fields in the recordset rs
End With
Set cmd = Nothing
Set rs = Nothing

 

Example : Stored Procedures defined in the database (with parameters).

Let's assume that you have defined in your database a parameterized stored procedure query named UpdateUser as follows:

UPDATE USERS
SET USERS.FIRSTNAME = [inFirstName], USERS.MIDDLEINITIAL = [inMiddleInitial],
USERS.LASTNAME = [inLastName], USERS.DEPARTMENT = [inDepartment],
USERS.EMAIL = [inEMail], USERS.TELEPHONE = [inTelephone],
USERS.EXTENSION = [inExtension]
WHERE (((USERS.USERNAME)=[inUserName]));
 

You should probably use a function like this as part of our VBScript to run this stored procedure (note this example assumes that the database connection and VG.CommandLink are defined outside this function):

Public Function UpdateUser(ByRef cn As Connection, ByVal Firstname As String, ByVal MiddleInitial As String, _
                           ByVal LastName As String, ByVal Department As String, ByVal Email As String, _
                           ByVal Telephone As String, ByVal Extension As String, ByVal UserName As String ) As Boolean

   Const adParamInput = 1
   Const adParamOutput = 2
   Const adInteger = 3

   Const adCmdStoredProc = 4
   Const adExecuteNoRecords = 128
   Const adVarChar = 200

   Set cmd = CreateObject("ADODB.Command")
   Dim lngRecordsAffected As Long
   With adoCmd
       .ActiveConnection = cn
      .CommandType = adCmdStoredProc
      .CommandText = "UpdateUser"
      .Parameters.Append .CreateParameter("inFirstName", adVarChar, adParamInput, 50, Firstname)
      .Parameters.Append .CreateParameter("inMiddleInitial", adVarChar, adParamInput, 2, IIf(Len(MiddleInitial) > 0, MiddleInitial, Null))
      .Parameters.Append .CreateParameter("inLastName", adVarChar, adParamInput, 50, LastName)
      .Parameters.Append .CreateParameter("inDepartment", adVarChar, adParamInput, 50, IIf(Len(Department) > 0, Department, Null))
      .Parameters.Append .CreateParameter("inEmail", adVarChar, adParamInput, 50, IIf(Len(Email) > 0, Email, Null))
      .Parameters.Append .CreateParameter("inTelephone", adVarChar, adParamInput, 50, IIf(Len(Telephone) > 0, Telephone, Null))
      .Parameters.Append .CreateParameter("inExtension", adVarChar, adParamInput, 10, IIf(Len(Extension) > 0, Extension, Null))
      .Parameters.Append .CreateParameter("inUserName", adVarChar, adParamInput, 50, UserName)
      .Execute lngRecordsAffected, , adExecuteNoRecords
      UpdateUser = CBool(lngRecordsAffected)
   End With

   Set cmd = Nothing

End Function
 

The only thing you need to do is to match the order and data type of the parameters that are 'appended' to the command object with
those of the MS Access query.

 

Example : Retrieving data from an ASMX / SOAP Web Service

NOTE: The VoiceGuide Web Service module is the recommended way of querying web services.

The VBScript below retrieves the WSDL from the ASMX / SOAP Web Service provided by cdyne.com, and then calls the Web Service function GetCityWeatherByZIP.

In this example the ZIP code 10004 (New York, NY) was used.

Returned data is then formatted as a Result Variable list and returned to VoiceGuide.

Microsoft's SOAP Toolkit must be installed on system to use the MSSOAP.SoapClient30 COM object.

 

Set soapClient = CreateObject("MSSOAP.SoapClient30")
soapClient.MSSoapInit "http://wsf.cdyne.com/WeatherWs/Weather.asmx?WSDL"
Set node_list = soapClient.GetCityWeatherByZIP(10004)

For Each node In node_list
  sRv = sRv & "[" & node.nodeName & "]{" & node.Text & "}"
Next

set soapClient = Nothing
set node_list = Nothing

'MsgBox sRv

set vg = CreateObject("vgServices.CommandLink")
vg.Run_ResultReturn $RV_LINEID, sRv
set vg = Nothing

 

The Result Variables list returned would be something like this:

[Success]{true}[ResponseText]{City Found}[State]{NY}[City]{New York}[WeatherStationCity]{White Plains}[WeatherID]{4}[Description]{Sunny}[Temperature]{46}[RelativeHumidity]{17}[Wind]{NW16G26}[Pressure]{30.20F}[Visibility]{}[WindChill]{}[Remarks]{}

 

Example : Retrieving data from a HTTP / REST Web Service using HTTP GET

NOTE: The VoiceGuide Web Service module is the recommended way of querying web services.

Request to a Web Service can be made directly to the service/function URL.

eg. a GET call to cdyne.com GetCityWeatherByZIP function can be made directly to this HTTP address:

http://wsf.cdyne.com/WeatherWs/Weather.asmx/GetCityWeatherByZIP?ZIP=10004 and since the GetCityWeatherByZIP function returns data in XML format we can use the XML functions to to extract the required data.

 

Set Server = CreateObject("MSXML2.ServerXMLHttp")
Server.open "GET", "http://wsf.cdyne.com/WeatherWs/Weather.asmx/GetCityWeatherByZIP?ZIP=10004", False
Server.setRequestHeader "Content-Type", "text/xml"
Server.send

set objXMLDoc = Server.responseXML
'MsgBox Server.Status & " " & Server.StatusText & vbCrLf & objXMLDoc.xml

Set objChildNodes = objXMLDoc.documentElement.childNodes
For Each node In objChildNodes
  sRv = sRv & "[" & node.nodeName & "]{" & node.Text & "}"
Next
'MsgBox sRv

set vg = CreateObject("vgServices.CommandLink")
vg.Run_ResultReturn $RV_LINEID, sRv
set vg = Nothing

 

Example : Uploading data to HTTP / REST Web Service using HTTP POST

NOTE: The VoiceGuide Web Service module is the recommended way of querying web services.

Request to a Web Service can be made directly to the service/function URL.

eg. a POST call to cdyne.com GetCityWeatherByZIP function can be made directly to this HTTP address:

http://wsf.cdyne.com/WeatherWs/Weather.asmx/GetCityWeatherByZIP

and since the GetCityWeatherByZIP function returns data in XML format we can use the XML functions to to extract the required data.

 

Set Server = CreateObject("MSXML2.ServerXMLHttp")
Server.open "POST", "http://wsf.cdyne.com/WeatherWs/Weather.asmx/GetCityWeatherByZIP", False
Server.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
Server.send("ZIP=10004")

set objXMLDoc = Server.responseXML
'MsgBox Server.Status & " " & Server.StatusText & vbCrLf & objXMLDoc.xml

Set objChildNodes = objXMLDoc.documentElement.childNodes
For Each node In objChildNodes
sRv = sRv & "[" & node.nodeName & "]{" & node.Text & "}"
Next
'MsgBox sRv

set vg = CreateObject("vgServices.CommandLink")
vg.Run_ResultReturn $RV_LINEID, sRv
set vg = Nothing

 

eg. a new ticket in the zendesk.com system can be created using a HTTP POST call to URI:

https://yourname.zendesk.com/api/v1/tickets.xml

with ticket data provided as data attached to the POST:

 

sPostData = "<ticket><subject>Ticket from VoiceGuide</subject><description>Please call back $RV_CIDNUMBER</description></ticket>"
Set Server = CreateObject("MSXML2.ServerXMLHttp")
Server.open "POST", "https://yourname.zendesk.com/api/v1/tickets.xml", False, "youruser", "yourpassword"
Server.setRequestHeader "Content-Type", "application/xml"
Server.send sPostData

set objXMLDoc = Server.responseXML
MsgBox Server.Status & " " & Server.StatusText & vbCrLf & objXMLDoc.xml

set vg = CreateObject("vgServices.CommandLink")
vg.Run_ResultReturn $RV_LINEID, "[Server.Status]{" & Server.Status & "}[Server.StatusText]{" & Server.StatusText & "]"
set vg = Nothing

 

Example : Screen Scraping from Web Page

If the required information is not available through a web service but is shown on the web page itself, a screen-scraping approach can be used.

The VBScript below retrieves stock market levels from www.finance.yahoo.com and returns this data to VoiceGuide. (Please see the Web Site Scraping demo script).

 

Set IE = CreateObject("InternetExplorer.Application")
With IE
  .RegisterAsDropTarget = False
  .Visible = False
  .Silent = True
  .Navigate("finance.yahoo.com")
  While .Busy
    'sleep
  Wend
  With .Document.Body
    readWwwHtml = .InnerHTML
    readWwwText = .InnerText
  End With
End With
IE.Quit
Set IE = Nothing

iIndexDow = GetIntegerAfterLabel("Dow")
iIndexNasdaq = GetIntegerAfterLabel("Nasdaq")
iIndexSP500 = GetIntegerAfterLabel("S&P 500")

strResultVariables= "[MarketDow]{" & iIndexDow & "}" & _
"[MarketNasdaq]{" & iIndexNasdaq & "}" & _
"[MarketSP500]{" & iIndexSP500 & "}"

'make sure the returned data does not contain any commas
strResultVariables = replace(strResultVariables, ",", "")

set vg = CreateObject("vgServices.CommandLink")
vg.Run_ResultReturn $RV_LINEID, strResultVariables
Set vg = Nothing


function GetIntegerAfterLabel(strLabel)
  'it is assumed that the integer terminates with a decimal point
  iLblPos1= Instr(readWwwText, strLabel)
  iValuePos1 = iLblPos1 + len(strLabel)
  iValuePos2 = Instr(iValuePos1, readWwwText, ".")
  GetIntegerAfterLabel = mid(readWwwText, iValuePos1, iValuePos2-iValuePos1)
end function

 

 

Other Resources:

http://en.wikipedia.org/wiki/VBScript
http://www.visualbasicscript.com