on 05-07-2013 9:08 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.