Create An excel Utility to Cancel Open Invoice In SAP Business One in Bunch Step Details

What happen during creating invoice we created multiple invoice with wrong details or invoice open because of many reasons in SAP Business One, and after a few days you get a task to cancel all those list of invoice which are open not required to process any more. It becomes hectic to cancel  a huge records one by one. I am showing you the way by which you can achieve the result or cancel Invoice in a click.
For making it Possible, you will have to create an excel utility that integrated with SAP Business one using DI API.

Follow the following steps:
1       1. Create an Excel Workbook.
2       2.  Create Two sheets one with name Credential and other with Invoice Entry.
               Credential sheet will contain all connection related information.
               Invoice Entry Sheet will contain List of DocEntry of Invoice those entry require to cancel and a button at on which click Invoice will cancel.

 3.    Steps to add Button on sheet please read my article to know more.
4. Apply Macro on Button Click To Cancel
5. Add SAP Business one DI API  by click on Tool menu then references in Visual Basic Editor.

6.  And then a window will open if DI API is listed then select if not then click on Browse then add SAP Business one DI API as Reference.

7. Login have their detail definition in module 2
8. Write the code for Login in module 2 as below or in a way as attach screenshots

9. Assign the Cancel_Click() to the Button, then save the file as Utility.
1.    10.After providing the appropriate credential in Sheet credential and pass the Docentry and click on button and wait it will connect with SAP and give you message of successful connection with DB and cancel the entry.

Code For Module1
Sub Cancel_Click()
End Sub

Code For Module2

Public Sub login()

B1UserID = Trim(ActiveCell.Value2)
ActiveCell.Offset(1, 0).Activate
B1Password = Trim(ActiveCell.Value2)
ActiveCell.Offset(1, 0).Activate
sqluser = Trim(ActiveCell.Value2)
ActiveCell.Offset(1, 0).Activate
Password = Trim(ActiveCell.Value2)
ActiveCell.Offset(1, 0).Activate
Db = Trim(ActiveCell.Value2)
ActiveCell.Offset(1, 0).Activate
Server = Trim(ActiveCell.Value2)

Set oCompany = New SAPbobsCOM.Company

oCompany.DbServerType = dst_MSSQL2012
oCompany.Server = Server
oCompany.DbUserName = sqluser
oCompany.DbPassword = Password
oCompany.CompanyDB = Db
oCompany.UserName = B1UserID
oCompany.Password = B1Password
IRetCode = oCompany.Connect
If IRetCode <> 0 Then
sErrMsg = oCompany.GetLastErrorDescription
MsgBox (sErrMsg)
MsgBox ("Connected To:" & oCompany.CompanyName)

row_no1 = 2

Count2 = 1

Dim vDoc As SAPbobsCOM.Documents
Set vDoc = oCompany.GetBusinessObject(oInvoices)
Dim oCancelDoc As SAPbobsCOM.Documents
Set oCancelDoc = oCompany.GetBusinessObject(oInvoices)

    inv_docentry = Worksheets("Invoice Entry").Range("A" & row_no1 & "").Value
    If inv_docentry = "" Then Exit Sub
    GoSub Cancel_Invoice
    row_no1 = row_no1 + 1
    GoTo Get_Invoice
    vDoc.GetByKey (inv_docentry)

    Set oCancelDoc = vDoc.CreateCancellationDocument
     Dim YourString As String
     YourString = vDoc.Cancelled
     If vDoc.Cancelled = tNO Then
     Dim date1 As String
      date1 = Format(vDoc.DocDate, "yyyymmdd")
    oCancelDoc.DocDueDate = vDoc.DocDate
    IRetCode = oCancelDoc.Add()
   If IRetCode <> 0 Then
sErrMsg = oCompany.GetLastErrorDescription
MsgBox (sErrMsg)

End If
MsgBox ("DocEntry already Cancel " & inv_docentry)
End If
End If
End Sub

Hope this artcle will help you and save your time.
Note: Before processing it with production Database please check it with Test Database and process at your own risk NeGUL is not responsible for any damage.