Skip to Content
avatar image
Former Member

VBA SAP Macro: Import Excel to SAP multiple cells

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 16 at 11:04 AM

    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

    Add comment
    10|10000 characters needed characters exceeded