cancel
Showing results for 
Search instead for 
Did you mean: 

Extract data from SAP to Excell

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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

script_man
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi ScriptMan, thanks for ansver, but it's the same, only wisible data extract to Excell

script_man
Active Contributor
0 Kudos

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

Former Member
0 Kudos

i have replased to

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

But the same ..

script_man
Active Contributor
0 Kudos

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."


Former Member
0 Kudos

Hi Script Man,i'm afraid i dont fully understood how it's should be looks full macro.

;(

script_man
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi

this macro working correct.. but how can i extact data to Excell ?

script_man
Active Contributor
0 Kudos

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.


Former Member
0 Kudos

Hi Script Man,

this script working, but working strange, i need extract line by line

script_man
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

script_man
Active Contributor
0 Kudos

The following commands need to be replaced:

. . .

x = x + 1

on error resume next

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

if err.number <> 0 then

. . .

Sorry

script_man
Active Contributor
0 Kudos

Hi Evgeniy,

Is the issue solved?

Regards,

ScriptMan

Former Member
0 Kudos

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.