- SAP Community
- Products and Technology
- Additional Q&A
- Export data out of SM37 via vbscript?
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Export data out of SM37 via vbscript?
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 12-24-2012 6:53 PM
Hi everyone,
I process a lot of data in the background and then i go to t-code SM37 and have to extract the data one by one. I click on each spool in the step list and then send it to a local file. I'd like to create a script to do this, and then assign the variant name or spool number as the file name. i have been able to record a script to do the exporting, but i am still unable to edit the script to go down the spool list and ignore the items with no spool, and then if there is a spool, to export it, and assign the variant name as the file name. Can anyone help??
Accepted Solutions (0)
Answers (1)
Answers (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Christina,
unfortunately I do not work with the TA SM37. But I've seen it. Only spool to select you could try the following:
. . .
session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[1]/menu[0]").select
session.findById("wnd[1]/usr/btnB_SEARCH").press
session.findById("wnd[2]/usr/txtGD_SEARCHSTR").text = "Spool"
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/btnAPP_WL_SING").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
. . .
This should select only lines with spool. You could set also filter all of first manually without a script to use it.
If the selected cell contains the name you want, you can proceed as follows:
. . .
session.findById("wnd[0]/usr/lbl[14,3]").setFocus
session.findById("wnd[0]/usr/lbl[14,3]").caretPosition = 0
myFileName = session.findById("wnd[0]/usr/lbl[14,3]").text
session.findById("wnd[0]").sendVKey 2
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = myFileName & ".xls"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").setFocus
'please deactivate
'session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 18
session.findById("wnd[1]/tbar[0]/btn[0]").press
. . .
Regards,
ScriptMan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Scriptman, Thanks for the reply. I'm not fully understanding the first section of code. It pulls up the print screen. the second section of code works good, but it keeps erroring out during the myFileName = .... Also, i'd like to get the script to go to the next row and continue on, but if i try to assign i=3 and then enter [14,i] it's not working. Below is my code. The items that are commented out are previous code attempts/ideas. If Not IsObject(application) Then Set SapGuiAuto = GetObject("SAPGUI") Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then Set connection = application.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 application, "on" End If session.findById("wnd[0]").maximize session.findById("wnd[0]/mbar/menu[1]/menu[0]").select session.findById("wnd[1]/usr/btnB_SEARCH").press session.findById("wnd[2]/usr/txtGD_SEARCHSTR").text = "Spool" session.findById("wnd[2]/tbar[0]/btn[0]").press session.findById("wnd[1]/usr/btnAPP_WL_SING").press session.findById("wnd[1]/tbar[0]/btn[0]").press session.findById("wnd[1]").sendVKey 4 session.findById("wnd[2]/tbar[0]/btn[0]").press session.findById("wnd[1]/tbar[0]/btn[0]").press 'session.findById("wnd[0]/usr/lbl[42,3]").setFocus 'session.findById("wnd[0]/usr/lbl[42,3]").caretPosition = 1 'session.findById("wnd[0]/tbar[1]/btn[47]").press 'session.findById("wnd[1]/usr/ctxtBTCH1120-VARIANT").setFocus 'session.findById("wnd[1]/usr/ctxtBTCH1120-VARIANT").caretPosition = 10 'myFileName = session.findById("wnd[1]/usr/ctxtBTCH1120-VARIANT").text 'session.findById("wnd[1]/tbar[0]/btn[0]").press session.findById("wnd[0]/usr/lbl[14,3]").setFocus session.findById("wnd[0]/usr/lbl[14,3]").caretPosition = 0 myFileName = session.findById("wnd[0]/usr/lbl[14,3]").text session.findById("wnd[0]").sendVKey 2 session.findById("wnd[0]/tbar[1]/btn[48]").press session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus session.findById("wnd[1]/tbar[0]/btn[0]").press 'Enter path and filename session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Documents and Settings\m310761\My Documents\CHRISSY SOTO\SOD" session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = myFileName & ".xls" session.findById("wnd[1]/tbar[0]/btn[0]").press 'session.findById("wnd[0]/tbar[1]/btn[34]").press 'session.findById("wnd[0]/usr/lbl[14,3]").setFocus 'session.findById("wnd[0]/usr/lbl[14,3]").caretPosition = 0 'session.findById("wnd[0]").sendVKey 2 'session.findById("wnd[0]/tbar[1]/btn[48]").press 'session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select 'session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus 'session.findById("wnd[1]/tbar[0]/btn[0]").press 'session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Documents and Settings\m310761\My Documents\CHRISSY SOTO\SOD" 'session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = myFileName & ".XLS" 'session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8 'session.findById("wnd[1]/tbar[0]/btn[0]").press
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Christina,
please record the script for setting the filter to the "spool" itself. I guess we have different layouts.
You could also manually configure filtering for spool. (e.g. the column spool mark -> menu -> command Edit -> command Filter set -> ...)
In the table, you could be move for example as follows.
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.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 application, "on"
End If
session.findById("wnd[0]").maximize
i = 3
i_max_old = 0
do
on error resume next
session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").setfocus
if err.number <> 0 then
'msgbox "I check whether I call next site or whether I finish the script."
i_max_new = i
if i_max_old > i_max_new and i_max_old > 0 then
'msgbox "I finish the script."
exit do
end if
i_max_old = i_max_new
'msgbox "I'm going to next site."
session.findById("wnd[0]").sendVKey 82
err.clear
i = 3
session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").setfocus
end if
on error goto 0
'You can the follow line deactivate.
'session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").caretPosition = 0
myFileName = session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").text
session.findById("wnd[0]").sendVKey 2
session.findById("wnd[0]/tbar[1]/btn[48]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
'Enter path and filename
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Documents and Settings\m310761\My Documents\CHRISSY SOTO\SOD"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = myFileName & ".xls"
session.findById("wnd[1]/tbar[0]/btn[0]").press
'msgbox "I'm going to next line."
i = i + 1
loop
My scripts should be as an example for you. You have to be adapted to your conditions.
Regards,
ScriptMan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Scriptman, Thank you so much! 🙂 I can't express how great your help is. I was able to take your suggestions and add them to my code and i was able to get it to work correctly. One downside though. The myFileName code just refuses to work. Here is my semi-final code: (you'll have to ignore the lines that show what line number it is, i use that so that when i get an error, i don't have to count each line from the beginning): If Not IsObject(application) Then Set SapGuiAuto = GetObject("SAPGUI") Set application = SapGuiAuto.GetScriptingEngine End If If Not IsObject(connection) Then Set connection = application.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 application, "on" End If 'line 15 session.findById("wnd[0]").maximize For i = 3 to 38 'line 20 'click on Variant Name session.findById("wnd[0]/usr/lbl[53,"& cstr(i) & "]").setFocus 'click on spool button session.findById("wnd[0]/tbar[1]/btn[34]").press If session.findById("wnd[0]/sbar").text <> "No list available" Then 'click on spool square button session.findById("wnd[0]/usr/lbl[14,3]").setFocus session.findById("wnd[0]/usr/lbl[14,3]").caretPosition = 0 session.findById("wnd[0]").sendVKey 2 'line 35 'click on Send to Local File button session.findById("wnd[0]/tbar[1]/btn[48]").press 'click on spreadsheet radio button and press enter session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus session.findById("wnd[1]/tbar[0]/btn[0]").press 'enter path and file name and press enter 'i tried two ways to get the variant name 'myFileName = session.findById("wnd[0]/usr/lbl[53,"& cstr(i) & "]").text 'myFileName = session.findById("wnd[1]/usr/ctxtBTCH1120-VARIANT").text session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Documents and Settings\m310761\My Documents\CHRISSY SOTO\SOD\" 'i decided to use the text myFileName and then use i as a counter so that each file would have a different number and would save as a new file. session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "myFileName" & i & ".xls" session.findById("wnd[1]/tbar[0]/btn[0]").press session.findById("wnd[0]/tbar[0]/btn[3]").press session.findById("wnd[0]/tbar[0]/btn[3]").press End If Next
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Happy New Year, Christina,
It all looks very good at what you do. My next suggestion is as follows:
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.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 application, "on"
End If
'line 15
session.findById("wnd[0]").maximize
For i = 3 to 38
'line 20
'click on Variant Name
session.findById("wnd[0]/usr/lbl[53," & cstr(i) & "]").setFocus
'click on spool button
session.findById("wnd[0]/tbar[1]/btn[34]").press
If session.findById("wnd[0]/sbar").text <> "No list available" Then
'click on spool square button
session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").setFocus
'the next line can be deactivate
session.findById("wnd[0]/usr/lbl[14," & cstr(i) & "]").caretPosition = 0
session.findById("wnd[0]").sendVKey 2
'line 35
'click on Send to Local File button
session.findById("wnd[0]/tbar[1]/btn[48]").press
'click on spreadsheet radio button and press enter
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
'enter path and file name and press enter
'i tried two ways to get the variant name
'myFileName = session.findById("wnd[0]/usr/lbl[53," & cstr(i) & "]").text
'myFileName = session.findById("wnd[1]/usr/ctxtBTCH1120-VARIANT").text
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Documents and Settings\m310761\My Documents\CHRISSY SOTO\SOD\"
'i decided to use the text myFileName and then use i as a counter so that each file would have a different number and would save as a new file.
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "myFileName" & cstr(i) & ".xls"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
End If
Next
The first 38 lines are likely to be the first page. On the next page you come e.g. with the command:
session.findById("wnd[0]").sendVKey 82
Have fun by testing.
Regards,
ScriptMan
P.S. Within Notepad.exe there is a setting that gives you the line number of the code.
for example:
menu -> View -> Status Bar
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Scriptman, Happy New year! Thank you for your continued help. The one section of my code that will not work is this: myFileName = session.findById("wnd[0]/usr/lbl[53,"& cstr(i) & "]").text session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = myFileName & cstr(i) & ".xls" Do you know how I can fix this? Thank you for your help! 🙂 - Chrissy
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Chrissy,
there are apparently more people in the world who are working on their free day. 🙂
Could it be that you forgot a space?
e.g.
myFileName = session.findById("wnd[0]/usr/lbl[53," & cstr(i) & "]").text
Otherwise, it would be interesting, in which line the error actually is and how he is.
Good luck!
ScriptMan
- SUBMIT Statement in SAP ABAP (Updated for S/4HANA) for migrate your data (Migration cockpit) in Enterprise Resource Planning Blogs by Members
- Smooth transition to ABAP for Cloud Development(Cheat sheet) in Technology Blogs by Members
- SAP Important Tcodes - FICO MM SD PM BODS in Enterprise Resource Planning Blogs by Members
- Where can I find knowledge and information belongs to SAP HANA? in Technology Blogs by SAP
- SAPGui Script to Export Spool Once Complete in Technology Q&A
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.