Extracting data from SAP’s spool job using BAPI_XBP_JOB_SPOOLLIST_READ and Excel's VBA

[tyrogreenhorn]

Hi there,

I'm trying to build interactive Excel templates that extract data from SAP’s spool job using BAPI_XBP_JOB_SPOOLLIST_READ. The way I’m approaching the problem is:

1. Run a Transaction Code by means of RFC_CALL_TRANSACTION_USING and get SAP to print out the result to spool. The spool job number is returned by the Function Module.
2. Log onto the XBP interface using BAPI_XMI_LOGON.
3. Extract the content of the spool job using BAPI_XBP_GET_SPOOL_AS_DAT based on the spool job number obtained in Step 1 (originally I was using BAPI_XBP_JOB_SPOOLLIST_READ, but change'd it after Sandra's answer)
4. Log off the XBP interface using BAPI_XMI_LOGOFF.
5. Delete the spool job using RSPO_R_RDELETE_SPOOLREQ.

I’ve succeeded with Step 1. I've also managed Step 2. I can successfully log onto the XBP interface with BAPI_XMI_LOGON, using the following parameters: (EXTCOMPANY = "RFC_Excel", EXTPRODUCT = "RFC_Excel", INTERFACE = "XBP", VERSION = "3.0"), which returns a valid SESSION_ID. Steps 4 and 5 are also working fine.

Where I'm having troubles is in Step 3. The call gets executed but the .Tables("SPOOL_LIST").RowCount returns an error saying that "Run-time error '438' Object doesn't support this property or method" as if the object was not a table and the output of .Tables("SPOOL_LIST") is empty, as if the spool request had no content.

The Code I'm using is:

Sub SAP_Spool_Job_Read_From_SAP(sSpoolJobNo As String)
    CallResult = False
    
    If sSpoolJobNo = "0" Then Exit Sub
    
    Dim sExternalUserName As String
    Dim ObjR3_RFC_Spool_LogOn As Object
    Dim ObjR3_RFC_Spool_LogOff As Object
    Dim ObjR3_RFC_Spool_GetData As Object
    Dim sFilePath As String
    Dim lSpoolJobNo As Long
    Dim oSpoolOutput As Object
    Dim oSpoolErrors As Object
    
    Dim sXBP_SessionID As String
    Dim sXBP_Message As String
    Dim oXBP_Status As Object
    
    sExternalUserName = "RFC_Excel"
    lSpoolJobNo = sSpoolJobNo * 1
    
    ObjR3_Connection.RFCWithDialog = 1
    
    Set ObjR3_RFC_Spool_LogOn = ObjR3.Add("BAPI_XMI_LOGON")
    
    'Log onto the XBP interface
    With ObjR3_RFC_Spool_LogOn
        .Exports("EXTCOMPANY") = sExternalUserName
        .Exports("EXTPRODUCT") = sExternalUserName
        .Exports("INTERFACE") = "XBP"
        .Exports("VERSION") = "3.0"
    End With
    
    CallResult = False
    sXBP_SessionID = "?"
    sXBP_Message = "Pending..."
    CallResult = ObjR3_RFC_Spool_LogOn.Call
    
    'Check if XBP logon was successful
    If CallResult = True Then
        sXBP_SessionID = ObjR3_RFC_Spool_LogOn.Imports("SESSIONID").Value
        Debug.Print "ObjR3_RFC_Spool_LogOn: " & CallResult
        Debug.Print "ObjR3_RFC_Spool_LogOn: " & ObjR3_RFC_Spool_LogOn
        Debug.Print "Session ID: " & sXBP_SessionID
        Debug.Print ObjR3_RFC_Spool_LogOn.Imports("RETURN").Value
    Else
        Exit Sub
    End If

    'Get Data from Spool content from SAP
    Set ObjR3_RFC_Spool_GetData = ObjR3.Add("BAPI_XBP_GET_SPOOL_AS_DAT")
    Debug.Print lSpoolJobNo
    With ObjR3_RFC_Spool_GetData
        .Exports("SPOOL_REQUEST") = lSpoolJobNo
        .Exports("EXTERNAL_USER_NAME") = sExternalUserName
        .Exports("FIRST_PAGE") = 0
        .Exports("LAST_PAGE") = 0
        Set oSpoolOutput = .Imports("SPOOL_LIST")
         Set oSpoolErrors = .Imports("RETURN")
    End With
    
    CallResult = False

    'Call Request in SAP
    CallResult = ObjR3_RFC_Spool_GetData.Call
    
    If CallResult = True Then
        Debug.Print ObjR3_RFC_Spool_GetData.Exports("SPOOL_REQUEST")
        Debug.Print "ObjR3_RFC_Spool_GetData: " & CallResult
        Debug.Print oSpoolErrors.Value
        Debug.Print "Content: " & vbNewLine & oSpoolOutput
    Else
        GoTo LogOffXMI
    End If
    

