VBA SAP Macro: Import Excel to SAP multiple cells

[Former Member]

Hey guys,

I am working with SAP and excel on daily basis. Part of the job is copy paste data frome excel to sap.

I've created macro that helps me to do that. The problem is macro copying pasting data only cel by cell.

I need to copy paste by 25 cells.

Any suggestions how can adjust my script so I can copy every 25 values and paste them?
Script is attached

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 xclapp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open("C:\.....\test.xlsm")
set xclsht = xclwbk.Sheets("Sheet1")

for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row
for j = 1 to xclapp.ActiveCell.SpecialCells(11).Column
if j=1 then SKU = xclsht.Cells(i,j).Value

next

session.findById("wnd[0]").resizeWorkingPane 254,39,false
session.findById("wnd[0]/usr/tblSAPMV13GTCTRL_FAST_ENTRY/ctxtKOMGG-PMATN[0,0]").text = SKU
session.findById("wnd[0]/usr/tblSAPMV13GTCTRL_FAST_ENTRY/ctxtKOMGG-PMATN[0,0]").setFocus
session.findById("wnd[0]/usr/tblSAPMV13GTCTRL_FAST_ENTRY/ctxtKOMGG-PMATN[0,0]").caretPosition = 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 11

'Here follows the rest of the VBScript.
next
msgbox "All " & cstr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed."

Set xclwbk = Nothing
Set xclsht = Nothing
xclapp.Quit
set xclapp = Nothing

[script_man]

Hi Oleh,

the program construction is very familiar to me. 😉

I have the following suggestion for you:

 . . .
 set xclsht = xclwbk.Sheets("Sheet1") 
 '---------------------------------------- new ----------------------------------------------------------
 k = 0
 l = 0
 for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row 
  SKU = xclsht.Cells(i,1).Value
  session.findById("wnd[0]/usr/tblSAPMV13GTCTRL_FAST_ENTRY/ctxtKOMGG-PMATN[0," & cstr(k) & "]").text = SKU 
  k = k + 1
  l = l + 1 
  if k = 25 then
   session.findById("wnd[0]/usr/ssubITEMS:SAPLFSKB:0100/tblSAPLFSKBTABLE").verticalScrollbar.position = l
   k = 0
  end if
 next
 'session.findById("wnd[0]").sendVKey 11 
 '---------------------------------------- new -----------------------------------------------------------
 msgbox "All " & cstr(xclapp.ActiveCell.SpecialCells(11).Row - 1) & " Excel rows have been processed." 
 . . .

I have disabled the command session.findById("wnd[0]").sendVKey 11 on purpose.. If everything has been transferred from Excel and is OK, you can also save it manually.

Regards,

ScriptMan