Script for the SAP report - download to excel - problem

[Former Member]

Hi all

I know that similar problem had already been discussed but going through the solution presented in
any of that topic I was not able to be successful  (I am beginner in terms of VBA and SAP scripting)

What I would like to do is simply create the SAP script which based on the parameters filled
in in the excel sheet (user form created) will connect to SAP and make the report which I would like to have saved on my local drive as an excel file.
Everything is ok till the moment I would like to save it.  As you know SAP records the macro only till the moment some window pops up (where and under what name you would like to have your report  saved)

Could you please advise  ?

As a screen I attached also point where macro stops (maybe this makes difference)

Thank you in advance for any suggestions.

Below my code.

Private Sub CommandButton1_Click()

Dim MojaData

MojaData = ComboBox2.Value

Dim Companycode

Companycode = ComboBox1.Value

Dim Depreciation_area

Depreciation_area = ComboBox3.Value

If Not IsObject(Application1) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set Application1 = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

Set Connection = Application1.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 Application1, "on"

End If

Dim sbar As String

sbar = session.findById("wnd[0]/sbar").Text

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/n s_alr_87011990"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtBUKRS-LOW").Text = Companycode

session.findById("wnd[0]/usr/ctxtSO_ANLKL-LOW").Text = ""

session.findById("wnd[0]/usr/ctxtBERDATUM").Text = MojaData

session.findById("wnd[0]/usr/ctxtBEREICH1").Text = Depreciation_area

session.findById("wnd[0]/usr/ctxtSRTVR").Text = "0003"

session.findById("wnd[0]/usr/radSUMMB").SetFocus

session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select

??

[Harel]

HI,

try the following:

http://scn.sap.com/thread/1540783

Regards,

harel

[Former Member]

I was having the same problem.  Google'd the error message and found many forums/blogs that mention spaces inside paths can cause this error.  There are a few workarounds though.  I don't have all of them to reference (I closed my web tabs before noting them all) but ensuring double quotes around the string that has spaces worked for me.

Main Program that calls the secondary script:

'//execute report
SAPsession.findById("wnd[0]/tbar[1]/btn[8]").press

'//start another script to watch for the File Select window
Wshell.Run Chr(34) & CurrentProject.Path & strWShellScript & Chr(34) & " " & strSAPFileNm, 1, False

'//click download button
SAPsession.findById("wnd[0]/tbar[1]/btn[21]").press

Secondary Script in ".vbs" file:

Dim strSAPFileNm2
Dim strWindowNm


Set Wshell = CreateObject("WScript.Shell")
strWindowNm = "File Select."


Do
bWindowFound = Wshell.AppActivate(strWindowNm)
WScript.Sleep 1000
Loop Until bWindowFound
bWindowFound = Wshell.AppActivate(strWindowNm)


If (bWindowFound) Then
Wshell.AppActivate strWindowNm
Wshell.CurrentDirectory = "c:\temp"
WScript.Sleep 1000
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"   
WScript.Sleep 1000
strSAPFileNm2 = WScript.Arguments.Item(0)
Wshell.sendkeys "c:\temp"
WScript.Sleep 1000
Wshell.sendkeys "{ENTER}"
WScript.Sleep 1000
Wshell.SendKeys strSAPFileNm2
WScript.Sleep 1000
Wshell.SendKeys "{ENTER}"
WScript.Sleep 1000
End If

This is my very first post in this community, and I've not yet mastered inserting code into posts...so let me know if it doesn't post well. 

For my application, I'm using VB inside of Access 2010 to run reports in SAP, download them, manipulate them, then distribute them.

[Former Member]

MJ MJ - I cannot replicate a screen like you have to test it but here's what I would suggest:

Immediately above the command that brings up the SAVE AS dialog, insert these lines:

... your code up to here.....

FileName = "C:\Apps\Notifications.txt"     '<----- enter your save file path/name
Wshell.run "C:\Apps\DataLoad.vbs " & FileName,1,False     '<-- call up the loader program

... then continue with your code....

Then build a program "dataload.vbs" as follows:

