通过EXCEL宏和SAPScript进行批量业务处理.doc

通过EXCEL宏和SAP Script进行批量业务处理
SAP提供了很多批量导入的工具，但问题是一般情况下不会赋予普通用户这些权限，因此我结合EXCEL VBA和SAP Script功能整理出一个简易的批量业务处理方式，供大家参考
l 使用SAP Script Recording功能录制SAP操作，生成代码块
1. 激活SAP Script Recording

2. 选择存放路径和script代码块文件名称

3. 点击开始录制SAP操作，建议以”/N”+事务代码，以保存为结尾。生成如下程序代码文件，可以用文本编辑器打开

l 通过EXCEL VBA编写宏代码，用于读取数据模板和导入SAP
1. 进入VBA编辑视图

2. 复制Script代码块中的代码，并做以下适当修改：
a) 在两个"Application" 和 "Connection" 之前加入"SAP"
b) 修改录制中的固定值，改为模板中的相应字段，如 Range("A" & I).Value等
c) 如有更为复杂的数据处理需求，可以在宏代码中加入适当的判断或者循环语句

l 以下代码用于修改物料主数据的，前段和末端代码可以沿用，只需用script代码覆盖病修改中段原代码即可。供参考。

Sub EXCEL_to_SAP()

yes_No = MsgBox("Do you want to upload data into SAP really?", vbOKCancel)
If yes_No = 2 Then
End
End If

Range("Z1").Value = "=COUNTA(A:A)": TEMP = Range("Z1").Value: Range("Z1").Value = ""

On Error GoTo NotLoggedOnSAP:
Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting object
Set SapApplication = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
Set SapConnection = SapApplication.Children(0) 'Get the first system that is currently connected
Set session = SapConnection.Children(0) 'Get the first session (window) on that connection
GoTo 10:
NotLoggedOnSAP:
x = MsgBox("You are not logged on SAP. Please log on and try again.", vbOKOnly, "Not Logged on SAP")
Exit Sub
On Error GoTo 0

10 If TEMP > 1 Then
For I = 2 To TEMP

'********************** 1. copy from scrip
'********************** 2. add "SAP" before 2 "Application" and 2 "Connection" in next 10 lines
'********************** 3. change fixed value by fields' value, refer to -> Range("A" & I).Value <-

If Not IsObject(Application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SapApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set SapConnection = SapApplication.Children(0)
End If
If Not IsObject(session) Then
Set session = SapConnection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = Range("A" & I).Value
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 12
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-EXTWG").Text = Range("B" & I).Value
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-EXTWG").SetFocus
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-EXTWG").caretPosition = 1
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]").sendVKey 0

' ********************** copy from scrip <-

Next
End If

End Sub