The VBScript tool is used to create “programming” statements in a Bizweaver workflow using the VBScript language. The results from a VBScript step can be used as variables in subsequent workflow steps.

VBScript can be used for a broad range of purposes. Several VBScript samples are included in VBScript Samples section. A web search (Google, etc.) for “VBScript” and what you are trying to accomplish will typically provide a wealth of examples as well.

There are two key elements to the VBScript tool setup. First is the VBScript statement. This is the “program code” that gets executed.

Second is the “Return” statement. This statement sets the values from the first step into a variable that can be used in other workflow steps. The format of the “Return” statement is:

 Return “VariableName”, ScriptValue 

where “VariableName” is the variable that will be displayed for selection (using the <F4> key) in subsequent workflow steps, and ScriptValue is the value from the VBScript statement. Note that the variable name must be enclosed in double-quotes.

A single VBScript step can contain multiple VBScript statements. There must be a “Return” statement for each VBScript statement.

1. Enter the Script text.
   1. See VBScript Tips and VBScript Samples for more information and sample scripts.
2. Click the [Execute] button to validate your script.
   1. If the Return statements are not included the VBScript may execute normally but the validation will not present any results.
   2. Using the [Execute] validation will let you see exactly what value will be produced.
   3. The [Execute] option may not function as expected if variables from other workflow steps are used in the VBScript. In this case, you will need to use the workflow “Test Run” (Debug) option to step through the workflow.

This section includes a number of tips that may be useful when working with the VBScript tool.

There are three characters that have a special meaning in a VBScript.

• A single quote is used to indicate a comment line. Keep in mind that a comment can only be one line. Comment blocks are not supported.
• An ampersand (&) is used to concatenate text values. This is shown in the earlier example image above.
• A VBScript statement is expected to be one long string of text. This means that if you use a carriage return in the middle of the statement, the statement will fail. If you want to put line breaks in the statement, use an underscore (_) just before the carriage return.

• There is no specific significance to the script values and variable names. The names should indicate their purpose to aid in their use. This is particularly true of the variable name since it will be referenced in other workflow steps.
• The script value and the variable name cannot be the same.
• Script values and variable names cannot include spaces or special characters other than an underscore (_). For example: Date Value is not acceptable, nor is Date-Value, whereas DateValue or Date_Value are acceptable.

These samples provided for reference only. While most of them have been used in Bizweaver implementations, Third Wave Business Systems does not warrant that they will function in your environment, nor are they a supported part of Bizweaver.

Using copy/paste with these examples may raise errors in the VBScript tool. This is because single and double-quotes in Microsoft Word are slightly different that those used when developing computer code. We have tried to adjust for these. However, if you encounter errors simply check the single and double-quotes to ensure that they are not the “pretty” format used in many Word fonts.

DateCalc = DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d",Date),2)
Return "DateValue", DateCalc

DateCalc = Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d",Date),2) & DatePart("yyyy",Date)
Return "DateValue", DateCalc

DateCalc = DatePart("yyyy",Date -1) & Right("0" & DatePart("m",Date -1),2) & Right("0" & DatePart("d",Date -1),2)
Return "DateValue", DateCalc

DateCalc = DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d",Date),2) & "T" & right("0" & DatePart("h",Now),2) & right("0" & DatePart("n",Now),2)
Return "FileDateTime", DateCalc

StringCreate = "Created by Bizweaver at " & CStr(Now)
Return "CreatedByString", StringCreate

MonthDayCalc = Day(Date)
Return "MonthDayValue", MonthDayCalc

CalcDate =(DateAdd("d",-1,Date()))
return "PriorDay",CalcDate

DayCalc = Weekday(Date)
Return "DayValue", DayCalcc

daysInMonth = Day(DateSerial(thisYear, thisMonth + 1, 0))
Return "DaysInCurrentMonth", daysInMonth

MonthCalc = Month(now)
Return "MonthValue", MonthCalc

MonthNameCalc = MonthName(Month(now))
Return "MonthNameValue", MonthNameCalc

MonthAbbrvCalc = MonthName(Month(now),True)
Return "MonthAbbrvValue", MonthAbbrvCalc

TimeCalc = Right("0" & DatePart("h",now),2) & Right("0" & DatePart("n",now),2) & Right("0" & DatePart("s",now),2)
Return "TimeValue", TimeCalc

In this example, if the day of the week is Monday (day 2) then the current date is returned. Otherwise the date one day back is returned.

If Weekday(Date) = 2 Then
DateCalc = DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d",Date),2)
Else
DateCalc = DatePart("yyyy",Date -1) & Right("0" & DatePart("m",Date -1),2) & Right("0" & DatePart("d",Date - 1),2)
End If
Return "DateValue", DateCalc

