vba code to copy a column from sap window and paste the values in excel sheet

Question

I have a VBA code that scripts some SAP transactions then select a whole column from a grid on SAP GUI.

I am successful till here but I want to copy the column and paste it in an Excel sheet, but I can't do that.

I tried to open the context menu to choose copy text but it doesn't work. I tried to send CTRL+C stroke from keyboard but also failed. If someone can help me with that I will be very grateful.

Here is my code:

Sub lolo()

If Not IsObject(App) Then
    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
     Set Connection = App.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 App, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/btn%_EQUNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/txtSELCOUNT").Text = "1"
session.findById("wnd[0]/usr/txtSELCOUNT").SetFocus
session.findById("wnd[0]/usr/txtSELCOUNT").caretPosition = 14
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell -1, "PTTXT"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "PTTXT"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&FILTER"
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[2]/usr/lbl[1,23]").SetFocus
session.findById("wnd[2]/usr/lbl[1,23]").caretPosition = 2
session.findById("wnd[2]").sendVKey 2
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell -1, "CNTRC"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "CNTRC"
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "^C"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu


End Sub

score 0 · Answer 1 · answered Jun 01 '18 at 14:01

0

I expect the OP is no longer interested in the answer but thinking that people are still reading this question...

add a form to your vba project then the following code into your module

Dim DataObj As MSForms.DataObject 
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
sString = DataObj.GetText(1)

This pulls the data from your clipboard into sString. You can then pass the string to a cell value.

answered Jun 01 '18 at 14:01

tsuimark

53
4

This doesn't solve the problem as OP isn't even able to get the column into the clipboard – Nacorid Feb 20 '20 at 11:16

score 0 · Answer 2 · answered Feb 20 '20 at 11:23

I had the same problem, depending on the transaction I either got

session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[1]/tbar[0]/btn[0]").press

or

session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItemBytext "Copy Text"

which both didn't copy the column.

What solved my predicament was this handy line:

session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectContextMenuItemByPosition "0"

score 0 · Answer 3 · answered Jul 05 '22 at 11:48

Could you try with this? I've integrated parts of code that I use to extract data from these types of SAP sheets..

Sub lolo()

If Not IsObject(App) Then
    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
     Set Connection = App.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 App, "on"
End If
dim t as object
dim arr as variant
dim index1 as long
session.findById("wnd[0]/usr/btn%_EQUNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/txtSELCOUNT").Text = "1"
session.findById("wnd[0]/usr/txtSELCOUNT").SetFocus
session.findById("wnd[0]/usr/txtSELCOUNT").caretPosition = 14
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").setCurrentCell -1, "PTTXT"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectColumn "PTTXT"
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&FILTER"
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[2]/usr/lbl[1,23]").SetFocus
session.findById("wnd[2]/usr/lbl[1,23]").caretPosition = 2
session.findById("wnd[2]").sendVKey 2
session.findById("wnd[1]/tbar[0]/btn[0]").press
Set t = session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
    
ReDim arr(0 To t.RowCount, 1 To 2)
    
arr(0, 1) = "Column1"
arr(0, 2) = "Column2"
    
For index1 = 1 To t.RowCount

    If t.VisibleRowCount + t.FirstVisibleRow < index1 Then
        t.FirstVisibleRow = index1
    End If
    
    arr(index1, 1) = t.GetCellValue(index1 - 1, "CNTRC")
    'arr(index1, 2) = t.GetCellValue(index1 - 1, <any other column>)
    
Next index1

'To print it to excel alter this to fit into your excel sheet:
ThisWorkbook.Sheets(1).Range("A1:A" & UBound(arr) + 1).Offset(0, 0) = arr

end sub

vba code to copy a column from sap window and paste the values in excel sheet

3 Answers3