Skip to Content

Sap scripting : Copy Pasting from Excel to SAP

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Feb 01, 2019 at 07:49 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 05, 2019 at 10:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.