Sap scripting : Copy Pasting from Excel to SAP

[former_member1372647]

Hello all,

I developed a script to load values from an Excel tab (12 columns (12 months), n lines (n=number of references)) to SAP APO (Planning book).

Everything works well with the following code looping on my 12 months and my n lines to declare that APO cell i,j value is equal to Excel value i,j :

For j = 1 To lastreference - vertical_excel_offset

For i = 1 To 12

objSess.findById("wnd[0]/usr/subSUB_MAIN:/SAPAPO/SAPLMSDP_SDP:0027/cntlSDP_CUSTOM_CONTROL/shellcont/shell/shellcont[0]/shell/shellcont[0]/shell").setCellValue i + horizontal_offset_pb, j + vertical_offset_pb, objSheet.Cells(j + 1, i + 2)

Next i

Next j

Since I have 12*n iterations I would like to improve the code to make program run faster by replacing this one by one value filling by a simple copy paste Excel tab selection into APO tab (same dimensions for reminder).

Fact is :

- SAP VBS code generator doesn't recognize CTRL+V and doesn't generate any associated code.

- Session.findById("wnd[0]").sendVKey 78 doesn't work (error : "The Virtual Key is not enabled." + I found here that the function (grayed out) is disabled https://experience.sap.com/files/guidelines/References/nv_fkeys_ref2_e.htm (consistent with my error message)).

- Simulate the pasting with Windows VBS doesn't seem to work (however without bugging) with following instructions :

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.SendKeys "^v"

Is there a way to do so or is it definitely impossible ?

My 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 IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject application, "on"

End If

Set WshShell = WScript.CreateObject("WScript.Shell")

'copy : done manually at the moment in Excel file (12 columns values)

session.findById("wnd[0]/usr/subSUB_MAIN:/SAPAPO/SAPLMSDP_SDP:0027/cntlSDP_CUSTOM_CONTROL/shellcont/shell/shellcont[0]/shell/shellcont[0]/shell").setCellValue 3,1,"1" 'works, write 1 in my first cell, allows to selection the cell from where I would paste

WshShell.SendKeys "^v" '--> No effect

'WshShell.SendKeys 78 --> Virtual Key is not enabled

Thank you for your help.

[former_member1372647]

Thank you Stefan.

So as far as I understood, there is no way to basically paste an Excel range directly into SAP APO.

The only method seems to be what I did, each cell in SAP equals to corresponding Excel cell, then next one etc.

Do you know why Session.findById("wnd[0]").sendVKey 78 is unusable ? There is no way to activate it ?

Thank you.

Julien

[stefan_schnell]

Hello Julien,

welcome in the SAP Community.

VBScript offers no native ways to use the clipboard. There are several examples how you can use the clipboard via htmlfile, IE or other applications. Here an example from stackoverflow:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate("about:blank")
objIE.document.parentwindow.clipboardData.SetData "text", "Hello This Is A Test"
objIE.Quit

You could use also your code from Excel VBA, this offers much more possibilities as VBScript.

Best regards
Stefan