从 SAP 到 Excel 再返回的重复脚本 [英] repeating script that goes from SAP to Excel and back again

问题描述

我正在尝试构建一个脚本，该脚本从 SAP 的庞大报告中提取数据，在 Excel 中运行一些清理，然后将其加载到 Access 中.至少，这是我最终的目标.SAP 中的数据有点污染，这就是为什么我们将 Access 作为我们部门特定的清理数据所在的地方.
我是脚本的新手(20 年后，我不记得我以前知道的很多东西)，但我一直在使用 SAP 和 Excel 中的内置宏生成器，并且已经获得了我想要的一部分.我现在遇到的是错误Microsoft Excel 正在等待另一个应用程序完成 OLE 操作".这可能是因为我的报告需要很长时间才能运行.它停止了这个过程，这应该是我可以设置并忘记并在早上拥有数据的事情.该脚本作为 Excel 中的宏启动，连接到 SAP 并在那里触发报告.报表运行，然后使用 SAP 将其导出回 Excel.鉴于报告的大小，我只能在一两个月内运行它，但我需要获取一整年的数据.我想我可以让脚本重复 6 次，每次 2 个月.任何超过 2 个月的时间，报告都会在产生任何结果之前超时.我曾尝试使用在 SAP 后台运行"来简化此操作，但生成的输出是如此乱码以至于毫无用处.这是我目前所拥有的.

I am trying to build a script that takes the data out of a huge report in SAP, runs some cleanup in Excel, and then load it into Access. At least, that is my eventual goal. The data in SAP is a bit polluted, which is why we are resorting to Access as the place where cleansed data specific to our department lives.
I'm new to scripting (after 20 years, I don't remember much of what I used to know) but I have been using the built in macro generators in both SAP and Excel and have gotten part of the way to what I want. What I am running into right now is the error "Microsoft Excel is waiting for another application to complete an OLE action ". This happens presumable because my report takes a very long time to run. It stops the process and this is supposed to be something I can set and forget and have the data in the morning. The script starts as a macro in Excel, connects to SAP and triggers the report there. The report runs and then uses SAP to export it back to Excel. Given the size of report I can only run it in one or two month chunks, but I need to get data for an entire year. I figure I can have the script repeat itself 6 times, 2 months at a time. Anything more than 2 months and the report will time out before it produces anything. I have tried to use "Run in Background on SAP to simplify this, but the output generated is so garbled as to be useless. Here is what I have so far.

Sub Experimental1()

' start the SAP portion

Dim SapGuiAuto, application, connection, session, WScript
If Not IsObject(application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject application, "on"
End If
'
' ***the above is key to opening a script in SAP.  SAP must be running for  this to work***
' begin ZSPWAR launch
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "zspwar"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 17
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]").sendVKey 8         session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellRow = 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "8"
session.findById("wnd[1]").sendVKey 2
' adjust the date
session.findById("wnd[0]/usr/ctxtS_DATE-LOW").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_DATE-HIGH").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
'begins save portion of script
'ignore warning from excel and keep going through long task
'application.IgnoreRemoteRequests = True
' end caffeine like behavior
session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
 session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press

'Ends Save portion

'back into Excel

  ChDir _
         "G:WarrantyStrategy'sSpecial Projects01 Process and Procedure      DocumentationDatabasesCore Return Database"
     ActiveWorkbook.SaveAs Filename:= _
         "G:WarrantyStrategy'sSpecial Projects01 Process and Procedure      DocumentationDatabasesCore Return DatabaseSPWAR test1.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

这就是我想要的，除了我必须照看 excel 错误消息，而且我不知道如何将焦点重新转移到 SAP

This does what I want, except I have to babysit through the excel error message and I dont know how to get the focus back to SAP

感谢您为我提供的任何帮助或文章.

Thanks for any help or articles you can direct me to.

推荐答案

我为您提供解决问题的方法.

I offer you a way to solve your problem.

例如:

Sub Experimental1()

' start the SAP portion

Dim SapGuiAuto, application, connection, session, WScript
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPapplication = SapGuiAuto.GetScriptingEngine
Set connection = SAPapplication.Children(0)
Set session = connection.Children(0)
'
' ***the above is key to opening a script in SAP.  SAP must be running for  this to work***
' begin ZSPWAR launch
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "zspwar"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 17
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").currentCellRow = 8
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "8"
session.findById("wnd[1]").sendVKey 2
' adjust the date
session.findById("wnd[0]/usr/ctxtS_DATE-LOW").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_DATE-HIGH").Text = (Date)
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_LGORT-LOW").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
'begins save portion of script
'ignore warning from excel and keep going through long task
'application.IgnoreRemoteRequests = True
' end caffeine like behavior
session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
 session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press

'Ends Save Portion

'for the SAP German version
'SAP_Workbook = "Tabelle von Basis (1)"

'for the SAP English version (?)
SAP_Workbook = "Worksheet in ALVXXL01 (1)"
EXCEL_Path = "G:WarrantyStrategy'sSpecial Projects01 Process and Procedure DocumentationDatabasesCore Return Database"
myWorkbook = "ZSPWAR test1.xlsx"


On Error Resume Next
Do
Err.Clear
Set xclapp = GetObject(, "Excel.Application")
If Err.Number = 0 Then Exit Do
'msgbox "Wait for Excel session"
wscript.sleep 2000
Loop


Do
Err.Clear
Set xclwbk = xclapp.Workbooks.Item(SAP_Workbook)
If Err.Number = 0 Then Exit Do
'msgbox "Wait for SAP workbook"
wscript.sleep 2000
Loop


On Error GoTo 0


Set xclsheet = xclwbk.Worksheets(1)

xclapp.Visible = True
xclapp.DisplayAlerts = False

xclapp.ActiveWorkbook.SaveAs EXCEL_Path & "" & myWorkbook
xclapp.ActiveWorkbook.Close


Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
Set xclapp = Nothing

'only if an information on the display
session.findById("wnd[1]/tbar[0]/btn[0]").press

问候，脚本人

这篇关于从 SAP 到 Excel 再返回的重复脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！