(完整版)通过EXCEL宏和SAPScript进行批量业务处理.docx

1、通过 EXCEL宏和 SAP Script 进行批量业务处理SAP提供了很多批量导入的工具，但问题是一般情况下不会赋予普通用户这些权限，因此我结合 EXCEL VBA和 SAP Script功能整理出一个简易的批量业务处理方式，供大家参考使用 SAP Script Recording功能录制 SAP操作，生成代码块1.激活 SAP Script Recording2. 选择存放路径和 script 代码块文件名称3.点击 开始录制 SAP操作，建议以 ”/N ”+事务代码，以保存为结尾。生成如下程序代码文件，可以用文本编辑器打开通过 EXCEL VBA编写宏代码，用于读取数据模板和导入SAP1

2、. 进入 VBA 编辑视图2. 复制 Script 代码块中的代码，并做以下适当修改：a) 在两个 "Application" 和 "Connection" 之前加入 "SAP"b)修改录制中的固定值，改为模板中的相应字段，如Range("A" & I).Value 等c) 如有更为复杂的数据处理需求，可以在宏代码中加入适当的判断或者循环语句以下代码用于修改物料主数据的，前段和末端代码可以沿用，只需用script代码覆盖病修改中段原代码即可。供参考。Sub EXCEL_to_SAP()yes_No = Ms

3、gBox("Do you want to upload data into SAP really?", vbOKCancel)If yes_No = 2 ThenEndEnd IfRange("Z1").Value = "=COUNTA(A:A)": TEMP = Range("Z1").Value: Range("Z1").Value = ""On Error GoTo NotLoggedOnSAP:Set SapGuiAuto = GetObject("SAPG

4、UI")'Get the SAP GUI Scripting objectSet SapApplication = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI Set SapConnection = SapApplication.Children(0) 'Get the first system that is currently connectedSet session = SapConnection.Children(0)connectionGoTo 10:'Ge

5、t the first session (window) on thatNotLoggedOnSAP:x = MsgBox("You are not logged on SAP. Please log on and try again.", vbOKOnly, "Not Logged on SAP")Exit SubOn Error GoTo 010 If TEMP > 1 ThenFor I = 2 To TEMP'* 1. copy from scrip'* 2. add "SAP" before 2 &qu

6、ot;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) ThenSet SapGuiAuto = GetObject("SAPGUI")Set SapApplication = SapGuiAuto.GetScriptingEngin

7、eEnd IfIf Not IsObject(Connection) ThenSet SapConnection = SapApplication.Children(0)End IfIf Not IsObject(session) ThenSet session = SapConnection.Children(0)End IfIf IsObject(WScript) ThenWScript.ConnectObject session, "on"WScript.ConnectObject Application, "on"End Ifsession.fi

8、ndById("wnd0").maximizesession.findById("wnd0/tbar0/okcd").Text = "/nmm02"session.findById("wnd0").sendVKey 0session.findById("wnd0/usr/ctxtRMMG1-MATNR").Text = Range("A" & I).Valuesession.findById("wnd0/usr/ctxtRMMG1-MATNR").

9、caretPosition = 12session.findById("wnd0").sendVKey 0session.findById("wnd1").sendVKey 0session.findById("wnd0/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB 2:SAPLMGD1:2001/ctxtMARA-EXTWG").Text = Range("B" & I).Valuesession.findById("wnd0/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB 2:SAPLMGD1:2001/ctxtMARA-EXTWG").SetFocussession.findById("wnd0/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB 2:SAPLMGD1:2001/ctxtMARA-EXTWG").caretP