通过 VBA 完全导出 SAP 以达到卓越

Export SAP to excel completely via VBA

提问by Thomas Ossenblok

I hope you can assist me with the code below. I am trying to export a report from SAP using a recorded SAPscript via VBA on a daily basis. Subsequently I want to copy the data to another workbook and do some reformatting/calculations with it. However, the excel export file will only open after I have fully completed my macro.

我希望你能帮助我使用下面的代码。我正在尝试每天通过 VBA 使用记录的 SAPscript 从 SAP 导出报告。随后我想将数据复制到另一个工作簿并用它进行一些重新格式化/计算。但是，excel 导出文件只有在我完全完成我的宏后才会打开。

Using for instance ShellAndWait http://www.cpearson.com/excel/ShellAndWait.aspxassumes that it is an outside application, but I would like to find a solution in which I can keep everything in 1 file.

例如使用 ShellAndWait http://www.cpearson.com/excel/ShellAndWait.aspx假定它是一个外部应用程序，但我想找到一种解决方案，在其中我可以将所有内容保存在 1 个文件中。

Thanks in advance for your assistance!

提前感谢你的帮助！

Edit: Solved! Thanks to Scriptman.

编辑：解决了！感谢脚本人。

ExportInvest

出口投资

Public Sub ExportInvest()

Dim Today As String
Dim FileName As String
Dim Ret

Today = Format$(DateTime.Now, "yyyymmdd__hh-MM-ss")
FileName = "Invest_" & Today & ".xml"

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

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzx03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtENAME-LOW").Text = "TXO4074"
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "S:\FL_DMA\SAP Scripts\test\backup"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = FileName
session.findById("wnd[1]/tbar[0]/btn[0]").press

Set wshell = CreateObject("Wscript.Shell")
wshell.Run "C:\Users\txo4074\Documents\Projects\SAPscriptINVEST\myTest.vbs" & " " & "999Invest.xlsm" & " " & FileName

End Sub

MyTest.vbs

MyTest.vbs

set xclapp = getObject(,"Excel.Application")

on error resume next
do 
    err.clear
    Set xclwbk = xclApp.Workbooks.Item(wscript.arguments(1))
    If Err.Number = 0 Then exit do
    wscript.sleep 2000
loop
on error goto 0

xclapp.Run wscript.arguments(0) & "!ThisWorkbook.ExportInvest_1"

ExportInvest_1

出口投资_1

Sub ExportInvest_1()


FileName = NewestFile("S:\FL_DMA\SAP Scripts\test\backup", "****")

Workbooks(Left(FileName, 25)).Worksheets(1).Activate
Range("A2:K100").Select
Selection.Copy ThisWorkbook.Worksheets("Input").Range("A2:K100")
Workbooks(Left(FileName, 25)).Close
ThisWorkbook.Activate

End Sub

采纳答案by ScriptMan

Sorry, I used only a small amount of data for my tests. Therefore, an extension must now be incorporated into the proposed construction.

抱歉，我只使用了少量数据进行测试。因此，现在必须在提议的结构中加入扩展。

for example:

例如：

ExportInvest: (in myTest.xlsm)

出口投资：（在 myTest.xlsm 中）

. . .
'Dim FileName As String
. . .
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "S:\FL_DMA\SAP Scripts\test\backup"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = FileName
session.findById("wnd[1]/tbar[0]/btn[0]").press

Set wshell = CreateObject("Wscript.Shell")
'-----------------------------------------new
wshell.Run "c:\tmp\myTest.vbs" & " " & "myTest.xlsm" & " " & FileName
'-----------------------------------------new
End Sub

myTest.vbs:

myTest.vbs：

set xclapp = getObject(,"Excel.Application")

'-----------------------------------------new
on error resume next
do 
 err.clear
 Set xclwbk = xclApp.Workbooks.Item(wscript.arguments(1))
 If Err.Number = 0 Then exit do
 wscript.sleep 2000
loop
on error goto 0
'-----------------------------------------new

xclapp.Run wscript.arguments(0) & "!ExportInvest_1"

ExportInvest_1: (in myTest.xlsm)

ExportInvest_1：（在 myTest.xlsm 中）

Public FileName as String

Sub ExportInvets_1()
 'Dim Today as String
 'Today = Format$(DateTime.Now, "yyyymmdd__hh-MM-ss")

 MsgBox "file open", vbOKOnly, ""
 Workbooks(FileName).Activate
 Worksheets(1).Range("A2:K100").Select
 Selection.Copy ThisWorkbook.Worksheets("Input").Range("A2:K100")
End Sub

Regards, ScriptMan

问候， ScriptMan

回答by ScriptMan

I would solve it as follows:

我会解决它如下：

ExportInvest: (in myTest.xlsm)

出口投资：（在 myTest.xlsm 中）

. . .
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "S:\FL_DMA\SAP Scripts\test\backup"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = FileName
session.findById("wnd[1]/tbar[0]/btn[0]").press

'new commands
Set wshell = CreateObject("Wscript.Shell")
wshell.Run "c:\tmp\myTest.vbs" & " " & "myTest.xlsm"
End Sub

myTest.vbs:

myTest.vbs：

set xclapp = getObject(,"Excel.Application")
xclapp.Run wscript.arguments(0) & "!ExportInvest_1"

ExportInvest_1: (in myTest.xlsm)

ExportInvest_1：（在 myTest.xlsm 中）

Sub ExportInvets_1()
    Dim Today as String
    Today = Format$(DateTime.Now, "yyyymmdd__hh-MM-ss")

    MsgBox "file open", vbOKOnly, ""
    Workbooks("Invest_" & Today).Activate
    Worksheets(1).Range("A2:K100").Select
    Selection.Copy ThisWorkbook.Worksheets("Input").Range("A2:K100")
End Sub

Regards, ScriptMan

问候， ScriptMan