Dim FileNam2
Set Wshell = CreateObject("WScript.Shell")
Do 
bWindowFound = Wshell.AppActivate("Save As") 
WScript.Sleep 1000
Loop Until bWindowFound
bWindowFound = Wshell.AppActivate("Save As") 
if (bWindowFound) Then
Wshell.appActivate "Save As"
WScript.Sleep 100
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"
Wshell.sendkeys "{TAB}"   'make 4 or 5 depending on the platform 4:XP, 5:Win7
WScript.Sleep 100
FileNam2 = WScript.Arguments.Item(0)  
Wshell.sendkeys FileNam2
WScript.Sleep 100
Wshell.sendkeys "{ENTER}"
WScript.Sleep 100
end if

----------------------------------

This should do the trick of copying your filename into the filename box in the SAVE AS dialog.As I said, I did not get to test it but that is the theory.

Trial-and-error is the only way in many cases.

Good luck

Regards

Umur

[Former Member]

Hello Umur,

Thank you for your answer

command that brings up the SAVE AS dialog is

session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select

..but I get the error on line

Wshell.Run "C:\New folder\DataLoad.vbs " & Filename, 1, False

My code now looks like below (this is all my code together with Userform settings )

Private Sub CommandButton1_Click()

Dim MojaData
MojaData = ComboBox2.Value

Dim Companycode
Companycode = ComboBox1.Value

Dim Depreciation_area
Depreciation_area = ComboBox3.Value

If Not IsObject(Application1) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Application1 = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Application1.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 Application1, "on"
End If

Dim sbar As String
sbar = session.findById("wnd[0]/sbar").Text

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n s_alr_87011990"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtBUKRS-LOW").Text = Companycode
session.findById("wnd[0]/usr/ctxtSO_ANLKL-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtBERDATUM").Text = MojaData
session.findById("wnd[0]/usr/ctxtBEREICH1").Text = Depreciation_area
session.findById("wnd[0]/usr/ctxtSRTVR").Text = "0003"

session.findById("wnd[0]/usr/radSUMMB").SetFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press

Filename = "C:\New folder\Notifications.txt"
Wshell.Run "C:\New folder\DataLoad.vbs " & Filename, 1, False

session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select

Dim FileNam2
Set Wshell = CreateObject("WScript.Shell")
Do
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 1000
Loop Until bWindowFound
bWindowFound = Wshell.AppActivate("Save As")
If (bWindowFound) Then
Wshell.AppActivate "Save As"
WScript.Sleep 100
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"  
WScript.Sleep 100
FileNam2 = WScript.Arguments.Item(0)
Wshell.SendKeys FileNam2
WScript.Sleep 100
Wshell.SendKeys "{ENTER}"
WScript.Sleep 100

End If

End Sub

Private Sub UserForm_Initialize()
'Empty Company.code.box
ComboBox1.Clear

'Fill Company.code.box
With ComboBox1
     .AddItem "BE10"
     .AddItem "BE11"
     .AddItem "BE12"
     .AddItem "BE14"
     ComboBox1.Value = "BE10"
         
End With

'Empty Asset.Balance.box
ComboBox2.Clear

'Fill Asset.Balance.box
With ComboBox2
     .AddItem "31.01.2014"
     .AddItem "28.02.2014"
     .AddItem "31.03.2014"
     .AddItem "30.04.2014"
     .AddItem "31.05.2014"
     .AddItem "30.06.2014"
     .AddItem "31.07.2014"
     .AddItem "31.07.2014"
     .AddItem "31.08.2014"
     .AddItem "30.09.2014"
     .AddItem "31.10.2014"
     .AddItem "30.11.2014"
     .AddItem "31.12.2014"
     ComboBox2.Value = "31.01.2014"
    
End With

[Former Member]

Hi MJ MJ;

The section below is a separate vbs file by itself, and it should be saved as DataLoad.vbs in this case. Remove it from your main program and save as a separate file:

Dim FileNam2
Set Wshell = CreateObject("WScript.Shell")
Do
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 1000
Loop Until bWindowFound
bWindowFound = Wshell.AppActivate("Save As")
If (bWindowFound) Then
Wshell.AppActivate "Save As"
WScript.Sleep 100
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"
Wshell.SendKeys "{TAB}"  
WScript.Sleep 100
FileNam2 = WScript.Arguments.Item(0)
Wshell.SendKeys FileNam2
WScript.Sleep 100
Wshell.SendKeys "{ENTER}"
WScript.Sleep 100

