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:
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:
-------------------------------------------------------------------------
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