0

I'm trying to automate certain procedures in SAP using Excel Templates and VBA.

To extract data, I'm using INST_EXECUTE_REPORT via RFC. I packed it into a separate Sub in VBA, in which I declare a local variable ObjR3_EXECUTE_REPORT:

Set ObjR3_EXECUTE_REPORT = ObjR3.Add("INST_EXECUTE_REPORT")

'Define SAP Tables
With ObjR3_EXECUTE_REPORT
    Set ObjR3_EXECUTE_REPORT_Name = .Exports("PROGRAM")
    Set ObjR3_EXECUTE_REPORT_Para = .Tables("PARA")
    Set ObjR3_EXECUTE_REPORT_Result = .Tables("RESULT_TAB")
    Set ObjR3_EXECUTE_REPORT_Output = .Tables("OUTPUT_TAB")
End With

'Define SAP source table
ObjR3_EXECUTE_REPORT_Name.Value = ReportName

FieldTablesArray = Array(ObjR3_EXECUTE_REPORT_Para, ObjR3_EXECUTE_REPORT_Result, ObjR3_EXECUTE_REPORT_Output)  ',

For a = 0 To UBound(FieldTablesArray, 1)
    Set InnerSAPTable = FieldTablesArray(a)
    
    r = InnerSAPTable.RowCount
    For f = 1 To r
        InnerSAPTable.DeleteRow (1)
    Next f

Next a

Dim aParameterPair() As Variant
Dim aParameterInput() As Variant
Dim sParameterName As String
Dim sParameterInput As String

'Build up the table with the fields to be sellected
f = 1
For a = LBound(aParameters) To UBound(aParameters)
    aParameterPair = aParameters(a)
    aParameterInput = aParameterPair(UBound(aParameterPair))
    sParameterName = aParameterPair(LBound(aParameterPair))
    
    
    For c = LBound(aParameterInput) To UBound(aParameterInput)
        sParameterInput = aParameterInput(c)
        ObjR3_EXECUTE_REPORT_Para.AppendRow
        ObjR3_EXECUTE_REPORT_Para(f, "PARA_NAME") = sParameterName
        ObjR3_EXECUTE_REPORT_Para(f, "PARA_VALUE") = sParameterInput
        Debug.Print sParameterName & " " & sParameterInput
        
        f = f + 1
    Next c
    
Next a

CallResult = False

ObjR3_EXECUTE_REPORT.Imports("RESULT_TEXT").Clear
ObjR3_EXECUTE_REPORT_Output(1, 1) = sCaptionRow

'Call the ABAP function to extract the Report's output table to an object
CallResult = ObjR3_EXECUTE_REPORT.Call

'Debug.Print ReportName & ": " & ObjR3_EXECUTE_REPORT.Imports("RESULT_TEXT")

If CallResult = False Then
     
    Debug.Print ObjR3_EXECUTE_REPORT.Imports("RESULT_TEXT")
    Exit Sub

Else

    Debug.Print ReportName & ": " & ObjR3_EXECUTE_REPORT_Output.RowCount & " data rows downloaded successfully)"
    If ObjR3_EXECUTE_REPORT_Output.RowCount = 0 Then Exit Sub
End If

Please note that ObjR3 is a public variable:

Public ObjR3 As Object
Set ObjR3 = CreateObject("SAP.Functions")

To achieve the result I want, I first need to get the GL Account balances using program ReportName = "RFSSLD00" then, if the balance of a specific account is not 0, I want to download all individual transactions, but only in the last 60 days, using program ReportName = "RFITEMGL".

As long as there is at least one transaction in the last 60 days, the whole process works as expected. However, if there is no transaction in this time period, instead of returning a blank result, SAP returns the result of the ReportName = "RFSSLD00" program as soon as the ObjR3_EXECUTE_REPORT.Call function is executed.

I executed the routine in break mode, and before the second time ObjR3_EXECUTE_REPORT.Call is executed, the ObjR3_EXECUTE_REPORT_Output.RowCount of the Output table is 0. As soon as the call is ecexuted, so it was empty before the call.

If I reverse the order of both steps, i.e. first check for transactions in the last 60 days (RFITEMGL), then get the balance (RFSSLD00), the ObjR3_EXECUTE_REPORT_Output.RowCount for RFITEMGL is 0, as expected. However, I need the correct order for the procedure to make sense.

I'm at a loss here. Is there any way to make SAP "forget" the result of the first ObjR3_EXECUTE_REPORT.Call ? I tried removing the ObjR3_EXECUTE_REPORT, but it didn't work:

ObjR3.Remove ObjR3_EXECUTE_REPORT

Any suggestions would be welcome.

Best regards,

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Tyro
  • 45
  • 1
  • 8
  • `If I reverse the order of both steps, i.e. first check for transactions in the last 60 days ... However, I need the correct order for the procedure to make sense` RFSSLD00 and RFITEMGL are two independent reports, nothing prevents you from running RFSSLD00, fetch ALL the balances of ALL the G/L accounts (pre-fetch) and then loop-n-fetch via RFITEMGL only those accounts which balances are not zero. Running those reports in certain order is nonsensity – Suncatcher Nov 11 '22 at 22:37
  • "Nonsensity" is a strong word, and one that does nothing to help me solve my problem. My script does EXACTLY what you suggest, and RFSSLD00 still “bleeds” results into RFITEMGL if RFITEMGL is empty. Yes, this makes little sense, but this is what happens, I’ve tested it. Also, you’re only half-right. RFSSLD00 and RFITEMGL are two independent reports, but they’re both being called via the same RFC function INST_EXECUTE_REPORT. I read the results from same table .Imports("RESULT_TEXT"). SAP “remembers” the results of the first report if the second one is empty. Question is how to make it forget. – Tyro Nov 15 '22 at 09:57
  • `and RFSSLD00 still “bleeds” results into RFITEMGL` this is the issue of your implementation, not SAP architecture. Maybe you should use two **ObjR3_EXECUTE_REPORT**-like vars and not messing both reports into single one? I cannot help with the VBA issues here, I just wanna highlight that SAP does not “remember” anything by design, all RFC functions including INST_EXECUTE_REPORT are stateless. It is your script that remembers it wrong – Suncatcher Nov 18 '22 at 03:31
  • I'm sure I'm doing something wrong, but it's not VBA that "remembers" the state. I've double-checked in the "Locals" window. The ObjR3_EXECUTE_REPORT_Output is clear before the .call function is executed. I'm almost sure its' SAP that sends the "old"result back, maybe because the the "INST_EXECUTE_REPORT" persists in the memory until it is killed. Anyways, I've managed to avoid the whole issue by using "BAPI_GL_ACC_GETPERIODBALANCES" instead to get the GL account balance. It's actually a more elegant solution for my use case. But the question still remains to avoid errors in future. – Tyro Nov 30 '22 at 20:37
  • I'm really out of my wits here. I've changed `ObjR3_EXECUTE_REPORT` to a local rather than a public variable. I've also added `ObjR3_EXECUTE_REPORT_Output.FreeTable` and `ObjR3.Remove ObjR3_EXECUTE_REPORT` at the end of the sub to ensure that the objects are cleared. They do get cleared, I've checked it in break mode. Right before the execution of `ObjR3_EXECUTE_REPORT_Output.FreeTable` I can access the values e.g. through `ObjR3_EXECUTE_REPORT_Output(1,1)`, immediately thereafter I get a runtime error. So the object gets destroyed in VBA. It's only after the `.Call` that it get's recreated. – Tyro Feb 14 '23 at 17:23

0 Answers0