on 02-09-2016 9:55 PM
Hi All,
Basically I want to create an UDF on SAP system document like MarketingDocument header or rows. I can't find a sample in the forum. Can anyone post some sample code ?
Thanks,
Lan
Hi Lan
Try this:
Firstly an Excel VBA sample:
Sub AddFields()
Dim vCmp As SAPbobsCOM.Company
Dim lRetCode, lErrCode As Long
Dim sErrMsg As String
Dim lRet_val As Long
Set vCmp = New SAPbobsCOM.Company
vCmp.Server = "(local)"
vCmp.CompanyDB = "SBODemoZA"
'vCmp.LicenseServer = "localhost:30000"
vCmp.UserName = "manager"
vCmp.Password = "ebs123"
vCmp.Language = ln_English
vCmp.DbServerType = dst_MSSQL2012
vCmp.DbUserName = "sa"
vCmp.DbPassword = "*******"
vCmp.UseTrusted = False
lRetCode = vCmp.Connect
If lRetCode <> 0 Then
vCmp.GetLastError lErrCode, sErrMsg
MsgBox (sErrMsg)
End If
On Error GoTo Error_handler
Worksheets("Fields").Select
row_no1 = 2
Get_Table:
Table_no = Range("A" & row_no1 & "").Value
Field_no = Range("B" & row_no1 & "").Value
If Field_no = "" Then GoTo End_sub
Field_no2 = Range("B" & row_no1 + 1 & "").Value
Field_name = Range("C" & row_no1 & "").Value
Field_desc = Range("D" & row_no1 & "").Value
Field_type = Range("E" & row_no1 & "").Value
Field_subt = Range("F" & row_no1 & "").Value
Field_size = Range("G" & row_no1 & "").Value
Field_dflt = Range("I" & row_no1 & "").Value
Field_null = Range("J" & row_no1 & "").Value
Field_tabl = Range("L" & row_no1 & "").Value
Field_lnk1 = Range("P" & row_no1 & "").Value
Field_lnk2 = Range("Q" & row_no1 & "").Value
GoSub Write_Table
row_no1 = row_no1 + 1
GoTo Get_Table
Write_Table:
Dim vUDF As SAPbobsCOM.UserFieldsMD
Set vUDF = vCmp.GetBusinessObject(oUserFields)
vUDF.TableName = Table_no
vUDF.Name = Field_name
vUDF.Description = Field_desc
If Field_type = "A" Then vUDF.Type = db_Alpha
If Field_type = "B" Then vUDF.Type = db_Float
If Field_type = "D" Then vUDF.Type = db_Date
If Field_type = "N" Then vUDF.Type = db_Numeric
If Field_subt = "%" Then vUDF.SubType = st_Percentage
If Field_subt = "S" Then vUDF.SubType = st_Sum
If Field_subt = "Q" Then vUDF.SubType = st_Quantity
vUDF.Size = Field_size
If Field_null = "Y" Then vUDF.Mandatory = tYES Else vUDF.Mandatory = tNO
If Field_tabl <> "" Then vUDF.LinkedTable = Field_tabl
If Field_lnk1 <> "" Then vUDF.ValidValues.Description = Field_lnk2
If Field_lnk1 <> "" Then vUDF.ValidValues.Value = Field_lnk1
If Field_lnk1 <> "" Then row_no1 = row_no1 + 1: GoSub Get_Next
If Field_no <> Field_no2 Then
RetVal1 = vUDF.Add
vCmp.GetLastError lErrCode, sErrMsg
Range("S" & row_no1 & "").Value = RetVal1 & "-" & sErrMsg
End If
If Field_no <> Field_no2 Then Return
RetVal1 = vUDF.Add
Return
Get_Next:
vUDF.ValidValues.Add
Field_no = Range("B" & row_no1 & "").Value
Field_no2 = Range("B" & row_no1 + 1 & "").Value
Field_lnk1 = Range("P" & row_no1 & "").Value
Field_lnk2 = Range("Q" & row_no1 & "").Value
If Field_lnk1 <> "" Then vUDF.ValidValues.Description = Field_lnk2
If Field_lnk1 <> "" Then vUDF.ValidValues.Value = Field_lnk1
If Field_no2 <> 0 Then vUDF.DefaultValue = Field_dflt: Return
If Field_no = Field_no2 Then row_no1 = row_no1 + 1: GoTo Get_Next
vUDF.DefaultValue = Field_dflt
Return
Error_handler:
MsgBox ("Error!")
Resume
End_sub:
MsgBox ("Disconnecting")
vCmp.Disconnect
End Sub
Secondly a vb,net sample:
Private Sub CreateField(ByVal sTable As String, ByVal sName As String, ByVal sDesc As String, ByVal iSize As Integer, ByVal aFieldType As SAPbobsCOM.BoFieldTypes, ByVal aSubType As SAPbobsCOM.BoFldSubTypes, ByVal sLink As String, ByVal bMand As SAPbobsCOM.BoYesNoEnum, ByVal bDefl As String)
Dim iResult As Long
Dim sMsg As String = ""
Dim i As Integer
Dim bFound As Boolean = False
Dim oRc As SAPbobsCOM.Recordset = Nothing
Dim FieldIndex As Integer
Try
oRc = Me.muc_ParentAddon.SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
oRc.DoQuery("SELECT aliasID, TableID, FieldID FROM CUFD WHERE aliasID='" & sName & "' and (TableID = '@" & sTable & "' or TableID = '" & sTable & "')")
oRc.MoveFirst()
If oRc.RecordCount > 0 Then
bFound = True
FieldIndex = oRc.Fields.Item(2).Value
End If
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRc)
oRc = Nothing
If Not bFound Then
Dim oUserFieldsMD As SAPbobsCOM.UserFieldsMD = Nothing
oUserFieldsMD = Me.muc_ParentAddon.SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields)
oUserFieldsMD.TableName = sTable
oUserFieldsMD.Name = sName
oUserFieldsMD.Description = sDesc
oUserFieldsMD.Type = aFieldType
oUserFieldsMD.EditSize = iSize
oUserFieldsMD.SubType = aSubType
oUserFieldsMD.Mandatory = bMand
If sLink <> "" Then oUserFieldsMD.LinkedTable = sLink
If bDefl <> "" Then oUserFieldsMD.DefaultValue = bDefl
If Not ValidVals Is Nothing Then
For i = 0 To ValidVals.Length - 1
oUserFieldsMD.ValidValues.Add()
oUserFieldsMD.ValidValues.SetCurrentLine(i)
oUserFieldsMD.ValidValues.Value = ValidVals(i).Value
oUserFieldsMD.ValidValues.Description = ValidVals(i).Description
'If i = 0 Then
' oUserFieldsMD.DefaultValue = ValidVals(i).Value
'End If
Next
End If
iResult = oUserFieldsMD.Add()
If iResult <> 0 Then
Me.muc_ParentAddon.SBO_Company.GetLastError(iResult, sMsg)
Me.muc_ParentAddon.SBO_Application.SetStatusBarMessage("Exception Creating Field " & sName & " in Table " & sTable & " : " & sMsg, EventLogEntryType.Error)
End If
System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserFieldsMD)
oUserFieldsMD = Nothing
End If
Catch ex As Exception
Me.muc_ParentAddon.SBO_Application.SetStatusBarMessage("Exception in Create Field U_" & sName & " in Table " & sTable & " : " & ex.Message, EventLogEntryType.Error)
Finally
End Try
End Sub
Hope this helps.
Kind regards
Peter Juby
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Peter,
I am able to create the UDF now, thanks. But the only thing is as other people mentioned that I have to execute code right after connect to SAP company, otherwise I will get error: Ref count for this object is higher than 0, error code -1120. Not sure if it is a bug or I did something wrong.
Lan
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.