on 10-10-2012 1:29 PM
Hi SAP community,
i have next question.
does anybody know, hov can i extract data from SAP direct to Excell. ineed extract line by line
i have example next code
Sub testExtData()
If Not IsObject(sap) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set sap = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = sap.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 sap, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb52"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "a709"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").Text = "ua38"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtLGORT-LOW").caretPosition = 4
session.findById("wnd[0]").sendVKey 8
'x - rows numb
Cells(x, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/x[0,x]/lbl[1,x]").Text
Cells(x, 2) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/x[0,x]/lbl[14,x]").Text
'etc
End Sub
Thank for response
I found next solution, but it'sworking only for wisible data on page,only 37 lines, then needed scrol down
For x = 3 To 10000
On Error GoTo erh
Cells(x, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[1," & x & "]").Text
Cells(x, 2) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[14," & x & "]").Text
Cells(x, 3) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[55," & x & "]").Text
Cells(x, 4) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[60," & x & "]").Text
Cells(x, 5) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[65," & x & "]").Text
Cells(x, 6) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[80," & x & "]").Text
Cells(x, 7) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[99," & x & "]").Text
Cells(x, 😎 = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[118," & x & "]").Text
Next
erh: Exit Sub
End Sub
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Evgeniy,
You can try it as follows:
x = 2
For y = 3 To 10000
x = x + 1
Cells(x, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[1," & x & "]").Text
on error resume next
if err.number <> 0 then
err.clear
session.findById("wnd[0]").sendvkey 82
x = 3
Cells(x, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[1," & x & "]").Text
end if
on error goto 0
Cells(x, 2) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[14," & x & "]").Text
Cells(x, 3) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[55," & x & "]").Text
Cells(x, 4) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[60," & x & "]").Text
Cells(x, 5) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[65," & x & "]").Text
Cells(x, 6) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[80," & x & "]").Text
Cells(x, 7) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[99," & x & "]").Text
Cells(x, 😎 = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[118," & x & "]").Text
Next
Regards
ScriptMan
Hi Evgeniy,
in this case you must do the following:
1. start script recorder in the SAP
2. record the command for the next 37 lines (page down -> press Enter key on the slider)
3. stop script recorder
4. replace the old command with a new command from the recorded script
(old = session.findById("wnd[0]").sendvkey 82 and new = ?)
5. Done
Regards
ScriptMan
If you've done everything right, then this script goes through all the records to the end:
. . .
do
session.findById("wnd[0]/tbar[0]/btn[82]").press
if text_old = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[14,3]").Text then exit do
text_old = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[14,3]").Text
loop
msgbox "Done."
Hi Evgeniy,
It should first be just a test macro:
Sub test_macro()
If Not IsObject(sap) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set sap = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = sap.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 sap, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb52"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "a709"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").Text = "ua38"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtLGORT-LOW").caretPosition = 4
session.findById("wnd[0]").sendVKey 8
for i = 1 to 10
session.findById("wnd[0]/tbar[0]/btn[82]").press
next
msgbox "The first 10 pages were viewed."
End Sub
Only when this test macro works correctly, you can go one step further.
Regards,
ScriptMan
The next step is as follows:
Sub test_macro_1()
If Not IsObject(sap) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set sap = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = sap.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 sap, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb52"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "a709"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").Text = "ua38"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtLGORT-LOW").caretPosition = 4
session.findById("wnd[0]").sendVKey 8
for i = 1 to 10
cells(i,2) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[14,3]").Text
session.findById("wnd[0]/tbar[0]/btn[82]").press
next
msgbox "The first parameter of the first 10 pages were exported to excel."
End Sub
Only when this test_macro_1 works correctly, you can go one step further.
Hi Evgeniy,
I did not say that this is the full script. Through the test before, I understood where you have made a mistake. Here's the full script:
Sub full_macro()
If Not IsObject(sap) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set sap = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = sap.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 sap, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb52"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "a709"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").Text = "ua38"
session.findById("wnd[0]/usr/ctxtLGORT-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtLGORT-LOW").caretPosition = 4
session.findById("wnd[0]").sendVKey 8
x = 2
For y = 3 To 10000
x = x + 1
Cells(y, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[1," & x & "]").Text
on error resume next
if err.number <> 0 then
err.clear
session.findById("wnd[0]/tbar[0]/btn[82]").press
x = 3
Cells(y, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[1," & x & "]").Text
if err.number <> 0 then exit for
end if
on error goto 0
Cells(y, 2) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[14," & x & "]").Text
Cells(y, 3) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[55," & x & "]").Text
Cells(y, 4) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[60," & x & "]").Text
Cells(y, 5) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[65," & x & "]").Text
Cells(y, 6) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[80," & x & "]").Text
Cells(y, 7) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[99," & x & "]").Text
Cells(y, 😎 = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[118," & x & "]").Text
Next
msgbox "All need parameters of the all pages were exported to excel."
Regards,
ScriptMan
End Sub
Unfortunatelly this script isn't working.
1- instead Cells(y, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/3[0,3]/lbl[1," & x & "]").Text
should be
Cells(y, 1) = session.findById("wnd[0]/usr/sub/1[0,0]/sub/1/2[0,0]/sub/1/2/" & x & "[0," & x & "]/lbl[1," & x & "]").Text
i have change but it working only for first 37 lines
Hi Script Man,
sorry i didn't answer to you.
Yes, it's working but in first column got only one item every 37 lines.
also i detected, unfortunatelly this script works slow , because i have report in SAP more then 2000 lines and this script works 2 or more minuts.
i use another script which download report to txt file to PC then opened in excell, and it's working fast
however many thanks for your help. i got some idea for my another script.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.