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数据透视表和图表。
Thanks!
谢谢！
1 个解决方案
#1
28
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系统的访问权限。导出数据的ABAP程序和/或宏可以调用的RFC直接获取数据或让SAP创建文件可能是最好的。
However as 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 More button 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]之类的元素的名称，您可以使用脚本录制。单击自定义本地布局按钮，它可能看起来像这样：然后找到脚本录制和播放菜单项。在其中，“更多”按钮允许您查看/更改记录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.ocx in the SAP GUI installation folder.
但是，如果要使用早期绑定，以便VBA编辑器可能显示正在使用的对象的属性和方法，则需要在SAP GUI安装文件夹中添加对sapfewse.ocx的引用。
#1
28
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系统的访问权限。导出数据的ABAP程序和/或宏可以调用的RFC直接获取数据或让SAP创建文件可能是最好的。
However as 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 More button 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]之类的元素的名称，您可以使用脚本录制。单击自定义本地布局按钮，它可能看起来像这样：然后找到脚本录制和播放菜单项。在其中，“更多”按钮允许您查看/更改记录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.ocx in the SAP GUI installation folder.
但是，如果要使用早期绑定，以便VBA编辑器可能显示正在使用的对象的属性和方法，则需要在SAP GUI安装文件夹中添加对sapfewse.ocx的引用。