Export SAP to excel completely via VBA

Question

I hope you can assist me with the code below. I am trying to export a report from SAP using a recorded SAP GUI Script 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.

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

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

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

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

Maybe something like this: https://stackoverflow.com/questions/37065764/how-to-check-until-file-exist — Ryan Wildry, Aug 02 '17 at 15:54
Thanks for your input Ryan. Unfortunately this doesn't work either, because the file does exist, but just isn't open yet. Therefore I tried checking if the file is open already and then executing the loop using https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open . The code above is updated to reflect this. Unfortunately, this keeps the code looping because the workbook still won't open. — Thomas Ossenblok, Aug 03 '17 at 07:58
You can always iterate the workbooks collection looking for the file of interest. You can check the .FullName property to see if this matches the file of interest. — Ryan Wildry, Aug 03 '17 at 14:14
Unfortunately this doesn't seem to work, also not with .fullname. The problem is that excel is stuck in a state where it is still downloading the export from SAP. I snapped a picture of it in the link below https://postimg.org/image/70mqchddl/ — Thomas Ossenblok, Aug 03 '17 at 14:36
Perhaps do a loop checking the filesize until it stops increasing? — Ryan Wildry, Aug 03 '17 at 15:08

ScriptMan · Accepted Answer · 2017-08-04T08:28:16.127

1

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)

. . .
'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:

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)

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

edited Aug 04 '17 at 08:28

answered Aug 04 '17 at 06:30

ScriptMan

1,580
1
9
9

Hi ScriptMan, Thank you very much! The only issue that remained was that the variable 'Today' is different in ExportInvest_1, since it is executed at a different time. I solved this by finding the newest file in the map where I download the export file, using http://spreadsheetpage.com/index.php/tip/identifying_the_newest_file_in_a_directory/ – Thomas Ossenblok Aug 04 '17 at 07:59
Hi Thomas, another possibility would be to define FileName as public. Please see my changes above. – ScriptMan Aug 04 '17 at 08:24
Thanks. In the meantime I have ran into another problem however. What if the the filename (in your case MyTest.xlsm) of the workbook changes each day. How would I adapt myTest.vbs to reflect that? Sorry if I am bugging you ,but I have virtually no professional experience with this :) – Thomas Ossenblok Aug 04 '17 at 09:35
ExportInvest: wshell.Run "c:\tmp\myTest.vbs" & " " & ActiveWorkBook.Name & " " & FileName ;) – ScriptMan Aug 04 '17 at 09:56
Yes, I've tried that but I get the error: Cannot run the Macro. This while the String: '999INVEST2017-08-04.xlsm!thisworkbook.ExportInvest_1' is correct. Any idea what might cause this? – Thomas Ossenblok Aug 04 '17 at 10:17
a workaround: ExportInvest_1 in a modul and then '999INVEST2017-08-04.xlsm!ExportInvest_1' – ScriptMan Aug 04 '17 at 11:35
Hi Scriptman, apparently the "-" in the dates of the string caused an issue. When I save the workbook to 999INVEST20170804.xlsm I don't encounter any issues. But now, my very last problem: The MyTest.VBS is located on a shared disc that can be accessed by other users. When they want to execute my code however excel crashes as soon as it reaches wshell.run. Any thoughts what might cause this? Also, I will be forever in your debt if you can answer this for me as well :) – Thomas Ossenblok Aug 04 '17 at 12:28
Hopefully it is not because the path at wshell.run contains a space. In this case, the call should look as follows: wshell.Run """path with spaces.myTest.vbs""" & ... – ScriptMan Aug 05 '17 at 12:33

ScriptMan · Answer 2 · 2017-08-03T10:05:42.173

0

I would solve it as follows:

ExportInvest: (in 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:

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

ExportInvest_1: (in 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

edited Aug 03 '17 at 10:05

answered Aug 03 '17 at 09:50

ScriptMan

1,580
1
9
9

Hi Thomas, what is the parameter by wshell.run? – ScriptMan Aug 03 '17 at 10:38
Hello again. I deleted my comment since I found out that I indeed used a wrong parameter. Sorry for that. However, the export workbook still only opens after I completed all my code, now after Exportinvest_1().. – Thomas Ossenblok Aug 03 '17 at 11:56
This is already worth a lot. If everything is running properly, excel.exe will only appear once in Windows Task Manager. You could also experiment with xclapp.visible = true in myTest.vbs or with the order of the commands in ExportInvest_1: workbooks(...).activate and then msgbox "file open". – ScriptMan Aug 03 '17 at 12:30
Well, since the workbook with exported data from sap won't open untill all code including ExportInvest_1 is executed, I cannot copy any data to myTest.xlsm. Therefore it will always result in a subscript out of range error. – Thomas Ossenblok Aug 03 '17 at 14:01
I suspect this is related to the following: The problem is that excel is stuck in a state where it is still downloading the export from SAP. I snapped a picture of it in the link below postimg.org/image/70mqchddl – Thomas Ossenblok Aug 03 '17 at 14:44

Export SAP to excel completely via VBA

2 Answers2

Linked