当前位置：theitroad>vba 如何从 Excel 宏运行 SAP GUI 脚本

vba 如何从 Excel 宏运行 SAP GUI 脚本

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/45187903/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 12:55:30 　来源:igfitidea点击:

How to run SAP GUI script from Excel Macro

excelvbaautomationsap-guisapscript

提问by Ashique Sheikh

I am trying to create a Excel macro which executes the SAP GUI Script. I already created the SAP script but I didn't understand how to use that in VBA macro.

我正在尝试创建一个执行 SAP GUI 脚本的 Excel 宏。我已经创建了 SAP 脚本，但我不明白如何在 VBA 宏中使用它。

This is my SAP GUI Script :

这是我的 SAP GUI 脚本：

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.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 application, "on"
End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "ZL"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/chkP_DBAGG").selected = true
session.findById("wnd[0]/usr/ctxtP_DTA").text = "DB"
session.findById("wnd[0]/usr/chkP_DBAGG").setFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[25]").press
session.findById("wnd[0]/tbar[1]/btn[26]").press
session.findById("wnd[0]/usr/chkS005").selected = true
session.findById("wnd[0]/usr/chkS017").selected = true
session.findById("wnd[0]/usr/chkS018").selected = true
session.findById("wnd[0]/usr/chkS020").selected = true
session.findById("wnd[0]/usr/chkS025").selected = true
session.findById("wnd[0]/usr/chkS030").selected = true
session.findById("wnd[0]/usr/chkS031").selected = true
session.findById("wnd[0]/usr/chkS055").selected = true
session.findById("wnd[0]/usr/chkS057").selected = true
session.findById("wnd[0]/usr/chkS057").setFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/ctxtC025-LOW").setFocus
session.findById("wnd[0]/usr/ctxtC025-LOW").caretPosition = 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170717,20170717"
session.findById("wnd[0]/usr/ctxtC025-HIGH").setFocus
session.findById("wnd[0]/usr/ctxtC025-HIGH").caretPosition = 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = "20170724"
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170724,20170724"
session.findById("wnd[0]/usr/txtL_MX").text = "9999999"
session.findById("wnd[0]/usr/txtL_MX").setFocus
session.findById("wnd[0]/usr/txtL_MX").caretPosition = 11
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").select
session.findById("wnd[1]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[2]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[2]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[2]").sendVKey 4
session.findById("wnd[3]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[3]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[3]").sendVKey 4
session.findById("wnd[4]/usr/ctxtDY_PATH").text = "G:\PROFILES\AP\Desktop"
session.findById("wnd[4]/usr/ctxtDY_FILENAME").text = "report.xlsx"
session.findById("wnd[4]/usr/ctxtDY_FILENAME").caretPosition = 11
session.findById("wnd[4]/tbar[0]/btn[11]").press
session.findById("wnd[3]/tbar[0]/btn[11]").press
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[11]").press

I have few doubts regarding this:

我对此几乎没有怀疑：

  1. How can I add this in Excel macro.
  2. Do I need to open the SAP manually before running it?
  3. Is there any ad-on code do I need to use to open the SAP from Excel macro?
  1. 如何在 Excel 宏中添加它。
  2. 在运行之前是否需要手动打开 SAP？
  3. 从 Excel 宏打开 SAP 时是否需要使用任何附加代码？

I only need button click to perform the process. I didn't pass any value dynamically.

我只需要单击按钮即可执行该过程。我没有动态传递任何值。

回答by Steve W

Open and log in to SAP manually.

手动打开并登录到 SAP。

Open Excel, make sure the developer tab is visible. If not then select;

打开 Excel，确保开发人员选项卡可见。如果不是，则选择；

File/Options/Customize Ribbon. Make sure 'Developer' is ticked.

文件/选项/自定义功能区。确保勾选“开发人员”。