LogOffXMI:
    
    Set ObjR3_RFC_Spool_LogOff = ObjR3.Add("BAPI_XMI_LOGOFF")

    With ObjR3_RFC_Spool_LogOff
        .Exports("INTERFACE") = "XBP"
    End With
    
    CallResult = ObjR3_RFC_Spool_LogOff.Call
    
    If CallResult = True Then
        Debug.Print "ObjR3_RFC_Spool_LogOff: " & CallResult
        Debug.Print ObjR3_RFC_Spool_LogOff.Imports("RETURN").Value
    Else
        Exit Sub
    End If
End Sub 

A couple of remarks: As far as I can tell, the problem is not authorization rights, as I have dealt with all authorization errors (for both sExternalUserName = "RFC_Excel" and SU53 is clear. In addition, I've tested steps 2 through 4 using SE 37 and it works fine (SPOOL_LIST contains the content of the spool list). But I cannot get it to work in Excel's VBA. Does anyone have any idea what I'm doing wrong? I feel like the solution is just round the corner but I'm just too blind to see it. I would appreciate any idea. Alternatively, does anyone have any ideas how to read the spool content in a different way using RFC?

Cheers!

[zerzourrabah]

@tyro You overlooked one essential step that is documented in the following document https://ftp.gwdg.de/pub/misc/sapdb/icc/bc-xbp/BCXBPTCV2V3.pdf

AUDITLEVEL should be raised from 0 to 2 to read spool content In my case it worked like a charm

It stated Page 35:

6.1 Reading the Content of a Spool Request Specified by Number Description In this scenario, the content of a spool request that has been specified by its number is to be retrieved via the XBP interface. Prerequisites A connection to an SAP system is established. The corrections of note 1171295 have to be in the test system. The audit level is set to the value 2, so that the use of the required functions can be verified in the XMI log (RZ15).

and Page 14

3.12 Setting the Audit Level XMI Description Set the audit level for the XMI logging higher/lower. This function is mandatory, because it is the only way to set the audit level within the XMI interface. Explanation: The XMI framework, which allows external Management Tools to log on to the SAP system and create XMI-sessions, also offers the possibility of writing a trace of the functions called during an XMI-session. The trace level can be set with the function BAPI_XMI_SET_AUDITLEVEL and can be viewed in the SAP system using transaction rz15. In the terminology of XMI, the trace level is called audit level. If the function BAPI_XMI_SET_AUDITLEVEL is not called explicitly in an XMI session, the audit level is 0 by default. Most XBP functions that make changes of some description in the system (create jobs, change jobs etc.), are already traced at audit level 0. Most XBP functions that only read information (such as BAPI_XBP_JOB_STATUS_GET) are only traced with audit level 2 or higher. Prerequisites A connection to an SAP R/3 System is established. A (XBP) session has to be established through the XMI interface. Realization The audit level of the XMI interface should be raised to 2 using the external job scheduling system. Use BAPI_XMI_SET_AUDITLEVEL . Then a status check of an existing job should be performed by the external scheduler If everything worked fine, set the audit level back to the default value 0.