从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:\Warranty\Strategy's\Special Projects\001 Process and Procedure      Documentation\Databases\Core Return Database"
     ActiveWorkbook.SaveAs Filename:= _
         "G:\Warranty\Strategy's\Special Projects\001 Process and Procedure      Documentation\Databases\Core Return Database\ZSPWAR 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:\Warranty\Strategy's\Special Projects\001 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

关于， 脚本人

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