Useful for controlling workflow execution based on month end.

IF "DateSerial(Year(Now), 1 + Month(Now), 0)" = "Date" THEN LastDay = "Y" ELSE LastDay = "N" END IF
Return "RunJob",LastDay

This same logic can be modified to work with other days of the month as well.

Useful for controlling non-hourly scheduled events
IF datepart("h",Now) mod 2 = 0 THEN HourEvenOdd = "Yes" ELSE HourEvenOdd = "No" END IF
Return "RunYesNo",HourEvenOdd

sFolder = "C:\tmp"
iCount = 0
Set oFSO = Createobject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) then
Set oFolder = oFSO.GetFolder(sFolder)
iCount = oFolder.files.count
end if
Return "FileCount", iCount

dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
' Put the absolute path to the folder and file you want to check in the next line
If fso.FileExists("Your_FilePath_Here") Then
  FileExists=CBool(1)
Else
  FileExists=CBool(0)
End If
' Values returned will be True or False
Return "ExistsFlag",FileExists

Dim strDirectory, nThreshold, counter, extension, msgtext
Dim objFSO, objFolder, objFile, Logfile
extension = "xml"
strDirectory = "C:\tmp"
counter = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strDirectory)
For Each objFile in objFolder.Files
If LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) Then
counter = counter + 1
End If
Next
Return"FileCount",counter

' Create comma-delimited list of files in a folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Change value of objFolder to the appropriate target
Set objFolder = objFSO.GetFolder("c:\Programdata\Bizweaver\tmpFiles")
Set allFiles = objFolder.Files
dim fileList, outFile, oCount, iCount
'
If objFSO.FolderExists(objFolder) then
Set oFolder = objFSO.GetFolder(objFolder)
iCount = oFolder.files.count
End If
'
For Each objFile in allFiles
	oCount = oCount + 1
IF oCount <> iCount Then
outFile = objFile
fileList = (fileList + outFile) + " ,"
End IF
IF oCount = iCount Then
outFile = objFile
fileList = (fileList + outFile)
End IF
Next
return "OutFileList", fileList

Dim extension
extension="xml"
Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\tmp")
Set colFiles = objFolder.Files
dtmOldestDate = Now
For Each objFile in colFiles
If objFile.DateCreated < dtmOldestDate And LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) Then
dtmOldestDate = objFile.DateCreated
strOldestFile = objFile.Path
End If
Next
Return"OldestFile,strOldestFile

Basic example

ReplaceStr = replace("VariableToTest","ValueToFind","ReplacementValue")
Return "NewStr",ReplaceStr

Replace one URL with another

NewName = replace("http:%%//%%XXX-BUSONE/Login.aspx?LoginServer=XXX-BUSONE&ReturnUrl=%2fFM_PO_L1b16%2fDefault.aspx%3fid%3d200","YYY-BUSONE","###",1,1)
Return "NewStringName",NewName

This can be used to evaluate all records in a file and replace one character with another. A typical example would be replacing an apostrophe with another character to allow the data to be inserted into a SQL table. The apostrophe can then be reset using the standard SQL “replace” function.

In this example the apostrophe (‘) is replaced with a carat (^). Also, the value “vFullPath” will need to be set using a variable.

Const ForReading = 1
Const ForWriting = 2
vFullPath = "C:\tmp\SampleData.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(vFullPath, ForReading)
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "'", "^")
Set objFile = objFSO.OpenTextFile(vFullPath, ForWriting)
objFile.WriteLine strNewText
objFile.Close

Sample using Bizweaver variable from a prior workflow step

Const ForReading = 1
Const ForWriting = 2
vFullPath = "WorkFlow(GetFiles).Get(FileName)"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFile = objFso.OpenTextFile(vFullPath, ForReading)
strtext = objFile.ReadAll
objFile.Close
strNewText = Replace(strtext, "'", "`")
Set objFile = objFso.OpenTextFile(vFullPath, ForWriting)
objFile.WriteLine strNewText
objFile.Close

This situation typically occurs when source application has text fields that allow use of <Enter> to add blank lines for appearance. In this example the source file is hardcoded. It should typically be entered using parameters.

