从 SAP 中提取数据的 VBA 通用方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19452461/
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
VBA general way for pulling data out of SAP
提问by Kes Perron
Does anyone know how to use VBA to pull data from SAP Netweaver?
有谁知道如何使用 VBA 从 SAP Netweaver 中提取数据？
I have a number of daily reports that require exporting data from SAP to Excel and formatting it into a report. I have already written working VBA macros that do the formatting. I have to manually extract the data and run each report macro individually. So much time could be saved if my macro could just go into SAP, grab the data for report #1, format it, grab the data for report #2, etc.
我有许多每日报告，需要将数据从 SAP 导出到 Excel 并将其格式化为报告。我已经编写了用于格式化的工作 VBA 宏。我必须手动提取数据并单独运行每个报告宏。如果我的宏可以进入 SAP，获取报告 #1 的数据，格式化它，获取报告 #2 的数据等，那么可以节省很多时间。
I work with SAP NetWeaver (Release 730, Version 7300.1.3.1079). The reports are just Excel pivot tables and charts.
我使用 SAP NetWeaver（版本 730，版本 7300.1.3.1079）。报告只是 Excel 数据透视表和图表。
回答by AndASM
This all depends on what sort of access you have to your SAP system. An ABAP program that exports the data and/or an RFC that your macro can call to directly get the data or have SAP create the file is probably best.
这一切都取决于您对 SAP 系统的访问权限。导出数据和/或宏可以调用以直接获取数据或让 SAP 创建文件的 RFC 的 ABAP 程序可能是最好的。
Howeveras a general rule people looking for this sort of answer are looking for an immediate solution that does not require their IT department to spend months customizing their SAP system.
然而，作为一般规则，寻求此类答案的人们正在寻找一种无需 IT 部门花费数月时间自定义 SAP 系统的即时解决方案。
In that case you probably want to use SAP GUI Scripting. SAP GUI scripting allows you to automate the Windows SAP GUI in much the same way as you automate Excel. In fact you can call the SAP GUI directly from an Excel macro. Read up more on it here. The SAP GUI has a macro recording tool much like Excel does. It records macros in VBScript which is nearly identical to Excel VBA and can usually be copied and pasted into an Excel macro directly.
在这种情况下，您可能想要使用 SAP GUI Scripting。SAP GUI 脚本允许您以与自动化 Excel 大致相同的方式自动化 Windows SAP GUI。事实上，您可以直接从 Excel 宏调用 SAP GUI。在此处阅读更多相关信息。SAP GUI 具有与 Excel 非常相似的宏记录工具。它在 VBScript 中记录宏，这与 Excel VBA 几乎相同，通常可以直接复制并粘贴到 Excel 宏中。
Example Code
示例代码
Here is a simple example based on a SAP system I have access to.
这是一个基于我有权访问的 SAP 系统的简单示例。
Public Sub SimpleSAPExport()
Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting object
Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
Set session = SAPCon.Children(0) 'Get the first session (window) on that connection
'Start the transaction to view a table
session.StartTransaction "SE16"
'Select table T001
session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "T001"
session.findById("wnd[0]/tbar[1]/btn[7]").Press
'Set our selection criteria
session.findById("wnd[0]/usr/txtMAX_SEL").text = "2"
session.findById("wnd[0]/tbar[1]/btn[8]").press
'Click the export to file button
session.findById("wnd[0]/tbar[1]/btn[45]").press
'Choose the export format
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select
session.findById("wnd[1]/tbar[0]/btn[0]").press
'Choose the export filename
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "test.txt"
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Temp\"
'Export the file
session.findById("wnd[1]/tbar[0]/btn[0]").press
End Sub
Script Recording
脚本录制
To help find the names of elements such aswnd[1]/tbar[0]/btn[0]you can use script recording.
Click the customize local layout button, it probably looks a bit like this:
Then find the Script Recording and Playback menu item.
Within that the Morebutton allows you to see/change the file that the VB Script is recorded to. The output format is a bit messy, it records things like selecting text, clicking inside a text field, etc.
为了帮助查找元素的名称，例如wnd[1]/tbar[0]/btn[0]您可以使用脚本录制。单击自定义本地布局按钮，它可能看起来有点像：
然后找到脚本录制和播放菜单项。
在该More按钮中，您可以查看/更改记录 VB 脚本的文件。输出格式有点乱，它记录了选择文本、在文本字段内单击等内容。
Edit: Early and Late binding
编辑：早期和晚期绑定
The provided script should work if copied directly into a VBA macro. It uses late binding, the line Set SapGuiAuto = GetObject("SAPGUI")defines the SapGuiAuto object.
如果直接复制到 VBA 宏中，则提供的脚本应该可以工作。它使用后期绑定，该行Set SapGuiAuto = GetObject("SAPGUI")定义了 SapGuiAuto 对象。
If however you want to use early binding so that your VBA editor might show the properties and methods of the objects you are using, you need to add a reference to sapfewse.ocxin the SAP GUI installation folder.
但是，如果您想使用早期绑定以便您的 VBA 编辑器可以显示您正在使用的对象的属性和方法，您需要sapfewse.ocx在 SAP GUI 安装文件夹中添加一个引用。
