Excel Sample Templates

knutheusermann · ‎05-15-2015

Using Microsoft Excel VBA and SAP Business ByDesign web services you can build Excel templates to process mass updates of ByDesign data, tailored to your specific mass change scenario.

This blog post

briefly presents a sample Excel template to query and change ByD data

explains how to adapt the Excel template to change other fields or other ByD business objects

explains ByD web service features to efficiently process mass updates using ByD web services

provides you a collection of sample Excel templates

Background:

ByD offers standard MS Excel integration scenarios to download data from ByD work centers into Excel ("Export to Microsoft Excel") as well as creating new data from Excel (for example "New Journal Entry Voucher from Microsoft Excel"). However, often very specific mass change scenarios are required, for example to change values of extension fields.

This page is about building such specific mass change solutions using Microsoft Excel and ByD web services.

Excel Sample Templates

Please find at the following link some example Excel templates for mass changes of ByD data with Excel VBA and ByD web services: ByD Excel VBA Samples

The share includes the following sample Excel templates:

SAP_ByD_MassChange_SupplierGeneralData.xlsm:
Very simple, mass updates of ABC classification on supplier header level - good to get familar with the Excel template.

SAP_ByD_MassChange_MaterialSalesData.xlsm:
Reads product sales data (sub-node of products) and enables mass-updates of sales lifecycle status codes; reads product descriptions in a given language.

SAP_ByD_MassChange_Material_TaxClassification.xlsm:
Reads material ID, descriptions in a given language and product tax classifications, and enables mass-updates of tax rate types and tax exemption reasons.

SAP_ByD_MassChange_Customer_CreateFromBupa.xlsm:
Reads business partners and enables to assign the roles "Customer" and "Prospect" to existing business partners. Check as well blog post Assign Role Supplier to an existing Business Partner via Web Service ManageSupplierIn.

Please note:

The Excel templates are no SAP products but instructive coding examples how to use ByD web services with Microsoft Excel and VBA.

The Excel samples contain Marcos and Visual Basic (VBA) modules and forms. Your security policy must allow VBA/Macro execution; the Excel template requires macros to be enabled.

In the sheets there are several named areas defined that are used in the VBA modules. The VBA coding uses MSXML 6.0.

Data retrieval and update are being performed via https web service calls to your SAP ByD System.

I tested the examples for Windows 7, Windows 8 and Windows 10, and Excel 2010 32-bit, Excel 2013 32-bit and 64-bit, and Excel 2016.

How to use the Sample Excel?

Example scenario

The purpose of the following sample Excel template is to change the ABC classification of ByD supplier:

Query and download all relevant supplier from ByD into your Excel template

Change the ABC classification of the supplier offline

Write back the changed supplier ABC classifications to ByD

To ensure data integrity and to avoid accidental changes, you would like to make sure, that the Excel can be used to change the ABC classification of existing suppliers only. It shall not be possible to create supplier, to delete supplier or to change any other supplier data except the ABC classification.

The sample Excel template SAP_ByD_MassChange_SupplierGeneralData.xlsm is designed to serve this scenario.

Create a user to access the ByD web services

The Excel template in our example uses the ByD web services QuerySupplierIn and ManageSupplierIn to query, read and update ByD data. The logon user used in the Excel template must have authorizations for these ByD web services.

Steps to create a user in ByD:

Create a communication system representing the Excel template
1. Open ByD work center Application and User Management, view Communication Systems
2. Create a new communication system via button New
  1. Enter an ID, host name (any name… irrelevant for this scenario) and the System Access Type "Internet"
  2. Add a system instance via button Add Row and enter a System Instance ID and the Preferred Application Protocol "5 - Web Service"
  3. Activate the communication system via button Actions >> set to Active
  4. Save and close the communication system

Create a communication scenario incl. all required ByD web services
(alternatively to step 2 you can use standard communication scenarios provided by SAP as well)
1. Open ByD work center Application and User Management, view Communication Scenarios
2. Create a new communication scenario via button New
  1. Enter a communication scenario name
  2. Press button Add Operations, filter by the service description "*Supplier*" and select the operations with service description "Check suppliers", "Maintain suppliers" and "Find suppliers"
  3. Press button OK, save and close the communication scenario

Create a communication arrangement
1. Open ByD work center Application and User Management, view Communication Arrangements
2. Create a new communication arrangement via button New
  1. Select the communication scenario from step 2 and continue with button Next
  2. Select the communication system from step 1 and continue with button Next
  3. Select the authentication method "User ID and Password", edit the credentials and continue with button Next
  4. Review your settings and complete the communication arrangement with button Finish

If you have already a suitable ByD user and communication arrangement, then you can change the credentials via ByD communication arrangement:

Open ByD work center Application and User Management, view Communication Arrangements
1. Select and open the communication arrangement which includes the required ByD web services
2. Click on button View All
3. Goto tab Technical Data
4. On section Inbound Communication: Basic Settings:
  1. Select the Authentication Method of your choice (e.g. user ID and password for logon)
  2. Edit the credentials via button Edit Credentials and change user/password or upload a client certificate.

Query and Change ByD Supplier using the Excel Template

Open the sample Excel template SAP_ByD_MassChange_SupplierGeneralData.xlsm.

The Excel file consists of 5 pages:

_DATA_MASTER contains the ByD data. Using the menu buttons “Query”, the data is retrieved from the ByD system. If you change data in the relevant field(s), the corresponding records will be marked as modified. Using the menu buttons “Simulate” and “Update” the changed records will be sent back to the ByD system (for simulation resp. update as expected).

Query allows you to filter the data that is retrieved via button „Query“

Config holds important design time parameters. Most of the parameters are consisting of XML snippets.

Terms and Conditions contains information about the source and usage of the sample Excel template.

Query ByD Supplier:

Open Tab Query and enter suitable selection parameters

Query and read ByD supplier via button "Query" of section "SAP ByD" (Excel quick access toolbar, tab Home); enter your ByD system and user/password on the logon popup (the Logon screen automatically pops up on the first query):

Review the extracted supplier records on tab "_DATA_MASTER"

Change Supplier ABC Classification and update Supplier in ByD:

Change the ABC classifications in column E
Observe, that all touched suppliers get the status "Modified" in column B:

Optional: Simulate the update of ByD supplier using button "Simulate" of section "SAP ByD" (Excel quick access toolbar, tab Home).
Observe: successfully checked lines are marked green, lines with errors are marked red and system messages are added as field comments in column B.

Write back your changes to ByD using button "Update".
If you simulated the update before, then Excel only sends lines to ByD that are marked "OK".
If you did not simulate the update, then Excel sends all modified lines to ByD. In this case the complete update is rejected if one or more line are erroneous. Successfully updated lines are marked green, unprocessed lines or lines with errors are marked yellow or red and system messages are added as field comments.

Press "Update" again to repeat the write back to ByD for the unprocessed lines w/o errors.

Test mode:

On tab "Config" you can set the Test Mode = "On" to review web service requests and responses.

Volumes:

Volume recommendations strongly depend on the business document and the data retrieved and updated per business document instance.

As an example I recommend the following limits for ByD product data:

Query: The query process uses paging and can load high numbers of records. However, to avoid too long runtimes, I recommend to choose selection parameter values such that the total number of hits (e.g. material - sales organization - distribution channel combinations) does not become too high (< 10.000 records).

Update/Simulate: Assume all changed records are updated to ByD in a single web service call. To avoid timeouts, I recommend editing a limited number of records for each update/simulation run only (start with < 500 and check what is possible for your use case).

Value Help

The Excel template does not yet support value helps. Feel free to enhance the sample Excel; helpful ByD web services are QueryCodeListIn and QueryObjectDescriptionIn (see as well How to get ByDesign Code Lists via Web Service QueryCodeListIn).

ByD web service concepts to efficiently process mass updates

ByD web services provide a comprehensive set of web service delta handling features to process data updates.

For data integrity reasons and to avoid accidental mass changes via Excel, I would recommend to use those delta handling features to update dedicated fields only.

Below I describe the main features which I used in the Excel templates above.

Please check the Web Services on SAP Business ByDesign – SAP Help Portal Page for more details.

The examples below refer to the samples Excel template SAP_ByD_MassChange_MaterialSalesData.xlsm.

Please find attached the used sample web service request and response xmls.

Retrieve minimum ByD data needed:

Use the node element RequestedElements to specify which nodes of ByD business objects shall be returned by the query web service.
Transmission request codes are:

1	Complete structure: The node element and all its elements, its child node elements and all their elements will be returned
2	Complete node: The node element and all its elements will be returned
4	Excluded node: The node element and its child node elements will not be returned

