Skip to Content

Using Excel VBA and DI API to post Journal Entries in SAP B1.

Hi, everybody

We have several thousands of Journal Entries to post in SAP Business One. So, we decided to write a small program to do the job.

Rather than develop a full add-on in Visual Studio, we decided to use Excel Visual Basic (VBA) to process the DI API.

Here is our strategy:

  • We have an Excel Sheet which has the data for all our Journal Entries.
  • Each row in Excel represents ONE Journal Entry.
  • A range of columns represents the Header Data.
  • Another range of columns represents the entries for the 1st line in the JE.
  • Similar for entries in the 2nd and 3rd lines in the JE.
  • When we press the button in the Excel Sheet, the VBA is executed, and we use looping to ensure that Journal Entries are posted one after another.
  • Each time a Journal is posted, the Excel row is coloured red.
  • Unfortunately, the application is not working as expected.

Our problem:

If the Excel sheet contains only 1 row (1 JE), it is posted without problem.

But if the Excel sheet contains several rows (several JE's), only the 1st row is posted successfully. The 2nd row (the 2nd JE) cannot be posted. This message is displayed:

We don't understand the problem, because nowhere did we attempt to post Dr and Cr in one row.

Anyway, if we delete the 1st Excel row, the 2nd row which refused to get posted, is now successfully posted.

So, it seems the error arises when we do the looping.

Hope somebody can help.

Thanks

Leon

---------------------------------------------------------------------

Here is a picture of our Excel Sheet:

capture1.jpg

-------------------------------------------------------------------------

And here is our VBA code:

The connection details are not shown, but they work correctly.

Dim oCompany As SAPbobsCOM.Company

Public Sub CreateOrder_Click()
    Dim lRetCode As Long
    Dim sErrMsg As String
    
    Dim answer As Integer
    Dim Counter As Integer: Counter = 1
    Dim RowNo As Integer: RowNo = 13
    
     'Connect to SBO
    login    '// We have not shown the code for this, but it works fine

    'Declare the sales order DI API object
    Dim oJE As SAPbobsCOM.JournalEntries
    Set oJE = oCompany.GetBusinessObject(oJournalEntries)
      
    If (Worksheets("JEsalesINV").Cells(RowNo, 1) = "") Then
    MsgBox "No more Entries to Post!", vbExclamation
    Exit Sub
End If

answer = MsgBox("Post Journal Entries?", vbYesNo + vbQuestion + vbDefaultButton2)

If answer = vbNo Then
    Exit Sub
End If
       
       '====================================================================
         ' JOURNAL ENTRY Worksheet = JEsalesINV
       '====================================================================
       
                  
Do While (Worksheets("JEsalesINV").Cells(RowNo, 1) <> "" And Counter <= 10)
          
             NoOfRows = Worksheets("JEsalesINV").Cells(RowNo, 10).Text
             ' // Some JEs have 2 lines, some 3

             Select Case NoOfRows   ' // No. of lines in the Journal Entry
                
              Case 2   '// If there are 2 lines in the Journal Entry
                
                '-----------------------
                'Header Journal Entry
                '----------------------
                        
          oJE.ReferenceDate = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
          oJE.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
          oJE.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
          oJE.Memo = Worksheets("JEsalesINV").Cells(RowNo, 6).Text
          oJE.Reference = Worksheets("JEsalesINV").Cells(RowNo, 7).Text
          oJE.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 8).Text
                                      
                '---------------------------------------
                'Journal Entry - details for rows 1 & 2
                '---------------------------------------
                                               
    oJE.Lines.AccountCode = Worksheets("JEsalesINV").Cells(RowNo, 11).Text
    oJE.Lines.LineMemo = Worksheets("JEsalesINV").Cells(RowNo, 12).Text
    oJE.Lines.Debit = Worksheets("JEsalesINV").Cells(RowNo, 13).Text
    oJE.Lines.Reference1 = Worksheets("JEsalesINV").Cells(RowNo, 14).Text
    oJE.Lines.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 15).Text
    oJE.Lines.ReferenceDate1 = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
    oJE.Lines.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
    oJE.Lines.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
    oJE.Lines.TaxGroup = "O1.1"

    oJE.Lines.Add

    oJE.Lines.AccountCode = Worksheets("JEsalesINV").Cells(RowNo, 17).Text
    oJE.Lines.LineMemo = Worksheets("JEsalesINV").Cells(RowNo, 18).Text
    oJE.Lines.Credit = Worksheets("JEsalesINV").Cells(RowNo, 19).Text
    oJE.Lines.Reference1 = Worksheets("JEsalesINV").Cells(RowNo, 20).Text
    oJE.Lines.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 21).Text
    oJE.Lines.ReferenceDate1 = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
    oJE.Lines.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
    oJE.Lines.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
                              
    oJE.Lines.Add
                             
                
        lRetCode = oJE.Add
                
        If lRetCode <> 0 Then
         sErrMsg = oCompany.GetLastErrorDescription
         MsgBox sErrMsg
         Exit Sub
                     
                  ElseIf iRetCode = 0 Then
                              
            ' // Once we have posted a row in Excel, we color the row red
                Rows(RowNo).Select
                Selection.Interior.Color = vbRed
                             
                Counter = Counter + 1
                RowNo = RowNo + 1
                End If
                       
                         
                Case 3    ' // If there are 3 lines in the Journal Entry
                '-----------------------
                'Header Journal Entry
                '----------------------
          oJE.ReferenceDate = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
          oJE.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
          oJE.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
          oJE.Memo = Worksheets("JEsalesINV").Cells(RowNo, 6).Text
          oJE.Reference = Worksheets("JEsalesINV").Cells(RowNo, 7).Text
          oJE.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 8).Text
        
                '-------------------------------------------
                'Journal Entry - details for rows 1,2 and 3
                '--------------------------------------------
                
   oJE.Lines.AccountCode = Worksheets("JEsalesINV").Cells(RowNo, 11).Text
   oJE.Lines.LineMemo = Worksheets("JEsalesINV").Cells(RowNo, 12).Text
   oJE.Lines.Debit = Worksheets("JEsalesINV").Cells(RowNo, 13).Text
   oJE.Lines.Reference1 = Worksheets("JEsalesINV").Cells(RowNo, 14).Text
   oJE.Lines.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 15).Text
   oJE.Lines.ReferenceDate1 = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
   oJE.Lines.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
   oJE.Lines.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
   oJE.Lines.TaxGroup = "O1.1"
   
    oJE.Lines.Add
                
                
    oJE.Lines.AccountCode = Worksheets("JEsalesINV").Cells(RowNo, 17).Text
    oJE.Lines.LineMemo = Worksheets("JEsalesINV").Cells(RowNo, 18).Text
    oJE.Lines.Credit = Worksheets("JEsalesINV").Cells(RowNo, 19).Text
    oJE.Lines.Reference1 = Worksheets("JEsalesINV").Cells(RowNo, 20).Text
    oJE.Lines.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 21).Text
    oJE.Lines.ReferenceDate1 = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
    oJE.Lines.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
    oJE.Lines.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
    
     oJE.Lines.Add
                                
                
   oJE.Lines.AccountCode = Worksheets("JEsalesINV").Cells(RowNo, 23).Text
   oJE.Lines.LineMemo = Worksheets("JEsalesINV").Cells(RowNo, 24).Text
   oJE.Lines.Credit = Worksheets("JEsalesINV").Cells(RowNo, 25).Text
   oJE.Lines.Reference1 = Worksheets("JEsalesINV").Cells(RowNo, 26).Text
   oJE.Lines.Reference2 = Worksheets("JEsalesINV").Cells(RowNo, 27).Text
   oJE.Lines.ReferenceDate1 = Worksheets("JEsalesINV").Cells(RowNo, 3).Text
   oJE.Lines.TaxDate = Worksheets("JEsalesINV").Cells(RowNo, 5).Text
   oJE.Lines.DueDate = Worksheets("JEsalesINV").Cells(RowNo, 4).Text
   
   oJE.Lines.Add
                
           lRetCode = oJE.Add
                
           If lRetCode <> 0 Then
              sErrMsg = oCompany.GetLastErrorDescription
              MsgBox sErrMsg
              Exit Sub
                     
            ElseIf iRetCode = 0 Then
              Rows(RowNo).Select
              Selection.Interior.Color = vbRed
                Counter = Counter + 1
                RowNo = RowNo + 1
            End If
                             
               End Select
 Loop


capture.jpg (22.9 kB)
capture1.jpg (484.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Sep 27, 2017 at 03:42 PM

    Hi Leon,

    Every time in your loop you need reinitialize again your JournalEntries object.

    Move the line code:

    Set oJE = oCompany.GetBusinessObject(oJournalEntries)

    To inside your loop, you can add the this line before assign value to NoOfRows variable.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 28, 2017 at 07:41 AM

    Hi Diego,

    Thanks a lot for your answer.

    It worked wonders!

    Now, my program is fully working.

    It posted 1000 J.Entries in less than 5 minutes!

    Best Regards,

    Leon

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 22 at 12:09 AM

    Hi Guys...

    Can you sahred this excel sheet? We are facing some dificulties to import a big lot data to inside Journal Entries from legacy system.

    Best regards,

    Wagner

    Add comment
    10|10000 characters needed characters exceeded