NWBC SAP and Excel VBA

Question

If I want to use Excel VBA for gathering information from an NWBC started SAP instance, how can I / could I manage to do that? In case I start my NWBC SAP (over Citrix connection) and I start my code via an Excel instance within NWBC, code is working well...

Sub Start_SAP_things()
  If Not IsObject(Application) Then
   Set SapGuiAuto = GetObject("SAPGUISERVER")
   Set SAPApp = SapGuiAuto.GetScriptingEngine
  End If
  
  If Not IsObject(Connection) Then
   Set Connection = SAPApp.Children(0)
  End If
  If Not IsObject(session) Then
   Set session = Connection.Children(0)
  End If
  If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
  End If
  session.findById("wnd[0]").maximize
  'And so on
End Sub

But is there any possibility to use an Excel-Macro-File from a "desktop-excel" connecting to NWBC SAP? (right now I unfortunately have create an export form SAP (eg. an ALV-Grid) in order have an active NWBC-Excel instance and then call the procedure... pretty nasty)

Best regards and thanks in advance.

House MD

`via an Excel instance within NWBC` how do you start an Excel instance inside NWBC? — Suncatcher, Sep 02 '20 at 09:23
Hello! In case I export data from Grid control by using the export command and then using "Export -> Spreadsheet". In my company this brings up an Excel instance within Citrix. — HouseMD, Sep 03 '20 at 19:36
try [this](https://stackoverflow.com/a/58348742/911419) or [this](https://stackoverflow.com/questions/24297063/pulling-data-from-sap-using-excel-macros) approach — Suncatcher, Sep 04 '20 at 07:09

score 0 · Answer 1 · answered Sep 08 '20 at 04:45

If I understand you correctly, you want to run a SAP script from EXCEL. If so, then this is what you need:

    Option Explicit

    Private Sub CommandButton1_Click()

    On Error GoTo Err_NoSAP

    Dim SAPguiApp As Variant
    Dim SapGuiAuto As Object
    Dim Connection As Variant
    Dim Session As Variant
    Dim WScript As Variant
    Dim aw As Object

    If Not IsObject(SAPguiApp) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set SAPguiApp = SapGuiAuto.GetScriptingEngine
    End If
    
    If Not IsObject(Connection) Then
       Set Connection = SAPguiApp.Children(0)
    End If
    
    If Not IsObject(Session) Then
       Set Session = Connection.Children(0)
    End If
    
    If IsObject(WScript) Then
        WScript.ConnectObject Session, "on"
        WScript.ConnectObject Application, "on"
    End If
    
    If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP
    
    Set aw = Session.ActiveWindow()
    aw.findById("wnd[0]").maximize
    
    On Error GoTo Err_Description
  
    'Your SAP script code here:
    '====================================================================================================================================
    
    'Example code - replace with yours.
    Session.findById("wnd[0]/tbar[0]/okcd").Text = "IW38"
    Session.findById("wnd[0]").sendVKey 0
        
    '====================================================================================================================================
       
    Exit Sub
        
      Err_NoSAP:
    
        MsgBox ("You don't have SAP open or scripting has been disabled."), vbInformation, "For Information..."
            Exit Sub
    
      Err_TooManySAP:
    
        MsgBox "You can only have one SAP session open." & Chr(13) & Chr(13) & _
               "Please close all other open SAP sessions.", vbInformation, "For Information..."
            Exit Sub
           
      Err_Description:
    
        MsgBox "The program has generated an error, the reason for this error is unknown." & Chr(13) & Chr(13) & _
        "If the error persists, try closing and re-opening SAP." & Chr(13) & Chr(13) & _
        "Microsoft's definition for the error is: " & Err.Number & " - " & Err.Description & ".", vbInformation, "For Information..."
          
    End Sub

NWBC SAP and Excel VBA

1 Answers1