Excel VBA从SAP NetWeaver提取数据 [英] Excel VBA pulling data from SAP NetWeaver

问题描述

如何使用VBA宏将数据从SAP NetWeaver提取到Excel中?

How do you pull data from SAP NetWeaver into Excel using a VBA macro?

我在SAP系统中有数据，可以通过SAP GUI通过一些不同的事务进行访问.我需要每天提取这些数据，然后将其格式化为Excel电子表格和报告.手动提取这些数据非常耗时且容易出错.

I have data in an SAP system accessible in a few different transactions through the SAP GUI. I need to extract this data on a daily basis, then format it into Excel spreadsheets and reports. Manually extracting this data is very time consuming and error prone.

我没有开发人员或系统支持.作为非特权用户，我必须能够自己进行操作.

I don't have developer or system support. I have to be able to do it myself as an unprivileged user.

推荐答案

这是从我的答案中复制过来的: https://stackoverflow .com/a/19456656/2250183

This is copied from my answer here: https://stackoverflow.com/a/19456656/2250183

这一切都取决于您对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.

这篇关于Excel VBA从SAP NetWeaver提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！