The idea was only to create user function in Excel to retrieve value from SAP Business One in excel:
To do that, I just created and xlam file wich refere to Microsoft ActiveX Data Objects X.X Library.
In a module I create a recordset function:
Public cn As ADODB.Connection
Public oCmd As ADODB.Command
Public oRS As ADODB.Recordset
Function ExecuteRS(ByVal oReq As String) As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Driver={SQL Server};Server=XXX.XXX.XXX.XXX;Trusted_Connection=no; Database=DDDD;Uid=sa;Pwd=PPPPP;"
.Open
End With
Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = cn
.CommandText = oReq
End With
Set ExecuteRS = oCmd.Execute
End Function
And after a collection of user function:
for example for OITM:
Public Function ItemName(ByVal Itemcode As String)
ItemName = ExecuteRS("Select itemName from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function Itmsgrpcod(ByVal Itemcode As String)
Itmsgrpcod = ExecuteRS("SELECT T1.[ItmsGrpNam] FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T0.[ItemCode] ='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function Gamme(ByVal Itemcode As String) As String
Gamme = ExecuteRS("SELECT T1.[Name] FROM [dbo].[OITM] T0 left outer JOIN [@DU001] T1 ON T0.U_DU001 = T1.Code WHERE T0.[ItemCode] ='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function Famille(ByVal Itemcode As String) As String
Famille = ExecuteRS("SELECT T1.[Name] FROM [dbo].[OITM] T0 left outer JOIN [@DU002] T1 ON T0.U_DU002 = T1.Code WHERE T0.[ItemCode] ='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function EAN(ByVal Itemcode As String) As String
EAN = ExecuteRS("Select codebars from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function NomDouane(ByVal Itemcode As String) As String
NomDouane = ExecuteRS("Select U_DU004 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function VOL(ByVal Itemcode As String) As Double
On Error Resume Next
VOL = ExecuteRS("Select svolume from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then VOL = 0
End Function
Public Function PNET(ByVal Itemcode As String) As Double
On Error Resume Next
PNET = ExecuteRS("Select U_DU005 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then PNET = 0
End Function
Public Function PBRUT(ByVal Itemcode As String) As Double
On Error Resume Next
PBRUT = ExecuteRS("Select sweight1 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then PBRUT = 0
End Function
Public Function TYPEEMB(ByVal Itemcode As String) As String
TYPEEMB = ExecuteRS("Select U_DU035 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function CONDT(ByVal Itemcode As String) As Double
On Error Resume Next
CONDT = ExecuteRS("Select salpackun from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then CONDT = 0
End Function
Public Function EANCART(ByVal Itemcode As String) As String
EANCART = ExecuteRS("Select U_DU036 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function VolC(ByVal Itemcode As String) As Double
On Error Resume Next
VolC = ExecuteRS("Select U_DU025 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then VolC = 0
End Function
Public Function VolP(ByVal Itemcode As String) As Double
On Error Resume Next
VolP = ExecuteRS("Select U_DU044 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then VolP = 0
End Function
Public Function PC(ByVal Itemcode As String) As Double
On Error Resume Next
PC = ExecuteRS("Select U_DU037 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then PC = 0
End Function
Public Function PP(ByVal Itemcode As String) As Double
On Error Resume Next
PP = ExecuteRS("Select U_DU045 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then PP = 0
End Function
Public Function LC(ByVal Itemcode As String) As Double
On Error Resume Next
LC = ExecuteRS("Select U_DU020 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then LC = 0
End Function
Public Function LaC(ByVal Itemcode As String) As Double
On Error Resume Next
LaC = ExecuteRS("Select U_DU021 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then La = 0
End Function
Public Function HtC(ByVal Itemcode As String) As Double
On Error Resume Next
HtC = ExecuteRS("Select U_DU022 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then HtC = 0
End Function
Public Function DiaC(ByVal Itemcode As String) As String
DiaC = ExecuteRS("Select isnull(U_DU024,'N') from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
End Function
Public Function HtP(ByVal Itemcode As String) As Double
On Error Resume Next
HtP = ExecuteRS("Select U_DU046 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then HtP = 0
End Function
Public Function ColisLit(ByVal Itemcode As String) As Double
On Error Resume Next
ColisLit = ExecuteRS("Select U_DU038 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then ColisLit = 0
End Function
Public Function VolLit(ByVal Itemcode As String) As Double
On Error Resume Next
VolLit = ExecuteRS("Select U_DU040 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then VolLit = 0
End Function
Public Function PoidsLit(ByVal Itemcode As String) As Double
On Error Resume Next
PoidsLit = ExecuteRS("Select U_DU041 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then PoidsLit = 0
End Function
Public Function ArtLit(ByVal Itemcode As String) As Double
On Error Resume Next
ArtLit = ExecuteRS("Select U_DU039 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then ArtLit = 0
End Function
Public Function LitPal(ByVal Itemcode As String) As Double
On Error Resume Next
LitPal = ExecuteRS("Select U_DU042 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then LitPal = 0
End Function
Public Function ArtPal(ByVal Itemcode As String) As Double
On Error Resume Next
ArtPal = ExecuteRS("Select U_DU043 from oitm where itemcode='" & Itemcode & "'").Fields.Item(0).Value
If Err Then ArtPal = 0
End Function
To finish I share this file in B1SHR and added to the AddIn of Excel
Christophe
- SAP Managed Tags:
