Members - SAPB1Community

Export OITM to SAP B1 directly from Excel

Hi everyone,
since this is my first post here, let me briefly introduce myself.

My name is Wojtek and I’ve been working with SAP Business One for 14 years. I’ve participated in implementations almost all over the world, from China to North America. I create localization packages where SAP has gaps and I really enjoy making life easier for end…

Copy Download Link

Private Sub CommandButton1_Click()
'Login to SAP
Dim oCompany As SAPbobsCOM.Company
Set oCompany = New SAPbobsCOM.Company
oCompany.DbServerType = 15
oCompany.Server = "********"
oCompany.CompanyDB = "*******"
oCompany.UserName = "*******"
oCompany.Password = "*******"
oCompany.LicenseServer = "*******"
oCompany.UseTrusted = False
oCompany.DbUserName = "*******"
oCompany.DbPassword = "*******"
oCompany.SLDServer = "*******"

Dim lRetCode As Long
lRetCode = oCompany.Connect

If lRetCode = 0 Then
    'Create column A , B , C With header Code , name , foreign name
    
    ' Downloading data from an Excel sheet
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Sheets("Sheet1")
    
    Dim itemCode As String
    itemCode = ws.Range("A2").Value
    
    Dim itemDescription As String
    itemDescription = ws.Range("B2").Value
    
    ' Checking if the item already exists in the OITM file
    Dim oItem As SAPbobsCOM.Items
    Set oItem = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems)
    If oItem.GetByKey(itemCode) Then
        ' Item name update
        oItem.ItemName = itemDescription
        Dim lUpdateCode As Long
        lUpdateCode = oItem.Update
        If lUpdateCode = 0 Then
            MsgBox "Item " & itemCode & " updated successfully."
        Else
            MsgBox "Error updating item. Error code: " & CStr(oCompany.GetLastErrorCode) & ". Error description: " & oCompany.GetLastErrorDescription
        End If
    Else
        ' Adding an item to the OITM file
        oItem.itemCode = itemCode
        oItem.ItemName = itemDescription
        Dim lAddCode As Long
        lAddCode = oItem.Add
        If lAddCode = 0 Then
            MsgBox "Item " & itemCode & " added successfully."
        Else
            MsgBox "Error adding item. Error code: " & CStr(oCompany.GetLastErrorCode) & ". Error description: " & oCompany.GetLastErrorDescription
        End If
    End If
    
    Set oItem = Nothing
    
Else
    MsgBox "Error connecting to SAP Business One database. Error code: " & CStr(lRetCode)
End If

Set oCompany = Nothing

End Sub

Expand Collapse