cancel
Showing results for 
Search instead for 
Did you mean: 

How to stop a script correct?

Former Member
0 Kudos

Hi,

I am using EXCEL VBA to import and export data between SAP and EXCEL sheets.

Everything works fine, but when the EXCEL VBA is finished the SAP-GUI stays in the "script mode".

The status says "scripting is in execution" (my translation may be not exact, because the status message is in German).

Do you have an idea, how I can terminate the conncetion to SAP-GUI correct?

Is there any command to do it?

Thanks, Franz

Accepted Solutions (1)

Accepted Solutions (1)

holger_khn
Contributor
0 Kudos

Hello Franz.

May you can post some code from you for review. As this seems caused by your code. I have not this issue when I develop some VBA scripts in Excel. Thanks.

Br, Holger

Former Member
0 Kudos

Hello Holger,

attached you find one example of my VBAs.

My comments are not very much and mainly in German.

If needed and helpful, I will translate or add more comments.

The Macro will start "SeminarTerminePflegen()"

Looking forward to hear about your comments - Thanks, Franz

VBA-Code:

--- Public - code - section ---

Option Explicit

'GUI connect
Public Applications
Public SapGuiAuto
Public Connection
Public Session
Public WScript

Public DT4M As Object 'Aktuelle DatenTabelle für Makrosteuerung
Public ActSAPtransaction As String

'Connect
Public Sub SAP_GUI_connect(transaction As String)
    If Not IsObject(Applications) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set Applications = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
       Set Connection = Applications.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 Applications, "on"
    End If
   
    ActSAPtransaction = transaction 'zur weiteren Kontrolle "public" speichern
    Session.findById("wnd[0]").maximize
    Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n " & ActSAPtransaction
    Session.findById("wnd[0]").sendVKey 0

End Sub

--- "local" - code - section ---

Sub SeminarTerminePflegen()
' Transaktion LSO_PV10
' Cell(R,C of "Mctrl").value = "trans" --> execute, interprete the line
' max. Zeilenanzahl = 2000

    Set SEMlist = ActiveWorkbook.Sheets("SeminarList")
    Call spaltenSEMlistZuteilen(SEMlist)
    statusInfo = ""
   
    'Connect
    SAP_GUI_connect ("LSO_PV10")

    'Startzeile!
    zeile = 2

    'max. 2000 Zeilen (Sicherheitsgrenze)
    Do Until (SEMlist.Cells(zeile, s_Mctrl).Value = "" Or zeile = 2000)
        If SEMlist.Cells(zeile, s_Mctrl).Value = "trans" Then
            SEMlist.Cells(zeile, s_Mctrl).Value = "start"
           
            If SEMlist.Cells(zeile, s_EventID).Value = "" Then
                'create new entry
                Session.findById("wnd[0]/usr/ctxtHRVPVA-ETSRK").Text = SEMlist.Cells(zeile, s_SeminarType).Value 'Seminar-ID
                Session.findById("wnd[0]/usr/txtHRVPVA-EVSRK").Text = "" 'Neu anlegen!
                Session.findById("wnd[0]/usr/ctxtHRVPVA-PEBEG").Text = SEMlist.Cells(zeile, s_Date).Value 'Beginndatum
                '<Datenbild> öffnen
                Session.findById("wnd[0]/tbar[1]/btn[20]").press
            Else
                Session.findById("wnd[0]/usr/ctxtHRVPVA-ETSRK").Text = SEMlist.Cells(zeile, s_SeminarType).Value 'Seminar-ID
                Session.findById("wnd[0]/usr/txtHRVPVA-EVSRK").Text = SEMlist.Cells(zeile, s_EventID).Value
                Session.findById("wnd[0]/tbar[1]/btn[6]").press
                Session.findById("wnd[0]/usr/ctxtHRVPVA-EVBEG").Text = SEMlist.Cells(zeile, s_Date).Value 'Beginndatum
            End If
           
            Session.findById("wnd[0]/usr/ctxtHRVPVA-EVLOC").Text = SEMlist.Cells(zeile, s_Location).Value 'Orts-ID
            Session.findById("wnd[0]/usr/cmbHRVPVA-LANGU").Key = SEMlist.Cells(zeile, s_Language).Value 'Sprache des Seminars
       
            Session.findById("wnd[0]/usr/cmbHRVPVA-OOTYP").Key = SEMlist.Cells(zeile, s_Organisation).Value 'Organisatortyp
            Session.findById("wnd[0]/usr/ctxtHRVPVA-OGSRK").Text = SEMlist.Cells(zeile, s_Organisation + 1).Value 'SAP-ID des Anbieters
          
            '<Ressourcenauswahl>
            Session.findById("wnd[0]/tbar[1]/btn[5]").press
            '<OK> (speichern)
            Session.findById("wnd[1]/tbar[0]/btn[11]").press
            '<OK> ... anlegen ohne Ressourcen
            Session.findById("wnd[2]/usr/btnSPOP-OPTION1").press
           
            '<speichern>
            Session.findById("wnd[0]/tbar[0]/btn[11]").press
                      
            If SEMlist.Cells(zeile, s_Length + 1).Value <> "" And _
                (SEMlist.Cells(zeile, s_Length).Value < SEMlist.Cells(zeile, s_Length + 1).Value) Then
                'Beim hinzufügen von Tagen können "Fehler" passieren:
                '... geht ins Wochenende
                '... Profile stimmen nicht exakt mit der Planung zusammen
                SEMlist.Cells(zeile, s_Mctrl).Value = "Check length profils"
                statusInfo = "Check length profil"
            Else
                SEMlist.Cells(zeile, s_Mctrl).Value = "ok"
            End If
            'SAP-Refernz speichern, für spätere Suchoptionen
            SEMlist.Cells(zeile, s_EventID).Value = Session.findById("wnd[0]/usr/txtHRVPVA-EVSRK").Text
        End If
       
        zeile = zeile + 1
    Loop
   
    If statusInfo = "" Then
        statusInfo = "Command list finshed"
    Else
        statusInfo = "Attention:" & statusInfo & "Command list finshed"
    End If
   
    Session.findById("wnd[0]/tbar[0]/btn[15]").press
   
    MsgBox (statusInfo)

End Sub

holger_khn
Contributor
0 Kudos

Hello Franz,

as I´m an german this is not an issue to understand.

When your code is done you need to clear all objects.

'.......................

    MsgBox (statusInfo)
   
    Set Session = Nothing
    Set Connection = Nothing
    Set Applications = Nothing
    Set SapGui = Nothing

End Sub

'------END----

Then your active SAP session will be released.

Br, Holger

Former Member
0 Kudos

Hello Holger,

thank you very much.

I am very surprised about the different behaviour of "recordings" and "self made" code.

Recordings do not clear all objects, but the sessions seems to be ended somehow.

Nevertheless - thanks for the solution!

Best regards, Franz

holger_khn
Contributor
0 Kudos

Hi Franz.

You have defined this objects as global variables. In this case you need to clear them by end of your code.

When you set them as local variables in your procedure (macro) the will release automatically when the code is done and 'End Sub' is reached.

Br, Holger

Former Member
0 Kudos

Hello Holger,

it is good to see, that the software (code) is working.

But it is important and good to understand, why the software is working correct.

Thanks for your informations, Franz

Answers (0)