Select the Developer tab in Excel then choose 'Insert'. From the 'Active X' set of controls (Not the 'FORM' controls') select the Command Button control and draw it on your worksheet. Right-click the button and select 'Properties', look for 'Caption' and either delete it or change it to something more appropriate. After that, right-click the command button again and select 'View Code'. Paste the following code into the vba editor.

在 Excel 中选择“开发工具”选项卡，然后选择“插入”。从“Active X”控件集（不是“FORM”控件）中选择命令按钮控件并将其绘制在您的工作表上。右键单击该按钮并选择“属性”，查找“标题”并删除它或将其更改为更合适的内容。之后，再次右键单击命令按钮并选择“查看代码”。将以下代码粘贴到 vba 编辑器中。

On the VBA editor menu select; Debug/Compile VBA Project. Hopefully there will be no errors and you can now close the VBA editor.

在 VBA 编辑器菜单上选择; 调试/编译 VBA 项目。希望没有错误，您现在可以关闭 VBA 编辑器。

Now, click your button and the code should run your SAP transaction. If the button won't click, make sure 'Design Mode' isn't selected in the Developer tab.

现在，单击您的按钮，代码应该会运行您的 SAP 事务。如果该按钮无法点击，请确保未在“开发人员”选项卡中选择“设计模式”。

Don't forget to save your spreadsheet :)

不要忘记保存您的电子表格:)

Private Sub CommandButton1_Click()

On Error GoTo Err_NoSAP

