时间戳

首页 > 解决方案 > 通过 SAP GUI 脚本发布订单块

excel - 通过 SAP GUI 脚本发布订单块

问题描述

我可以通过使用 SAP GUI 脚本和 VBA 宏来解锁网点。我需要将出口代码（a 列）和状态 0（B 列）保留在 Excel 工作表上，然后当我单击启动脚本时，sap 将执行解锁活动。

Excel模板如下所示， 在此处输入图像描述

此 VBA 代码有效。

Option Explicit
Public SapGuiAuto, WScript, msgcol
Public objGui  As GuiApplication
Public objConn As GuiConnection
Public objSess As GuiSession
Public objSBar As GuiStatusbar
Public objSheet As Worksheet
Dim W_System
Dim iCtr As Integer
Const tcode = "XD05"


Function Attach_Session(iRow, Optional mysystem As String) As Boolean
Dim il, it
Dim W_conn, W_Sess

' Unless a system is provided (XXXYYY where XXX is SID and YYY client)
' get the system from the sheet (in this case it is in cell A8)
If mysystem = "" Then
    W_System = ActiveSheet.Cells(iRow, 1)
Else
    W_System = mysystem
End If

' If we are already connected to a session, exit do not try again
If W_System = "" Then
   Attach_Session = False
   Exit Function
End If

' If the session object is not nil, use that session (assume connected to the correct session)
If Not objSess Is Nothing Then
    If objSess.Info.SystemName & objSess.Info.Client = W_System Then
        Attach_Session = True
        Exit Function
    End If
End If

' If not connected to anything, set up the objects
If objGui Is Nothing Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set objGui = SapGuiAuto.GetScriptingEngine
End If

' Cycle through the open SAP GUI sessions and check which is in the same system running the matching transaction
For il = 0 To objGui.Children.Count - 1
    Set W_conn = objGui.Children(il + 0)
    For it = 0 To W_conn.Children.Count - 1
        Set W_Sess = W_conn.Children(it + 0)
   If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
            Set objConn = objGui.Children(il + 0)
            Set objSess = objConn.Children(it + 0)
            Exit For
        End If
    Next
Next

' If nothing is found, display and error message
If objSess Is Nothing Then
   MsgBox "No active session to system " + W_System + " with transaction " + tcode + ", or scripting is not enabled.", vbCritical + vbOKOnly
   Attach_Session = False
   Exit Function
End If

' Turn on scripting
If IsObject(WScript) Then
   WScript.ConnectObject objSess, "on"
   WScript.ConnectObject objGui, "on"
End If

' Maximize the window of the connected session
Set objSBar = objSess.findById("wnd[0]/sbar")
objSess.findById("wnd[0]").Iconify

Attach_Session = True


End Function

Public Sub StartProcessing()

Dim W_Obj1, W_Obj2, W_Obj3, W_Obj4, iRow
Dim W_Func
Dim W_Src_Ord
Dim W_Ret As Boolean
Dim itemcount As Integer
Dim itemmax As Integer
Const startrow As Integer = 5 'First row with actual data

Set objSheet = ActiveWorkbook.ActiveSheet
' Connect to a system stored in cell A8
W_Ret = Attach_Session(2)
If Not W_Ret Then
    MsgBox "Not connected to client"
    GoTo MyEnd
End If

itemcount = 0
itemmax = 0

' Determine the number of items to be processed: where the status is zero
For iRow = startrow To objSheet.UsedRange.Rows.Count
    If objSheet.Cells(iRow, 2) = "0" Then
        itemmax = itemmax + 1
    End If
Next
' Update the counter in cell A9
objSheet.Cells(3, 1) = itemcount & "/" & itemmax

' Cycle through the rows with status 0 and call the ProcessRow function to process them
For iRow = startrow To objSheet.UsedRange.Rows.Count
    If objSheet.Cells(iRow, 2) = "0" Then
        Call ProcessRow(iRow)
        itemcount = itemcount + 1
        objSheet.Cells(3, 1) = itemcount & "/" & itemmax
    End If
Next
           

MyEnd:
' destory the objects, free up memory
    Set objSess = Nothing
    Set objGui = Nothing
    Set SapGuiAuto = Nothing
    
    MsgBox "Script completed.", vbInformation + vbOKOnly
       
End Sub

Function ProcessRow(iRow)
Dim W_BPNumber, W_Blockcode, W_Companycode, W_Salesorg
Dim lineitems As Long