Const ForReading = 1
Const ForWriting = 2
' vFullPath = WScript.Arguments.Item(0) & "\" & WScript.Arguments.Item(1)
vFullPath = "C:\TaskCentre\Handshake\To_Be_Processed\POOL_MAGIC_SPR16(2).csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(vFullPath, ForReading)
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, vbCr & vbLf, "||")
' strNewText = Replace(strText, vbLf, ";")
' strNewText = Replace(strText, "^", vbLf)
' strNewText = Replace(strText, "||", vbCr & vbLf )
Set objFile = objFSO.OpenTextFile(vFullPath, ForWriting)
objFile.Write strNewText
objFile.Close
Set objFile = objFSO.OpenTextFile(vFullPath, ForReading)
strText = objFile.ReadAll
objFile.Close
' strNewText = Replace(strText, vbCr & vbLf, "||")
strNewText = Replace(strText, vbLf, ";")
' strNewText = Replace(strText, "^", vbLf)
' strNewText = Replace(strText, "||", vbCr & vbLf )
Set objFile = objFSO.OpenTextFile(vFullPath, ForWriting)
objFile.Write strNewText
objFile.Close
Set objFile = objFSO.OpenTextFile(vFullPath, ForReading)
strText = objFile.ReadAll
objFile.Close
' strNewText = Replace(strText, vbCr & vbLf, "||")
' strNewText = Replace(strText, vbLf, ";")
' strNewText = Replace(strText, "^", vbLf)
strNewText = Replace(strText, "||", vbCr & vbLf )
Set objFile = objFSO.OpenTextFile(vFullPath, ForWriting)
objFile.Write strNewText
objFile.Close

