Skip to Content

The idea was only to create user function in Excel to retrieve value from SAP Business One in excel:

Capture1.JPG

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

Capture3.JPG

Christophe

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply