cancel
Showing results for 
Search instead for 
Did you mean: 

Cut & Paste Column of data from ALV

peter_atkin
Active Contributor
0 Kudos

Hi all,

I've searched the internet/community for a suitable post and the nearest thing I found is this: http://scn.sap.com/thread/1226566 which doesn't really work for us..

We are wanting to do the following:

  • Count the number of filtered/unfiltered records in an ALV (in the current session)
  • Be able to select a column of ALV data and cut it to the clipboard (in the current session)

Is this possible with scripting, and if so, does anyone have any sample code

One last question: how do I debug the scripts?

Thanks in advance.

PeteA

Accepted Solutions (1)

Accepted Solutions (1)

script_man
Active Contributor
0 Kudos

Hi Pete,

since 2009, one has also done a lot. Today, I would try to solve the problem as follows.

for example:

. . .

set GRID = session.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell")

GRID.selectColumn "RECNNR"

GRID.firstVisibleRow = GRID.RowCount - 1

GRID.contextMenu

GRID.selectContextMenuItembyPosition "0"

msgbox "the number of filtered/unfiltered records: " & GRID.RowCount,  vbinformation , "Currently count of records"

. . .

It has been very helpful for me the following links.

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

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

I used to debug my VB scripts as follows:

WScript.exe //x  "myVBScript.vbs"

Today, you might have to watch the following:

http://scn.sap.com/docs/DOC-39696

Regards,

ScriptMan


peter_atkin
Active Contributor
0 Kudos

Thanks ScriptMan,

The code above gets the current ALV row count, however if you have filtered the ALV it won't tell you the unfiltered count (only filtered count).

Also the code doesn't use the current session, so if I have IW38 open twice, the code above only runs for the data in the 1st session even though I have the 2nd session open.

I still have an issue selecting data from an ALV with a large number of rows - I get the usual following message: "Not all data has been copied into the clipboard".

I have added a post to the 2nd link above as this is very similar to my issue.

The debugging worked great thanks.

Thanks for your help.

PeteA

script_man
Active Contributor
0 Kudos

Hi Pete,

the property RowCount of GuiApoGrid displays always the current number. But you may find out that the display is filtered.

for example:

. . .

if GRID.IsColumnFiltered("RECNNR") then

   msgbox "The filtered count: " & GRID.RowCount, vbinformation, "Currently count"

else

   msgbox "The unfiltered count: " & GRID.RowCount, vbinformation, "Currently count"

end if

. . .

The contents of the clipboard can be determined by an active filter in an ALV - Grid. If you do not want to have empty rows in the clipboard, you have to filter it before.

You can always connect to the current session. You have to know only how the VB script is started.

for example from script recorder and playback from the active SAP session :

f 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

set asession = application.ActiveSession

set GRID = asession.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell")

. . .

for example from any other program, but you know the number of the session (e.g. 2):

f 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

Set asession    = connection.Children(1)

set GRID = asession.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell")

. . .

My tests have shown that the message "Not all data has been copied into the clipboard" is wrong.
The command GRID.firstVisibleRow = GRID.RowCount - 1 has scrolled through the table. It can therefore be suppressed.


for example:

. . .

set GRID = asession.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell")

GRID.selectColumn "RECNNR"

GRID.firstVisibleRow = GRID.RowCount - 1

GRID.contextMenu

GRID.selectContextMenuItembyPosition "0"

on error resume next

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

on error goto 0

. . .

Where should the contents be copied from the clipboard?

Regards,

ScriptMan

peter_atkin
Active Contributor
0 Kudos

ScriptMan

Some comments:

  1. Is it possible to determine the number of total records AND filtered records at the same time e.g. issue a single message that states: Total records: 1000, Filtered Records 500
  2. The records in the ALV are not blank, its when I either export to clipboard or write to a TXT file that blank records are generated (see Maximilian Queck's post which indicates he had the same issue and solved it, but does not explain how). Maximilian seems to indicate that if you page down, then you will be able to get more of this records, and this is true if you page down manually. How can I page down using scripting?

PeteA

script_man
Active Contributor
0 Kudos

Pete

          1. I do not know this command, unfortunately. One could query via SAP GUI scripting the number                before  filtering. Then you could set the filter via script, and re-calculate the number.

         2. You could for example try the following.:

. . .

set GRID = asession.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell")

GRID.selectColumn "RECNNR"

GRID.pressToolbarButton "&MB_FILTER"

asession.findById("wnd[1]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").text = "*001"

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

ROW_COUNT_1 = GRID.RowCount

GRID.selectColumn "RECNNR"

GRID.pressToolbarButton "&MB_FILTER"

asession.findById("wnd[1]/tbar[0]/btn[16]").press

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

ROW_COUNT_2 = GRID.RowCount

GRID.selectColumn "RECNNR"

i=0

do

GRID.firstVisibleRow = i

i = i + 40

if i >= GRID.RowCount - 1 then exit do

loop

GRID.contextMenu

GRID.selectContextMenuItembyPosition "0"

msgbox "Total records: " & ROW_COUNT_2 & vbcr & vbcr & "Filtered records: " & ROW_COUNT_1 , vbMsgBoxForeground + vbInformation , "Currently count"

Regards,

ScriptMan

peter_atkin
Active Contributor
0 Kudos

Its a bit rough, but here's my script code that appears to work.

My last question: say I have two sessions open, both with ALVs, how do I ensure that I have the current session selected?

Script Code

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 Not IsObject(SapGuiAuto) Then

   Set SapGuiAuto = GetObject("SAPGUI")

End If

If Not IsObject(Application) Then

   Set Application = SapGuiAuto.GetScriptingEngine()

End If

If Not IsObject(Connection) Then

   Set Connection = Application.Connections(0)

End If

If Not IsObject(Session) Then

   Set Session = Connection.Sessions(0)

End If

Set Grid = Session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")

' Get data for paging-down

RowCount = Grid.RowCount

VisibleRows = Grid.VisibleRowCount

VisiblePages = (RowCount / VisibleRows) - 1

' Get current column selected by user

SelectedColumn = Grid.CurrentCellColumn

Grid.SelectColumn SelectedColumn

Grid.FirstVisibleRow = Grid.RowCount - 1

'Page to bottom of ALV

For Pages = 1 To VisiblePages

    Grid.FirstVisibleRow = (Pages * VisibleRows)

Next

' Select all text

Grid.ContextMenu

Grid.SelectContextMenuItemByPosition "0"

' Return to first row

Grid.FirstVisibleRow = 0

Grid.ClearSelection

Grid.CurrentCellRow = 0

Grid.CurrentCellColumn = SelectedColumn

'MsgBox "Number of records: " & Grid.RowCount, vbInformation, "Count of Records"

' Cancel popup message (Not all data has been copied into the clipboard)

On Error Resume Next

    Session.FindById("wnd[1]/tbar[0]/btn[0]").Press

On Error GoTo 0

script_man
Active Contributor
0 Kudos

The code I really like. But something I would still improve.

1. This last part of the code is not needed. After the reading of the ALV should  the message no longer appear.

' Cancel popup message (Not all data has been copied into the clipboard)

  On Error Resume Next

  Session.FindById("wnd[1]/tbar[0]/btn[0]").Press

  On Error GoTo 0

And if the message should appear, then already behind this command:

Grid.SelectContextMenuItemByPosition "0"

2. So as I understand it, your code is always in the first session.

 

    Counter question: How run You the script?

peter_atkin
Active Contributor
0 Kudos

Thanks again Script Man

I'll amend my code as suggested above.

I call the script from the following menu:

Select the Customised Local Layout button on top right-hand side of every screen

- Script Recording and Playback (which opens the record & Playback popup)

-- Select the Playback Script button (green arrow)

--- Then choose my script

Question 1: Is there a way of displaying the open sessions and selecting which one you want the script to run against?

Question 2: How do I remove this message which is shown every time I run a script:

PeteA

script_man
Active Contributor
0 Kudos

Hi Pete,

with this run you can address the current session as follows.

set mySession = application.ActiveSession

1. You can try the follows:

'the max.number of SAP sessions

session_number_max = 6

ReDim session_number_(Session_number_max)

ReDim session_name_(Session_number_max)

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

For session_number = 0 to Connection.Children.Count - 1

    Set mySession = Connection.Children(Int(session_number))

    session_number_(mySession.info.sessionnumber) = mySession.info.sessionnumber

    session_name_(mySession.info.sessionnumber) = mySession.findById("wnd[0]").text 

Next

allSessions = "Session" & chr(9) & "Name" & vbcr

allSessions = allSessions & "---------------------------------------------------------" & vbcr

for i = 1 to Connection.Children.Count

    allSessions =  allSessions & session_number_(i) & chr(9) &  session_name_(i) & vbcr

next

newSession = inputbox(allSessions,"Please enter the number of the session")

if newSession <> "" then

  For session_number = 0 to Connection.Children.Count - 1

    Set mySession = Connection.Children(Int(session_number))

    mySession.findById("wnd[0]").iconify

  Next

  For session_number = 0 to Connection.Children.Count - 1

    Set mySession = Connection.Children(Int(session_number))

    if cstr(mySession.info.sessionnumber) = newSession then

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

       msgbox "The current session is the number: " & mySession.info.sessionnumber

       exit for

    end if

  Next

end if

2. First you need to make an important setting, so the pop-up window will not come. See especially the point 2 :

http://www.erpexecutive.com/2010/12/how-to-enable-sap-gui-scripting/

Regards,

ScriptMan

Answers (0)