If Not IsObject(SAPGuiApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAPGuiApp.Children(0)
End If
If Not IsObject(SAP_session) Then
   Set SAP_session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAP_session, "on"
   WScript.ConnectObject SAPGuiApp, "on"
End If

If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP

Set aw = SAP_session.ActiveWindow()
aw.findById("wnd[0]").Maximize

On Error GoTo Err_Description
SAP_session.findById("wnd[0]").Maximize
SAP_session.findById("wnd[0]/tbar[0]/okcd").Text = "ZL"
SAP_session.findById("wnd[0]").sendVKey 0
SAP_session.findById("wnd[0]/usr/chkP_DBAGG").Selected = True
SAP_session.findById("wnd[0]/usr/ctxtP_DTA").Text = "DB"
SAP_session.findById("wnd[0]/usr/chkP_DBAGG").SetFocus
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/tbar[1]/btn[25]").press
SAP_session.findById("wnd[0]/tbar[1]/btn[26]").press
SAP_session.findById("wnd[0]/usr/chkS005").Selected = True
SAP_session.findById("wnd[0]/usr/chkS017").Selected = True
SAP_session.findById("wnd[0]/usr/chkS018").Selected = True
SAP_session.findById("wnd[0]/usr/chkS020").Selected = True
SAP_session.findById("wnd[0]/usr/chkS025").Selected = True
SAP_session.findById("wnd[0]/usr/chkS030").Selected = True
SAP_session.findById("wnd[0]/usr/chkS031").Selected = True
SAP_session.findById("wnd[0]/usr/chkS055").Selected = True
SAP_session.findById("wnd[0]/usr/chkS057").Selected = True
SAP_session.findById("wnd[0]/usr/chkS057").SetFocus
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/usr/ctxtC025-LOW").SetFocus
SAP_session.findById("wnd[0]/usr/ctxtC025-LOW").caretPosition = 0
SAP_session.findById("wnd[0]").sendVKey 4
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170717,20170717"
SAP_session.findById("wnd[0]/usr/ctxtC025-HIGH").SetFocus
SAP_session.findById("wnd[0]/usr/ctxtC025-HIGH").caretPosition = 0
SAP_session.findById("wnd[0]").sendVKey 4
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = "20170724"
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170724,20170724"
SAP_session.findById("wnd[0]/usr/txtL_MX").Text = "9999999"
SAP_session.findById("wnd[0]/usr/txtL_MX").SetFocus
SAP_session.findById("wnd[0]/usr/txtL_MX").caretPosition = 11
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[1]").sendVKey 4
SAP_session.findById("wnd[2]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[2]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[2]").sendVKey 4
SAP_session.findById("wnd[3]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[3]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[3]").sendVKey 4
SAP_session.findById("wnd[4]/usr/ctxtDY_PATH").Text = "G:\PROFILES\AP\Desktop"
SAP_session.findById("wnd[4]/usr/ctxtDY_FILENAME").Text = "report.xlsx"
SAP_session.findById("wnd[4]/usr/ctxtDY_FILENAME").caretPosition = 11
SAP_session.findById("wnd[4]/tbar[0]/btn[11]").press
SAP_session.findById("wnd[3]/tbar[0]/btn[11]").press
SAP_session.findById("wnd[2]/tbar[0]/btn[0]").press
SAP_session.findById("wnd[1]/tbar[0]/btn[11]").press

Exit Sub

Err_Description:
    MsgBox ("The program has generated an error;" & Chr(13) & _
    "the reason for this error is unknown."), VbInformation, _
    "For Information..."
        Exit Sub

Err_NoSAP:
MsgBox ("You don't have SAP open or " & Chr(13) & _
"scripting has been disabled."), VbInformation, _
"For Information..."
        Exit Sub

Err_TooManySAP:
MsgBox ("You must only have one SAP session open. " & Chr(13) & _
        "Please close all other open SAP sessions."), VbInformation, _
        "For Information..."
         Exit Sub

End Sub

回答by swridings

I always had problems using the connection script that the recorder spit out (If IsObject(WScript) Then).

我总是在使用记录器吐出的连接脚本时遇到问题（If IsObject(WScript) Then）。

This is what I've always used. In a code module insert the below.

这是我一直使用的。在代码模块中插入以下内容。

Sub Connect_To_SAP()

    Dim SapGuiAuto As Object
    Dim SAPApp As Object
    Dim SAPCon As Object
    Dim session As Object

    Set SapGuiAuto = GetObject("SAPGUI")
    Set SAPApp = SapGuiAuto.GetScriptingEngine
    Set SAPCon = SAPApp.Children(0)
    Set session = SAPCon.Children.ElementAt(0) ' <--- Assumes you are using the first session open. '

'Your script here

End Sub

You can always throw your recorded script in here, but when you want to take things to the next level you should try adding the SAP GUI Scripting API. Deep in the SAP folder of your program files there's a class library you can use to make your SAP automation flawless. Try searching for this file "sapfewse.ocx". Get the file path.

您始终可以将录制的脚本放在此处，但是当您想将事情提升到一个新的水平时，您应该尝试添加SAP GUI Scripting API。在程序文件的 SAP 文件夹深处，有一个类库，可用于使 SAP 自动化完美无缺。尝试搜索此文件“ sapfewse.ocx”。获取文件路径。

Then, in the IDE select Tools > References then click Browse and select that file. Once you've added it to the VBA Project press F2 to view all the methods and properties at your disposal.

然后，在 IDE 中选择工具 > 引用，然后单击浏览并选择该文件。将它添加到 VBA 项目后，按 F2 以查看您可以使用的所有方法和属性。

回答by Brownish Monster

Unfortunately, I no longer have access to any SAP applications due to changing jobs so the below is from what I remember.

不幸的是，由于工作变动，我无法再访问任何 SAP 应用程序，因此以下内容来自我的记忆。

  • You can add this into an Excel macro by copy and pasting into a Sub.

  • You will need to open SAP manually as well keeping the computer unlocked, otherwise your macro will crash. It might be possible to open SAP via the macro, I did it in my last job and logged into it with a hard-coded password but I don't have the code to share.

  • In the script pasted, replace every instance of applicationwith something else, such as guiApplication, otherwise Excel will get confused with its own Excel.Applicationmember.

  • No other code is required but you will need to call the Subroutine and ensure you do not have option explicitset. You can but then you will need to instantiate session, connection, etc.

  • 您可以通过复制并粘贴到Sub.

  • 您需要手动打开 SAP 并保持计算机处于解锁状态，否则您的宏将崩溃。可能可以通过宏打开 SAP，我在上一份工作中这样做并使用硬编码密码登录它，但我没有可共享的代码。

  • 在粘贴的脚本中，将 的每个实例替换为application其他内容，例如guiApplication，否则 Excel 会与其自己的Excel.Application成员混淆。

  • 不需要其他代码，但您需要调用Sub例程并确保您没有option explicit设置。你可以，但那么你将需要实例化session，connection等等。

Where to put the script:

放置脚本的位置：

 Public Sub MySapScript()
     ' Your Sap Script here
 End Sub

相关推荐

VBA - 如何将单元格的值从文本转换为数字

VBA - 如何将单元格的值从文本转换为数字

使用 Vba 从 Excel 工作簿中删除查询

使用 Vba 从 Excel 工作簿中删除查询

如何使用 VBA 更改单元格的格式化字体颜色？

如何使用 VBA 更改单元格的格式化字体颜色？

VBA/Excel 在没有代码更改的情况下突然运行缓慢

VBA/Excel 在没有代码更改的情况下突然运行缓慢

相关推荐
最近更新
标签