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
https://www.nevergiveuplearning.com/2020/03/create-command-button-and-apply-macro.html 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()
login
End Sub
Code For Module2
Public Sub login()
Sheets("Credintial").Select
Worksheets("Credintial").Range("B1").Activate
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)
Else
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)
Get_Invoice:
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
Cancel_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)
Else
End If
Else
MsgBox ("DocEntry already Cancel " &
inv_docentry)
End If
Return
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.