vba 从 SAP 到 Excel 再返回的重复脚本

repeating script that goes from SAP to Excel and back again

提问by Paul TIKI

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.

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

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:\Warranty\Strategy's\Special Projects
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:\Warranty\Strategy's\Special Projects
application.DisplayAlerts = false
1 Process and Procedure Documentation\Databases\Core 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
1 Process and Procedure      Documentation\Databases\Core Return Database"
     ActiveWorkbook.SaveAs Filename:= _
         "G:\Warranty\Strategy's\Special Projects
sub test
    Dim wsh
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run ("cscript ""C:\your path\s1.vbs""")
    wsh.Run ("cscript ""C:\your path\s2.vbs""")
    wsh.Run ("cscript ""C:\your path\s3.vbs""")
    wsh.Run ("cscript ""C:\your path\s4.vbs""")
    wsh.Run ("cscript ""C:\your path\s5.vbs""")
    wsh.Run ("cscript ""C:\your path\s6.vbs""")
end sub
1 Process and Procedure      Documentation\Databases\Core Return Database\ZSPWAR test1.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

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

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

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

感谢您可以指导我的任何帮助或文章。

采纳答案by ScriptMan

I offer you a way to solve your problem.

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

For example:

例如：

##代码##

Regards, ScriptMan

问候， ScriptMan

回答by Paul TIKI

Thanks to everyone for your help!

感谢大家的帮助！

The eventual solution to the problem ended up being to break the scripting down into smaller modules and then running them sequentially, with

该问题的最终解决方案是将脚本分解成更小的模块，然后按顺序运行它们，使用

##代码##

being the first line in the overall program. Now I have 3 macros in total in excel. The first is my initial code with the final lines at the bottom trying to start a save removed. This leaves a solid, functioning core. I created a separate save macro that works just fine. The third macro runs the line that kills the alerts first, followed by the other macros, in sequence, with a 1 minute forced pause in between because the time it takes to pass control back and forth on the machine I have at work is inconsistent as heck.

是整个程序的第一行。现在我在excel中总共有3个宏。第一个是我的初始代码，底部的最后几行试图开始保存删除。这留下了一个坚实的，运作的核心。我创建了一个单独的保存宏，效果很好。第三个宏运行首先终止警报的行，然后是其他宏，依次执行，中间有 1 分钟的强制暂停，因为在我工作的机器上来回传递控制所需的时间不一致，因为哎呀。

If anyone would like to see the actual code, I'll put it up, but it's pretty long. I just wanted to share the general technique of building chunks and then stringing them together separately like this. It has worked on many projects for me, I don't know why I didn't try it first.

如果有人想看实际代码，我会把它贴出来，但它很长。我只是想分享构建块的一般技术，然后像这样将它们分别串在一起。它为我完成了许多项目，我不知道为什么我没有先尝试一下。

Again, Thanks everybody, and I hope this helps someone else in the future.

再次感谢大家，我希望这能在未来帮助其他人。

回答by Ravi Kalidosan

In SAP you can run maximum 6 session:

在 SAP 中，您最多可以运行 6 个会话：

1. create 6 session

2. split your duration with 6 (example 10 days each)

3. create s1.vba,s2.vbs,s3.vbs,s4.vbs,s5.vbs,s6.vbs (copy your code into this file)

4. in vba editor use the following to run all 6 sessions simultaneously

1. 创建 6 个会话

2. 将您的持续时间分成 6 个（例如每个 10 天）

3. 创建 s1.vba,s2.vbs,s3.vbs,s4.vbs,s5.vbs,s6.vbs（将您的代码复制到此文件中）

4. 在 vba 编辑器中使用以下命令同时运行所有 6 个会话

回答by Ravi Kalidosan

##代码##