Writeback Examples – SAP Business One

Example Excel Workbook

Before writting data from Excel back into SAP Business One via the SAP DI API library please ensure the correct setup procedures have been completed.
Sharperlight supports both SQL Server and HANA and there is another article that covers this setup

Excel writebacks follows these basic steps

1. ​Create a Location for the Company Reference at the top of the Sheet
2. Create the basic layout with headers and details that matches the target writeback Table fields
3. Map the columns of the sheet to the Writeback Window using a mix of cell references and constants
4. Use the Lookup on the field’s right click menu to explore valid values when not sure what type of data is required
5. If you want errors and other feedback to appear in a cell of the worksheet set the Process OptionsLogging Cell
6. To execute the writeback based on a Button create a button and set the macro as mdWriteback and set the name the same as the Writeback name

​SL_WritebackTemplate_V1_2.xlsx

Excel Sharperlight Ribbon

Journal Entry Writeback

This writeback creates a table of Journal Entries​​. Validation of data takes two steps.  The first step is done by Sharperlight and the next level is done by the SAP DI API which enforces the more complex rules.  Data is only written back if all validation levels are cleared.

The areas of the worksheet are mapped to the Writeback Cells of the selected Table which in this case is Journal Entry

It is recommended that you make the Company a cell reference and use the first mapping column as the Driver Range.  Driver Ranges define the general area to look for data.  If there are blank rows in the Driver Range these will be ignored.

Budget Matrix Writeback

This writeback creates a matrix of Accounts by Period.  Notice in the perview all the combinations of Accounts by Period have been generated.

The key to the matrix writeback

1. Set the Accounts as a Driver Range =$E$7:$E$1000 going down   (Notice the static cell reference format)
2. Set the Periods =$I$6:$T$6 going across  (Notice the static cell reference format)
3. Set the first row of the Amounts ​​=I7:T7   (Notice the relative cell reference format)​
4. ​Set other data columns as Line Memo  =H7:H1000

Video showing the general concept of Excel Writebacks​

The 8 minute mark of the video will show Writeback mapping and Button Binding

Display Writeback feedback in a Worksheet cell

If you want errors and other feedback to appear in a cell of the worksheet set the Process OptionsLogging Cell​ with a cell location.

As the feedback can be quite long use Format merge cells to increase the visible area of the Logging Cell

Create Button to Execute Writeback

1. ​Create a Sha​pe of any type on the Worksheet and set the Text
2. Assign the macro name to be mdWriteback  This macro name will both validate and execute the Writeback
3. Set the name of the Shape to match the Writeback name used for the Writeback