Const ForReading = 1
Const ForWriting = 2
vFullPath = "C:\Quartzy\POImport_20170918_TabDelimited.txt"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFile = objFso.OpenTextFile(vFullPath, ForReading)
strtext = objFile.ReadAll
objFile.Close
'DEFINE BAD CHARACTERS
'badchars = Array("?","/","\",":","*","""","<",">","","&","#","~","%","{","}","+","_",".","®","™","µ",",")
'badchars = Array("?","\",":","*","""","<",">","&","#","~","%","{","}","+","_","®","™","µ",",","[","]","(",")","=")
badchars = Array("?","\","*","""","&","#","~","%","{","}","+","_","®","™","µ",",","[","]","(",")","=")
'LOOP THROUGH AND REPLACE BAD CHARACTERS
For Each badchar in badchars
'REPLACE BAD CHARACTERS WITH OTHER CHARACTERS
Select Case badchar
Case "&": goodchar = " and "
Case "µ": goodchar = "u"
Case ",": goodchar = ""
Case Else: goodchar = " "
End Select
strtext = Replace(strtext , badchar, goodchar )
Next
'clean = strtext
Set objFile = objFso.OpenTextFile(vFullPath, ForWriting)
objFile.WriteLine strText
objFile.Close

If a File Operations/Get step is feeding this step, replace the variable with the variable from your “get” step.

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("WorkFlow(FileOperations1).Get(FileName)", ForReading)
strContents = objFile.ReadAll
objFile.Close
strFirstLine = "DUMMY"
strNewContents = strFirstLine & vbCrLf & strContents
Set objFile = objFSO.OpenTextFile("WorkFlow(FileOperations1).Get(FileName)", ForWriting)
objFile.WriteLine strNewContents
objFile.Close

Dim fso, f, f1, fc, strComments, strScanDir, testcount, delcount
'------------------------------
strDir = "C:\ProgramData\Bizweaver\LogArchives"
strDays = 10
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(strDir)
Set fc = f.Files
'-------------
For Each f1 in fc
If DateDiff("d", f1.DateCreated, Now) > strDays then testcount = testcount + 1 End If
Next
Return "InFileCount",testcount
'--------------------------------------------
' Delete files
'------------------------------------------------------------------
For Each f1 in fc
If DateDiff("d", f1.DateCreated, Now) > strDays then delcount = delcount + 1 End If
'If DateDiff("d", f1.DateCreated, Now) > strDays then fso.DeleteFile(f1) End If
Next
Return "DelFileCount",delcount

• Change StdDestinationLocation, sInputFolder, sSearchString1 values as needed

Const ForReading = 1
Dim StrDestinationLocation
StrDestinationLocation = "D:\BizWeaver\PO_Zwanger\600 ZPRAD Orders\"
Dim sInputFolder
sInputFolder = "D:\BizWeaver\PO_Zwanger\100 Incoming"
Dim sSearchString1
sSearchString1 = "30-ZPRAD"
Dim fso, oFolder, oFile, fil, sReadAll
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(sInputFolder)
For Each fil in oFolder.Files
   Set oFile = fso.OpenTextFile(fil.Path, ForReading)
   sReadAll = oFile.ReadAll
   oFile.close
   If InStr(sReadAll, sSearchString1) > 0 Then   
      fso.MoveFile fil.Path, StrDestinationLocation
   End If
Next 

Dim sInvalidChars, i, sTemp, eString, ErrCount, bTemp
'
ErrCount = 0
eString = "WorkFlow(SQLCommand1).Get({FIELD_Email})"
'
' Disallowed characters
sInvalidChars = "!#$%^&*()=+{}[]|\;:'/?>,< "
'
' Check that there is at least one '@'
IF InStr(eString, "@") <= 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
' Check that there is at least one '.'
IF InStr(eString, ".") <= 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
' Thelength is at least six (a@a.ca)
IF Len(eString) < 6 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
i = InStr(eString, "@")
sTemp = Mid(eString, i + 1)
IF InStr(sTemp, "@") > 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
' Space not allowed AFTER '@'
IF InStr(sTemp, " ") > 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
' Check that there is one dot AFTER '@'
IF InStr(sTemp, ".") = 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
'
' Be sure there's no quote (")
IF InStr(eString, Chr(34)) > 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
' Check for any other disallowed chars
' optimize a little if eString longer than sInvalidChars
' check the other way around
If Len(eString) > Len(sInvalidChars) Then
For i = 1 To Len(sInvalidChars)
  If InStr(eString, Mid(sInvalidChars, i, 1)) > 0 THEN
ErrCount = ErrCount + 1
End If
Next
ELSE
For i = 1 To Len(eString)
If InStr(sInvalidChars, Mid(eString, i, 1)) > 0 THEN
  ErrCount = ErrCount + 1
end if
'If bTemp Then Exit For
Next
END IF
' wscript.echo ErrCount
'
' no two consecutive dots
IF InStr(eString, "..") > 0 THEN
  ErrCount = ErrCount + 1
END IF
' wscript.echo ErrCount
Return "ErrorCount", ErrCount

This example shows how a VBScript file can be used as a desktop “app” to allow users to execute Bizweaver workflows as needed.

Const ForWriting=2
'
dim oFso, oFile, oShell, oShellEnv, oHomepath, parm1, parm2, oHTTP, returnValue
const overwrite = true
set oFso      = CreateObject("Scripting.FileSystemObject")
set oShell    = WScript.CreateObject("WScript.Shell")
oHomepath = "C:"+ oshell.Environment("PROCESS").Item("HOMEPATH")
'
parm1=InputBox("Workflow ID (1=WorkflowID1, 2=WorkflowID2, 3=WorkflowID3, 4=WorkflowID4):")
IF not isnumeric(parm1) then
msgbox "This value must be a number"
END IF
parm2=InputBox("Document ID:")
IF not isnumeric(parm2) then
msgbox "This value must be a number"
END IF
'
response=msgbox("Continue?",vbYesNo)
IF isnumeric(parm1) AND isnumeric(parm2) AND response=6 then
strURL="https://YourBizweaverServer/BWService/api/workflow/InvokeWorkFlow?pTaskID="+parm1+"&pStartAfter=0&pArguments={vgoRecNum:"+parm2+"}"
'
' wscript.echo strURL
'
Set oHTTP = CreateObject("MSXML2.XMLHTTP") 
Call oHTTP.Open("GET", strURL, FALSE) 
oHTTP.Send
'
' wscript.echo oHTTP.ResponseText
'
IF instr(oHTTP.ResponseText,":true") > 0 then
msgbox("Process Successful")
ELSE
msgbox("A problem occurred")
Set oFile = oFSO.CreateTextFile (oHomepath+"\Desktop\Bizweaver Out.txt", ForWriting)
oFile.Write oHTTP.ResponseText
oFile.Close
END IF
ELSE
msgbox "No processing occurred"
END IF

Const ForReading = 1
Const ForWriting = 2
Dim fs
Dim fname
Dim HasCert
Dim vStartNum 
Dim vLength
Dim FileIndex 
vStartNum = 1
vSourcePath = "WorkFlow(GetCustomerFiles).Get(FileName)"
vTargetPath = "WorkFlow(GetCustomerFiles).Get(SourcePath)\Working\"
Set objfso = CreateObject("Scripting.FileSystemObject")
Set objFile = objfso.OpenTextFile(vSourcePath, ForReading)
strtext = objFile.ReadAll
sSearchString1 = "<customer"
sSearchString2 = "</customer>"
objFile.Close
While InStr(vStartNum, strtext, sSearchString1) > 0
    FileIndex = FileIndex + 1
    vStartNum = InStr(vStartNum, strtext, sSearchString1)
    vLength = InStr(vStartNum, strtext, sSearchString2) - InStr(vStartNum, strtext, sSearchString1) + 11
    strNewText = Mid(strtext, vStartNum, vLength)
    Set fname = objfso.CreateTextFile(vTargetPath & "WorkFlow(GetCustomerFiles).Get(FileTitleWithoutExtension)" & "_" & FileIndex & ".xml", True)
    fname.WriteLine strNewText
    fname.Close
    vStartNum = InStr(vStartNum, strtext, sSearchString2) + 11
Wend

Home