|
|
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.
| |
| |
| |
Bypassing the Need to use the "Prompt" button |
|
| |
Call Application.Run("SAPSetRefreshBehaviour", "Off")
Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
Dim lResult As Long ' Used for debugging only
lResult = Application.Run("SAPSetVariable", "your variable name", "your value", "INPUT_STRING", "your dataset")
If lResult = 0 Then
vLastError = Application.Run("SAPGetProperty", "LastError", "Text")
MsgBox vLastError
Exit Sub
End If
Call Application.run("SAPExecuteCommand", "PauseVariableSubmit", "Off")
Call Application.run("SAPSetRefreshBehaviour", "On")
Call Application.run("SAPExecuteCommand", "RefreshData", "ALL")
|
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 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.
|
|
| |
| |
| |
Converting SAP dates to Excel Format |
|
| |
Dim RunDate As Date ' Date of run
Dim DateDbl As Double ' To convert to double word
Dim StrWork As String ' Work String
' Now handle the date
StrWork = Cell that contain SAP Date value
StrWork = DateSerial(Mid(StrWork, 1, 4), _
Mid(StrWork, 5, 2), _
Mid(StrWork, 7, 2)) ' Reformat date
RunDate = DateValue(StrWork)
DataDbl = CDbl(RunDate)
|
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.
|
|
| |
| |
|
|
