News Feed - SAPB1Community

B1 Technical Tips and Tricks

Wojciech
6 months ago

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 users.

In the attachment, I’m sharing a script that allows you to create OITM item master data in SAP. This kind of solution is useful for people who don’t need direct access to the system but are responsible for creating things like item master data or cost centers. It can also be easily used from Power Apps and, for example, added as an app to a Microsoft Teams group.

In the near future, I’ll be sharing more practical solutions, as this is exactly the kind of work I like to do.
Documents

Title Following special characters are not supported: \ / ? % * : | " < >

Privacy

Cancel Create

Create Folder Cancel Move
Excel-OITM.txt 2 KB Text File - Click to view

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
2 reactions

3 Comments
- Gary
  
  6 months ago
  
  Curious… Why would you recommend using a script and not using the Data Transfer Workbench? What are the advantages?
  
  1 reply
  - Wojciech
    
    6 months ago
    
    @GJFIBN OITM is only an example.
    For customers, I also created BP master data, document drafts, and journal vouchers.
    With one button in Excel, the data is sent to SAP.
    With DTW blocks, it is easy to make mistakes because of the format. From Excel, you send exactly what you see, immediately.
    In my opinion, sending data through the DI API from interfaces is a good solution.
    Personally, I do not like DTW.
    During data imports, I also send data from Excel through the DI API.
    The Excel files include validations to avoid errors.
    
    1 reaction
- Amirhossein
  
  6 weeks ago
  
  Well done @Wwozniak , really interesting approach!
  I’ve used the Service Layer to submit incoming checks and item batches in inventory transfers, which skips the need for multiple Excel files, though it can also submit in draft status.

My Groups

My Groups

Export OITM to SAP B1 directly from Excel