VBA从SAP提取数据的一般方法 [英] VBA general way for pulling data out of SAP

问题描述

有人知道如何使用VBA从SAP Netweaver提取数据吗?

Does anyone know how to use VBA to pull data from SAP Netweaver?

我有许多每日报告，需要将数据从SAP导出到Excel并将其格式化为报告.我已经编写了可以进行格式化的VBA宏.我必须手动提取数据并分别运行每个报告宏.如果我的宏可以直接进入SAP，获取报告1的数据，对其进行格式化，获取报告2的数据，等等，那么可以节省很多时间.

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 NetWeaver(版本7300.1.3.1079，版本730).这些报告只是Excel数据透视表和图表.

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

推荐答案

这全部取决于您对SAP系统的访问权限.导出数据的ABAP程序和/或宏可以调用以直接获取数据或由SAP创建文件的RFC可能是最好的.

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.

但是作为一般规则，寻找这种答案的人们正在寻找一种即时解决方案，该解决方案不需要IT部门花费数月的时间来定制他们的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.

在这种情况下，您可能要使用SAP GUI脚本. SAP GUI脚本允许您以与自动化Excel几乎相同的方式来自动化Windows SAP GUI.实际上，您可以直接从Excel宏调用SAP GUI.在此处上阅读更多内容. SAP GUI具有类似于Excel的宏记录工具.它在VBScript中记录的宏几乎与Excel VBA相同，通常可以直接复制并粘贴到Excel宏中.

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系统的简单示例.

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

脚本录制

为帮助查找诸如wnd[1]/tbar[0]/btn[0]之类的元素的名称，您可以使用脚本记录. 单击自定义本地布局按钮，它可能看起来像这样:
然后找到脚本记录和播放"菜单项.

在其中More按钮允许您查看/更改VB脚本记录到的文件.输出格式有点混乱，它记录了诸如选择文本，在文本字段内单击等内容.

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.

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

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编辑器可以显示所使用对象的属性和方法，则需要在SAP GUI安装文件夹中添加对sapfewse.ocx的引用.

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屋！