| Siebel EAI > Using VB Script to access Siebel Objeckts
This is an example of how you can access Siebel Component Objects using VBScript.
You can you the excel sheet to update LOVs in Siebel.
This was developed by Iain Ollerenshaw who agreed to let me use it in my website. Thanks you Iain!
His website is http://www.lifeaftersiebel.co.uk/
The excel sheet can be downloaded here
To use the excel sheet you need to enter your siebel url. The image below shows the enu component, that does not have to be the case always.
The VB Script code is here
Option Explicit
' ******************************************************
'* Function: createButton_Click()
'*
'* Handle the 'New' button click
'*
'* Input: None
'*
'* Output: None
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Sub createButton_Click()
MainProcess "Create", "Create"
End Sub
' ******************************************************
'* Function: deactivateButton_Click()
'*
'* Handle the 'Deactivate' button click
'*
'* Input: None
'*
'* Output: None
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Sub deactivateButton_Click()
MainProcess "Deactivate", "Deactivate"
End Sub
' ******************************************************
'* Function: MainProcess
'*
'* Handles logon and passes control to the function related to the button pressed
'*
'* Input: sProcessToRun - name of the function to call
'* sSourceBook - name of the Workbook containing the source data
'*
'* Output: None
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Function MainProcess(sProcessToRun As String, sSourceBook As String)
Dim iResponse As Integer
Dim sConnectString As String
Dim sUserName As String
' This is where we keep track of each record status
Dim rStatus As Range
' Main Siebel application object
Dim oSiebel As SiebelDataControl
' Overall process status
Dim bStatus As Boolean
' Get the connection details and initialise
sConnectString = Range("C2").Value
sUserName = Range("C3").Value
Set rStatus = Range("C5")
rStatus.Value = "Initialising..."
' Make sure user wants to proceed
' *** Modify the message to give user details of what's about to happen
iResponse = MsgBox("You are about to amend LOVs in this environment!" & vbNewLine & _
vbNewLine & "Are you sure you want to continue?", vbYesNo, "Warning!")
If (iResponse = vbYes) Then
' Get admin password - actual connect process will handle incorrect password
frmPassword.txtPassword.SetFocus
frmPassword.Show
If gPassword <> "" Then
rStatus.Value = "Connecting..."
DoEvents
' Connect to Siebel
If (ConnectToSiebel(sConnectString, sUserName, gPassword, oSiebel)) Then
rStatus.Value = "Processing..."
' Run the process specified
If sProcessToRun = "Deactivate" Then
bStatus = ChangeAllStatus(oSiebel, sSourceBook, "N")
ElseIf sProcessToRun = "Create" Then
bStatus = CreateAllRecords(oSiebel, sSourceBook)
Else
MsgBox "Process name not recognised: " & sProcessToRun
End If
Else
' Handle the failure to connect to Siebel
MsgBox "An error has occurred connecting to Siebel" & vbNewLine & "VB: " & Err.Number & " - " & Err.Description & vbNewLine & _
"Siebel: " & oSiebel.GetLastErrText, vbCritical + vbOKOnly
rStatus.Value = "Error connecting to Siebel"
DoEvents
End If
' Close the Siebel connection and zap the object reference
CloseSiebel oSiebel
' Display the final status
If bStatus = True Then
rStatus.Value = "Finished without errors!"
Else
rStatus.Value = "Finished with errors!"
End If
Else
rStatus.Value = "Cancelled!"
End If
Else
rStatus.Value = "Cancelled!"
End If
Set oSiebel = Nothing
End Function
' ******************************************************
'* Function: ChangeAllStatus
'*
'* Go through source data records in the worksheet and calls the individual record processing function
'*
'* Input: oSiebel - Siebel Application object
'* sSourceBook - the Workbook from which to pull the data
'* sStatus - Status to set, Y, N or D for delete
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Function ChangeAllStatus(oSiebel As SiebelDataControl, sSourceBook As String, sActiveFlag As String) As Boolean
On Error GoTo ErrorHandler
' Results will be output to the this location
Dim rResults As Range
Set rResults = Worksheets(sSourceBook).Range("H3")
' Data Source is defined here
Dim rSource As Range
Set rSource = Worksheets(sSourceBook).Range("B3")
Dim bIsRecord As Boolean
Dim bStatus As Boolean
bStatus = True
' Keep count of rows effected
Dim iCount As Integer
iCount = 0
Dim iRowsInQuery As Integer
' Keep track of success for each row
Dim sStatus As String
' Declare BO and BC for use
Dim oBO As SiebelBusObject
Dim oBC As SiebelBusComp
' Init BO and BC
' *** Define specific required BO and BC names here
Set oBO = oSiebel.GetBusObject("List Of Values")
Set oBC = oBO.GetBusComp("List Of Values")
' Go through the records and process them
Dim sType As String
Dim sDisplay As String
Dim sLIC As String
Dim sParLIC As String
Dim sParType As String
Dim sDescription As String
Dim sOrder As String
Dim sHigh As String
Dim sLow As String
Dim sLang As String
Dim sParId As String
'*** Grab appropriate cell values
sType = rSource.Offset(iCount, 0).Value
While sType <> ""
'*** Grab appropriate cell values
sDisplay = rSource.Offset(iCount, 1).Value
sLIC = rSource.Offset(iCount, 2).Value
sLang = rSource.Offset(iCount, 3).Value
sParLIC = rSource.Offset(iCount, 4).Value
sParType = rSource.Offset(iCount, 5).Value
' Execute query and do something
With oBC
.ClearToQuery
.SetViewMode 3
' *** Activate and set appropriate search spec / expression here
.ActivateField "Active"
.SetSearchSpec "Name", """" + sLIC + """"
.SetSearchSpec "Value", """" + sDisplay + """"
.SetSearchSpec "Type", sType
.SetSearchSpec "Lang", sLang
' Get the Parent LIC AND Type
If (sParLIC <> "") Then
.SetSearchSpec "Parent Id", sParLIC
Else
.SetSearchSpec "Parent Id", "IS NULL"
End If
.ExecuteQuery True
bIsRecord = .FirstRecord
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
' Loop through record set
iRowsInQuery = 0
If bIsRecord Then
While bIsRecord
iRowsInQuery = iRowsInQuery + 1
' Process the current record
If SetStatus(oSiebel, oBO, oBC, sActiveFlag, iCount, rResults) Then
rResults.Offset(iCount, 0).Value = "Success (" & iRowsInQuery & " rows)"
Else
rResults.Offset(iCount, 0).Value = "Failed: " & Err.Description
bStatus = False
End If
bIsRecord = .NextRecord
' Allow some time for the application to update
DoEvents
Wend
Else
rResults.Offset(iCount, 0).Value = "Failed: record not found"
bStatus = False
End If
rResults.Offset(iCount, 1).Value = Now
End With
iCount = iCount + 1
'*** Grab appropriate cell values
sType = rSource.Offset(iCount, 0).Value
Wend
' Destroy objects
Set oBC = Nothing
Set oBO = Nothing
ChangeAllStatus = bStatus
Exit Function
ErrorHandler:
If oSiebel.GetLastErrCode <> 0 Then Err.Description = "A Siebel error has occurred: " & oSiebel.GetLastErrText
rResults.Offset(iCount, 0).Value = "Failed: " & Err.Description
Set oBC = Nothing
Set oBO = Nothing
ChangeAllStatus = False
End Function
' ******************************************************
'* Function: SetStatus
'*
'* Sets the 'Active' flag of a record
'*
'* Input: oSiebel - Siebel Application object
'* oBO - Siebel Business Object, already initialised
'* oBC - Siebel Business Component, already initialised
'* sStatus - status to set, Y or N
'* iCount - record being processed
'* rResults - range where results will be added to the worksheet
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Function SetStatus(oSiebel As SiebelDataControl, oBO As SiebelBusObject, oBC As SiebelBusComp, sStatus As String, iCount As Integer, rResults As Range) As Boolean
On Error GoTo ErrorHandler
' Do the processing here
With oBC
' Delete record if D
If sStatus = "D" Then
.DeleteRecord
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
Else
' Otherwise, set the status flag
.SetFieldValue "Active", sStatus
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.WriteRecord
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
End If
End With
SetStatus = True
Exit Function
ErrorHandler:
If oSiebel.GetLastErrCode <> 0 Then Err.Description = "A Siebel error has occurred: " & oSiebel.GetLastErrText
SetStatus = False
End Function
' ******************************************************
'* Function: CreateAllRecords
'*
'* Go through source data records in the worksheet and calls the individual record processing function
'*
'* Input: oSiebel - Siebel Application object
'* sSourceBook - the Workbook from which to pull the data
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Function CreateAllRecords(oSiebel As SiebelDataControl, sSourceBook As String) As Boolean
On Error GoTo ErrorHandler
' Results will be output to the 'New' worksheet
Dim rResults As Range
Set rResults = Worksheets(sSourceBook).Range("N3")
' Data Source is defined here
Dim rSource As Range
Set rSource = Worksheets(sSourceBook).Range("B3")
Dim bIsRecord As Boolean
Dim bStatus As Boolean
bStatus = True
' Keep count of rows effected
Dim iCount As Integer
iCount = 0
Dim iRowsInQuery As Integer
' Keep track of success for each row
Dim sStatus As String
' Declare BO and BC for use
Dim oBO As SiebelBusObject
Dim oBC As SiebelBusComp
' Init BO and BC
' *** Define specific required BO and BC names here
Set oBO = oSiebel.GetBusObject("List Of Values")
Set oBC = oBO.GetBusComp("List Of Values")
' Go through the records and process them
' *** Define variables here to retrieve values from source worksheet
Dim sType As String
Dim sDisplay As String
Dim sLIC As String
Dim sActive As String
Dim sLang As String
Dim sOrder As String
Dim sParLIC As String
Dim sParType As String
Dim sHigh As String
Dim sLow As String
Dim sSubType As String
Dim sDescription As String
'*** Grab appropriate cell values
sType = rSource.Offset(iCount, 0).Value
While sType <> ""
'*** Grab appropriate cell values
sDisplay = rSource.Offset(iCount, 1).Value
sLIC = rSource.Offset(iCount, 2).Value
sActive = rSource.Offset(iCount, 3).Value
sLang = rSource.Offset(iCount, 4).Value
sOrder = rSource.Offset(iCount, 5).Value
sParLIC = rSource.Offset(iCount, 6).Value
sParType = rSource.Offset(iCount, 7).Value
sHigh = rSource.Offset(iCount, 8).Value
sLow = rSource.Offset(iCount, 9).Value
sSubType = rSource.Offset(iCount, 10).Value
sDescription = rSource.Offset(iCount, 11).Value
If CreateRecord(oSiebel, oBO, oBC, iCount, rResults, sType, sDisplay, sLIC, sActive, sLang, sOrder, sParLIC, _
sParType, sHigh, sLow, sSubType, sDescription) Then
rResults.Offset(iCount, 0).Value = "Success"
Else
rResults.Offset(iCount, 0).Value = "Failed: " & Err.Description
bStatus = False
End If
rResults.Offset(iCount, 1).Value = Now
' Allow some time for the application to update
DoEvents
iCount = iCount + 1
sType = rSource.Offset(iCount, 0).Value
Wend
' Destroy objects
Set oBC = Nothing
Set oBO = Nothing
CreateAllRecords = bStatus
Exit Function
ErrorHandler:
If oSiebel.GetLastErrCode <> 0 Then Err.Description = "A Siebel error has occurred: " & oSiebel.GetLastErrText
rResults.Offset(iCount, 0).Value = "Failed: " & Err.Description
' Destroy objects
Set oBC = Nothing
Set oBO = Nothing
CreateAllRecords = False
End Function
' ******************************************************
'* Function: CreateRecord
'*
'* Creates a single instance of a BC record
'*
'* Input: oSiebel - Siebel Application object
'* oBO - Siebel Business Object, already initialised
'* oBC - Siebel Business Component, already initialised
'* iCount - record being processed
'* rResults - range where results will be added to the worksheet
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Function CreateRecord(oSiebel As SiebelDataControl, oBO As SiebelBusObject, oBC As SiebelBusComp, iCount As Integer, rResults As Range, _
sType As String, sDisplay As String, sLIC As String, sActive As String, sLang As String, sOrder As String, sParLIC As String, _
sParType As String, sHigh As String, sLow As String, sSubType As String, sDescription As String) As Boolean
On Error GoTo ErrorHandler
Dim sSiebError As String
Dim oParBC As SiebelBusComp
Dim sRet As Boolean
sRet = True
' Do the processing here
With oBC
.NewRecord 1
' *** Set appropriate field values
.SetFieldValue "Type", sType
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Name", sLIC
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Value", sDisplay
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Active", sActive
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Language Name", sLang
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Order By", sOrder
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "High", sHigh
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Low", sLow
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Sub Type", sSubType
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
.SetFieldValue "Description", sDescription
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
' Set the parent, if appropriate
If sParLIC <> "" Then
Set oParBC = .GetPicklistBusComp("Parent")
With oParBC
.ClearToQuery
.SetViewMode 3
.SetSearchSpec "Type", sParType
.SetSearchSpec "Name", """" & sParLIC & """"
.ExecuteQuery True
If .FirstRecord Then
.Pick
Else
Err.Description = "Parent LOV not found!"
sRet = False
End If
End With
End If
.WriteRecord
If oSiebel.GetLastErrCode <> 0 Then GoTo ErrorHandler
End With
Set oParBC = Nothing
CreateRecord = sRet
Exit Function
ErrorHandler:
If oSiebel.GetLastErrCode <> 0 Then Err.Description = "A Siebel error has occurred: " & oSiebel.GetLastErrText
Set oParBC = Nothing
CreateRecord = False
End Function
' ******************************************************
'* Function: GetParRowId
'*
'* Returns the ROW_ID of the Parent LOV
'*
'* Input: oSiebel - Siebel Application object
'* oBO - Siebel Business Object, already initialised
'* oBC - Siebel Business Component, already initialised
'* sParLIC - Parent LIC
'* sParType - Parent Type
'* Output: String - ROW_ID or NOT_FOUND
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Function GetParRowId(oSiebel As SiebelDataControl, oBO As SiebelBusObject, oBC As SiebelBusComp, sParLIC As String, sParType As String) As String
On Error GoTo ErrorHandler
Dim oParBO As SiebelBusObject
Dim oParBC As SiebelBusComp
Dim sParId As String
Set oParBO = oSiebel.GetBusObject("List Of Values")
Set oParBC = oParBO.GetBusComp("List Of Values")
With oParBC
.ClearToQuery
.SetViewMode 3
.SetSearchSpec "Name", """" & sParLIC & """"
.SetSearchSpec "Type", sParType
.SetSearchSpec "Parent Id", "IS NULL"
.ExecuteQuery 1
If (.FirstRecord) Then
sParId = .GetFieldValue("Id")
Else
sParId = "NOT_FOUND"
End If
End With
GetParRowId = sParId
Set oParBC = Nothing
Exit Function
ErrorHandler:
If oSiebel.GetLastErrCode <> 0 Then Err.Description = "A Siebel error has occurred: " & oSiebel.GetLastErrText
Set oParBC = Nothing
GetParRowId = "NOT_FOUND"
End Function
' ******************************************************
'* Function: ConnectToSiebel
'*
'* Connect to the Siebel Data Control, using the connect string and user credentials passed
'*
'* Input: sConnectString - appropriately formated Siebel connect string
'* sUserName - name to login as
'* sPassword - password for user
'* oSiebel - a Siebel Application object
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Function ConnectToSiebel(sConnectString As String, sUserName As String, sPassword As String, _
oSiebel As SiebelDataControl) As Boolean
On Error GoTo ErrorHandler
' Uses the specified parameters to connect to the Data Control
Dim iErrCode As Integer
Set oSiebel = CreateObject("SiebelDataControl.SiebelDataControl.1")
oSiebel.Login sConnectString, sUserName, sPassword
If (oSiebel.GetLastErrCode <> 0) Then GoTo ErrorHandler
ConnectToSiebel = True
Exit Function
ErrorHandler:
MsgBox oSiebel.GetLastErrCode
ConnectToSiebel = False
End Function
' ******************************************************
'* Function: CloseSiebel
'*
'* Close the connection to the Siebel Data Control
'*
'* Input: oSiebel - a Siebel Application object
'*
'* Output: Boolean - True for success, False for failure
'*
'* When Who What
'* 21/07/09 Iain Ollerenshaw Created
'*
'*******************************************************
Private Function CloseSiebel(oSiebel As SiebelDataControl) As Boolean
On Error GoTo ErrorHandler
oSiebel.Logoff
CloseSiebel = True
Exit Function
ErrorHandler:
CloseSiebel = False
End Function
| 