In the material sales data example above I would like to retrieve the material ID, material description and material sales data. Hence I use the transmission request code 2 for the header data (root level), transmission request code 1 for the description and transmission request code 2 for sales data (I'm not interested in sales nodes, so I request the sales top level data only). All other material data shall not be returned and hence I set transmission request code 4 (actually that would have been not necessary because the transmission request code 2 on header level implies 4 as default one level below).

Use action codes "06 - No Action" and "02 - Update" to change dedicated business object nodes only:
Action code 06 makes sure that the business object node is not changed.
Action code 02 updates existing node instances; if the node instance does not exist the system returns an error.
In the attached example I use action code 06 on header level to make sure that no header level data is changed. In the sub-node Sales I use action code 02 because that that is the sub-node I want to change. I don't use "01 - Create" or "04 - Save" because I want to make sure, that existing material sales node instances are updated only and no instances are create accidentally (e.g. if a user changes the distribution channel in some Excel line).

Use list complete transmission indicator "false" to make sure that you don't overwrite lists:
The list complete transmission indicator "false" makes sure that all business object node instances not listed in the web service request remain unchanged. For example by setting attribute salesListCompleteTransmissionIndicator "false" I make sure that only the sales node instances that have been changed in my Excel are changed in ByD as well. All other sales node instances of the same material remain untouched. If I would transfer salesListCompleteTransmissionIndicator "true", then the system would assume that the list of transferred material sales node instances to be complete and hence delete all sales node instances which are not included in the material instance of the web service request.

Create update xml request with key elements and elements that shall be changed only:
You have to list all key elements in the update request xml, which are required to identify the node instance that shall be changed.
Elements that are not listed in the xml remain unchanged in ByD.
Example: In the sub-node Sales I provide the key elements SalesOrganisationID and DistributionChaannelCode (both are read-only) and the element LifeCycleStatusCode which is subject of the mass change. By omitting all other elements, I make sure that those are not changed accidentally.

Use object node sender technical IDs to assign system messages to Excel table lines:
The object sender technical IDs which you provide in the update request xml are returned by ByD in log items of the web service response. You can use that feature to assign ByD system messages to the corresponding Excel lines.
In the sample Excels the corresponding line number of table _SAP_DATA_0001 is added to the web service request as ObjectNodeSenderTechnicalID. As this ID is included in log items of the web service response, you can assign these log items to Excel table lines using VBA.

Adapt the Excel Template to your specific mass-change scenario

Excel VBA and XMLHTTP:

The Excel templates use VBA and the XMLHTTP object to call ByD web services. The world wide web offers plenty of good tutorials how to use VBA and XMLHTTP.

The attached Excel templates are build for ByD web services following the Query - CheckMaintainBundle - MaintainBundle pattern. These kind of ByD web services have the characteristics that

you can query and read deeply structured ByD business objects with one web service call (Query interface),

you can update multiple deeply structured business object instances of the same type in a single web service call (MaintainBundle operations) ,

you can simulate the update of multiple deeply structured business object instances of the same type in a single web service call (CheckMaintainBundle operations),

MaintainBundle and CheckMaintainBundle operations share the same xml payload structure.

Any ByD mass-change scenario based on such web services can be realized using the following guideline without deep VBA knowledge.

You can develop mass-change scenarios using other kind of ByD web services as well (incl. web services generated using SAP Cloud Applications Studio), but this would require some more VBA coding changes than described below.

Steps to adapt the Excel template:

Step 1: Copy Excel template.

Step 2: Tab "Config"

Adapt URL Query and URL Maintain.
For example /sap/bc/srt/scs/sap/querysupplierin1 and /sap/bc/srt/scs/sap/managesupplierin1.
1. Get ByD web service access URL from ByD work center Application and User Management, view Communication Arrangements or Service Explorer

Assemble sample query request and response xmls:
1. Download WSDL from ByD work center Application and User Management, view Communication Arrangements.
2. Create sample request xml using a web service test tool (e.g. SoapUI).
3. Test sample request using the web service test tool.

Enter Query request xml template incl. default values and variables framed with ## (for example "##SelectionByInternalID##").
Observe: The xml must not contain soap envelop elements, but the payload only.
Do not change the variables used in processing conditions (##MaxHits##, ##UnlimitedHits##, ##LastReturnedObjectID##)!
Example:

Enter Query response: leading xml Node (xPath) which is the xml node that corresponds to a table line in Excel.
Example: Enter "//Supplier" if each table line corresponds to a supplier header instance.

Assemble sample request and response xmls for update (operation MaintainBundle) and simulation (operation CheckMaintainBundle😞
1. Download WSDL from ByD work center Application and User Management, view Communication Arrangements.
2. Create sample request xml using a web service tool (e.g. SoapUI)
3. Test sample request using the web service tool.

Enter Update request: xml template and Update request: xml template for update simulation.
These xmls are the xml frames for update and simulation web service requests. The included variable ##BDO## will be replaced by the business document object body during runtime.

Enter Update request: business document object body (BDO) incl. default values and variables framed with #...# (e.g. "##ABCClassificationCode##").
Use the variable name "##row##" for elements ObjectNodeSenderTechnicalID.
The BDO is used for simulation as well as update web service requests.Example:

Step 3: Tab "Query"

Maintain query parameter according the query parameter used in the Query request: xml template.
1. Do not change the cell names of the generic parameter "Max Hits" and "Unlimited"!
2. Rename Field ID in column C (e.g. rename C3 by SAPBYD_QueryParam_SelectionByInternalID);
  use Formulars > Name Manager to rearrange field names

Enter default values in column C.

Step 4: Tab "_DATA_MASTER"

Adapt title

Adapt steps to process; add important remarks if needed.

Adapt header line of table _SAP_DATA_0001;
1. Do not change column B!
2. Do not delete column A, B or C!
3. Insert additional columns if needed.
4. Delete columns not needed (do not delete column A, B and C).

Add remarks per column in line 4 if needed (e.g. code values).

Step 5: Adapt VBA program

Open Visual Basic Editor via Excel quick access toolbar, tab Developer

Open module "modInterfaceAdapter" (this module contains all Excel - web service mappings)
1. Map Excel Query Parameter (tab Query) to Web Service Query Request XML
2. Map Web Service Query Response XML to Excel content table (tab _DATA_MASTER)
3. Map Excel content table (tab _DATA_MASTER) to Web Service Update Request XML
4. Define columns that shall trigger an update (A=1, B=2,...)

Examples from Excel template SAP_ByD_MassChange_SupplierGeneralData.xlsm:

Example query parameter mapping:

Query request xml variable	Excel cell name of query parameter
##SelectionByInternalID##	SAPBYD_QueryParam_SelectionByInternalID
##SelectionBySortingFormattedName##	SAPBYD_QueryParam_SelectionBySortingFormattedName
##SelectionByCreatedSinceDateTime##	SAPBYD_QueryParam_SelectionByCreatedSinceDateTime
##SelectionBySearchText##	SAPBYD_QueryParam_SearchText

Example mapping between Excel table header and query response respectively update/simulation request:

Query response xml element	Update request xml variable	Excel table column header
InternalID	##InternalID##	Supplier ID
FirstLineName	<no update>	Supplier Name
ABCClassificationCode	##ABCClassificationCode##	ABC Classification

Step 6:

Change Excel file properties/advanced properties and adapt the "Prerequisites" on tab "Terms and Conditions".

Prepare Excel before sharing with others
1. Remove data on tab "_DATA_MASTER" (run query with empty result)
2. Remove target systems on tab "Config" (remove system favorites)
3. Remove private data

How to get Access URLs and WSDLs of ByD web services?

ByD UI: Open work center Application and User Management, view Communication Arrangements.

Select and open the communication arrangement to get the web service access URLs (service endpoints) and to download web service documentations and WSDLs.

Extension Fields with Namespaces

Excel VBA and ByD web services can be used to run mass updates on extension fields as well.

Adding extension fields to the query request as well as the update request and simulation request is straight forward following the guideline above; just make sure that you have the correct xml element names and that you added the additional xml namespace used for the extension fields in the xml snippets on tap "Config":

Query request xml template

Update request: xml template

Update request: xml template for update simulation

However, to parse and map extension fields of the query response some changes to the Excel VBA coding beyond mappings as described above are required.

Add the blue lines to your VBA coding to map extension fields in query response messages:

Module modMain >> Method cmdQuery:

…

Dim strUrl As String

Dim strNS As String

Dim xmlResponse As Object

…

   xmlResponse.setProperty "SelectionLanguage", "XPath" 'required for axis operations

   'Set namespace for extension fields

   Call QueryResponseXML_SetNamespace(strNS)

   xmlResponse.setProperty "SelectionNamespaces", strNS

…

Module modInterfaceAdapter >> Create new method

'-----------------------------------------------------------------------------

'Set Additional Namespace for Web Service Query Response XMLs:

'-----------------------------------------------------------------------------

Public Sub QueryResponseXML_SetNamespace(strNS As String)

   'Enter the custom namespace, for example: strNS = "xmlns:n1='http://sap.com/xi/AP/CustomerExtension/BYD/A0014'"

   strNS = "xmlns:n1='http://sap.com/xi/AP/CustomerExtension/BYD/A0014'"

End Sub

Module modInterfaceAdapter >> Method QueryResponseXML_MapElementsToColumns

…

    'Map extension field

    SetCellValue "External ID", "n1:ExternalID", oTable, oListRow, xmlRow

…

Mass Changes of ByD Data using Excel and ByD Web Services

Excel Sample Templates

How to use the Sample Excel?

Example scenario

Create a user to access the ByD web services

Query and Change ByD Supplier using the Excel Template

ByD web service concepts to efficiently process mass updates

Adapt the Excel Template to your specific mass-change scenario

Steps to adapt the Excel template:

How to get Access URLs and WSDLs of ByD web services?

Extension Fields with Namespaces

Useful documents on SCN

Evolution of ABAP

Analytics in S/4HANA - real shape of embedded analytics and beyond embedded analytics