' Set the line status to "processing..."
objSheet.Cells(iRow, 2) = 1

'BP Number
If objSheet.Cells(iRow, 1) <> "" Then
    W_BPNumber = objSheet.Cells(iRow, 1)
Else
    W_BPNumber = "xxxxxx"
End If

'Company Code
If objSheet.Cells(iRow, 2) <> "03" Then
    W_Companycode = objSheet.Cells(iRow, 2)
Else
    W_Companycode = "xxxxxx"
End If
'Sales Org
If objSheet.Cells(iRow, 4) <> "" Then
    W_Salesorg = objSheet.Cells(iRow, 4)
Else
    W_Salesorg = "xxxxxx"
End If

'Block Code
If objSheet.Cells(iRow, 5) <> "" Then
    W_Blockcode = objSheet.Cells(iRow, 5)
Else
    W_Blockcode = ""
End If



' Set error handling to the below code where we can capture if a line had failed in the GUI script
On Error GoTo myerr

' SAP GUI Script starts here

objSess.findById("wnd[0]").Iconify
'objSess.findById("wnd[0]").Maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = "/nXD05"
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/usr/ctxtRF02D-KUNNR").Text = W_BPNumber
objSess.findById("wnd[0]/usr/ctxtRF02D-BUKRS").Text = "1172"
objSess.findById("wnd[0]/usr/ctxtRF02D-VKORG").Text = "1172"
objSess.findById("wnd[0]/usr/ctxtRF02D-VTWEG").Text = "10"
objSess.findById("wnd[0]/usr/ctxtRF02D-SPART").Text = "00"
objSess.findById("wnd[0]/usr/ctxtRF02D-SPART").SetFocus
objSess.findById("wnd[0]/usr/ctxtRF02D-SPART").caretPosition = 2
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/usr/ctxtKNA1-AUFSD").Text = ""
objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").Text = ""
objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").SetFocus
objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").caretPosition = 0
objSess.findById("wnd[0]/tbar[0]/btn[11]").press
objSess.findById("wnd[0]").sendVKey 12


' Gets the message from the status bar and save it in column B
objSheet.Cells(iRow, 5) = objSBar.Text

' Update the Status to "Completed" and exit
objSheet.Cells(iRow, 2) = 2
Exit Function

myerr:
' Update the status to "Error"
objSheet.Cells(iRow, 2) = 3

End Function

我的要求

如果 sap 中以下字段的值为 03，则出口应畅通；否则，该特定出口的 excel 文件中应显示错误消息。

objSess.findById("wnd[0]/usr/ctxtKNA1-AUFSD").Text = ""
objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").Text = ""

在此处输入图像描述

如何使用 VBA 实现这一目标？

标签： excelvbascriptingsap-guilongtext

解决方案

不确定我是否完全理解您在这里的要求，但可能是这样的：

Const cBlockText As String = "03"

objSess.findById("wnd[0]").Iconify
objSess.findById("wnd[0]/tbar[0]/okcd").Text = "/nXD05"
objSess.findById("wnd[0]").sendVKey 0

objSess.findById("wnd[0]/usr/ctxtRF02D-KUNNR").Text = W_BPNumber
objSess.findById("wnd[0]/usr/ctxtRF02D-BUKRS").Text = "1172"
objSess.findById("wnd[0]/usr/ctxtRF02D-VKORG").Text = "1172"
objSess.findById("wnd[0]/usr/ctxtRF02D-VTWEG").Text = "10"
objSess.findById("wnd[0]/usr/ctxtRF02D-SPART").Text = "00"
objSess.findById("wnd[0]").sendVKey 0

' New code here:
' Not sure of and/or rules?
If objSess.findById("wnd[0]/usr/ctxtKNA1-AUFSD").Text = cBlockText _
   Or objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").Text = cBlockText Then

    objSheet.Cells(iRow, 5) = "Unable to block: Dispute with customer"
    objSess.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
    objSess.findById("wnd[0]").sendVKey 0
Else
    objSess.findById("wnd[0]/usr/ctxtKNA1-AUFSD").Text = ""
    objSess.findById("wnd[0]/usr/ctxtKNVV-AUFSD").Text = ""
    objSess.findById("wnd[0]/tbar[0]/btn[11]").press
    objSess.findById("wnd[0]").sendVKey 12

    objSheet.Cells(iRow, 5) = objSBar.Text

    ' Update the Status to "Completed" and exit
    objSheet.Cells(iRow, 2) = 2
End If

推荐阅读