End If

Also, I do not see the command at the top of your main program:

Set Wshell = CreateObject("WScript.Shell")

to be able to execute the call:

Wshell.Run "C:\New folder\DataLoad.vbs " & Filename, 1, False

The idea is you are in the main program and calling another vbs program to watch out for the Open or Save As dialog. So 2 programs are running at the same time.

Try again adn hopefully it will work now.

Regards

Umur

[Former Member]

Hi Umur,

As you see I am in fact real beginner  🙂
I did exactly as you said – so I created new vbs file DataLoad.vbs – with the code mentioned by you and save this under path C:/New Folder/
..but I think there is a problem to call DataLoad.vbs
I get the error on the line  (see screen)
Wshell.Run "C:\New folder\DataLoad.vbs" & Filename, 1, False

Error description

"Method 'Run' of object 'IWshShell3' failed"

I past again my code just to be sure that from your perspective it is ok.

Private Sub CommandButton1_Click()

Set Wshell = CreateObject("WScript.Shell")

Dim MojaData
MojaData = ComboBox2.Value

Dim Companycode
Companycode = ComboBox1.Value

Dim Depreciation_area
Depreciation_area = ComboBox3.Value

If Not IsObject(Application1) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Application1 = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Application1.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 Application1, "on"
End If

Dim sbar As String
sbar = session.findById("wnd[0]/sbar").Text

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n s_alr_87011990"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtBUKRS-LOW").Text = Companycode
session.findById("wnd[0]/usr/ctxtSO_ANLKL-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtBERDATUM").Text = MojaData
session.findById("wnd[0]/usr/ctxtBEREICH1").Text = Depreciation_area
session.findById("wnd[0]/usr/ctxtSRTVR").Text = "0003"

session.findById("wnd[0]/usr/radSUMMB").SetFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press

Filename = "C:\New folder\Notifications.txt"
Wshell.Run "C:\New folder\DataLoad.vbs" & Filename, 1, False

session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select

End Sub

Private Sub UserForm_Initialize()
'Empty Company.code.box
ComboBox1.Clear

'Fill Company.code.box
With ComboBox1
     .AddItem "BE10"
     .AddItem "BE11"
     .AddItem "BE12"
     .AddItem "BE14"
     ComboBox1.Value = "BE10"
         
End With

'Empty Asset.Balance.box
ComboBox2.Clear

'Fill Asset.Balance.box
With ComboBox2
     .AddItem "31.01.2014"
     .AddItem "28.02.2014"
     .AddItem "31.03.2014"
     .AddItem "30.04.2014"
     .AddItem "31.05.2014"
     .AddItem "30.06.2014"
     .AddItem "31.07.2014"
     .AddItem "31.07.2014"
     .AddItem "31.08.2014"
     .AddItem "30.09.2014"
     .AddItem "31.10.2014"
     .AddItem "30.11.2014"
     .AddItem "31.12.2014"
     ComboBox2.Value = "31.01.2014"
    
End With

'Fill Depreciation.area.box
With ComboBox3
.AddItem "01"
.AddItem "45"

ComboBox3.Value = "01"

End With

End Sub

[Former Member]

Mj MJ - I am pasting a drawing here.. this is working for me with a "Open" dialog. the key here is to have your main running and calling the dataload.vbs right before you trigger the "Open" dialog box so it drops the filename and presses Open button, and once the dialog box is removed, the next command should countinue from the Main program. You have to have the correct files in the correct folders referenced.

The error message "Method'Run' of object 'IWshShell3' failed" doesn't make sense.. looks like you are calling WshShell instead of what the code shows as Wshell.. try this again with correct call. Should work.

Regards

Umur

[Former Member]

Hi MJ MJ - this has been discussed in the past and there is a solution supplied in this link: .

It works from the 'load' side, wherease you need from the 'save' side but it is the same idea in handling the dialog box. If you follow th ethread it should clearly explain the solution. I have been using this approach for quite some time successfully.

Good luck

Regards

Umur

[Former Member]

Hi Umur,

Thanks a lot for  prompt replay.

I have seen that topic already and tried to “play” a little bit with it …but did not make any step forward.

That’s why I post new topic just to get some answer for my individual example.

I would really appreciated if you could help me with some solution.

Thx a lot.

MJ MJ