VBA从SAP中提取虚拟数据 [英] VBA pulling data from SAP for dummies

问题描述

有没有人知道如何使用VBA从SAP Netweaver中提取数据？

我有一些日常报告要求从SAP导出数据到Excel并格式化成为一份报告。我已经写了工作的VBA宏做格式化。我必须手动提取数据并单独运行每个报告宏。如果我的宏可以进入SAP，获取报告＃1的数据，格式化，获取报告＃2的数据等，那么可以节省很多时间。

我使用SAP NetWeaver（版本730，版本7300.1.3.1079）。报告只是Excel透视表和图表。

谢谢！

解决方案

p>这一切都取决于您对SAP系统的访问权限。一个ABAP程序，导出数据和/或您的宏可以调用的RFC直接获取数据或者SAP创建文件可能是最好的。

然而，作为一般原则，人们寻找这种答案正在寻找一个立即的解决方案，不需要他们的IT部门花费数月自订SAP系统。

在这种情况下，您可能希望使用SAP GUI脚本。 SAP GUI脚本允许您以与自动化Excel相同的方式自动执行Windows SAP GUI。实际上，您可以直接从Excel宏调用SAP GUI。了解更多信息 here 。 SAP GUI具有非常像Excel的宏录制工具。它在VBScript中记录与Excel VBA几乎相同的宏，通常可以直接复制并粘贴到Excel宏中。

示例代码

这是一个基于我可以访问的SAP系统的简单示例。

  Public Sub SimpleSAPExport（ ）
设置SapGuiAuto = GetObject（SAPGUI）'获取SAP GUI脚本对象
设置SAPApp = SapGuiAuto.GetScriptingEngine'获取当前运行的SAP GUI 
设置SAPCon = SAPApp.Children（0 ）'获取当前连接的第一个系统
设置会话= SAPCon.Children（0）'获取该连接上的第一个会话（窗口）
 
'启动事务以查看表
 session.StartTransactionSE16
 
'选择表T001 
 session.findById（wnd [0] / usr / ctxtDATABROWSE-TABLENAME）。Text =T001
 session.findById（wnd [0] / tbar [1] / btn [7]）按
 
'设置我们的选择条件
 session.findById（wnd [ 0]的/ usr /吨xtMAX_SEL）。text =2
 session.findById（wnd [0] / tbar [1] / btn [8]）按
 
'单击导出到文件按钮
 session.findById（wnd [0] / tbar [1] / btn [45]）。按
 
'选择导出格式
 session.findById（ wnd [1] / usr / subSUBSCREEN_STEPLOOP：SAPLSPO5：0150 / sub：SAPLSPO5：0150 / radSPOPLI-SELFLAG [1,0]）选择
 session.findById（wnd [1] / tbar [0] btn [0]）。按
 
'选择导出文件名
 session.findById（wnd [1] / usr / ctxtDY_FILENAME）。text =test.txt
 session.findById（wnd [1] / usr / ctxtDY_PATH）。text =C：\Temp\
 
'导出文件
 session.findById（ wnd [1] / tbar [0] / btn [0]）。按
 End Sub 
  

脚本记录

为了帮助查找元素的名称，例如 wnd [1] / tbar [0] / btn [0] / code>可以使用脚本录制。
点击自定义的本地布局按钮，可能看起来有点像这样：


然后找到脚本录制和播放菜单项。



在更多按钮中，您可以查看/更改VB脚本记录的文件。输出格式有点混乱，它记录了选择文字，点击文本字段等内容。

编辑：早期和晚期绑定

如果直接复制到VBA宏中，则提供的脚本应该可以工作。它使用后期绑定，行 Set SapGuiAuto = GetObject（SAPGUI）定义SapGuiAuto对象。

如果但是，您希望使用早期绑定，以便您的VBA编辑器可能显示您正在使用的对象的属性和方法，您需要在code中添加引用sapfewse.ocx SAP GUI安装文件夹。

Does anyone know how to use VBA to pull data from 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.

I work with SAP NetWeaver (Release 730, Version 7300.1.3.1079). The reports are just Excel pivot tables and charts.

Thanks!

解决方案

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.

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.

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.

Example Code

Here is a simple example based on a SAP system I have access to.

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.

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.

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中提取虚拟数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！