The code on this page is for use with Microsoft Excel™ which should have
AFE enabled.
The code samples are not meant as a definitive 'how to' but they can be used as
a starting point.
SAP™ S4Hana is the industrial standard way of querying SAP databases from
an Excel™ spreadsheet. There are, however, some performance 'gotcha's that
need to be considered. Unfortunately many S4Hana users are not IT savvy and
this can lead to terrible response times and heavy back-end resource usage. Of
course, SAP will be happy to sell you more processing power to run their database
engine, but sometimes this can be avoided by SQL tuning.
The usual disclaimers apply to all our code samples (including the MACROs).
| Name |
Description | |
|---|---|---|
| SAP date format are often in the format yyyymmdd which makes sense for sorting dates but it is not very Excel friendly. The code alongside demonstrates how to convert a SAP date from yyyymmdd format into Excel date format. The format for Excel is the integer day number that most dates in Excel are stored in. | ||
|
| ||
| This will set a variable in a SAP dataset and then update the sheet using the final call for RefreshData. Several variables can be set by coding more "SAPSetVariable" statements. The variables can also be in different datasets i.e. DS_1, DS_2 etc. This technique is used by us to change the dates that are used for data extraction from SAP. We use a DatePicker form rather than have the end user use the AFE Prompt table which as it is a user-friendly as a cornered rat! You may want to refresh each data source individually in order to improve performance. To do this replace code: Call Application.run("SAPExecuteCommand", "RefreshData", "ALL") with the name of the data source (i.e "DS_1") and then replicate the line for each data source. | ||
|
| ||
