cancel
Showing results for 
Search instead for 
Did you mean: 

Loop in SAP taking value from two column

Former Member
0 Kudos

Hello Experts..

I want to do Looping in VBA to update one fields in SAP, but taking from 2 columns (start row A2:B2 ) in excel file .

A column is for product code while B column is for value of the updated field.

My below trial code can not read the value in column B but only take from column A and then put the value on the updated field. It's should take value from coulmn A for ref ( product code) then take value from column B to update data.

Also I want the loop will ended if the next row is empty.

Appreciate for any correction of the codes.

Thank you .

Dim *vNilai* As Variant
    
    'Workbooks("LoopTrial.xls").Activate
    'Worksheets("Sheet1").Activate
    'Range("i2:j20").Activate
     
    
    With SAPSesi
                
        .findById("wnd[0]").maximize
        .findById("wnd[0]/tbar[0]/okcd").Text = "/NMM02"
        .findById("wnd[0]").sendVKey 0
        
        For Each *vNilai* In Workbooks("LoopTrial.xls").Worksheets("Sheet1").Range("A2:A20,B2:B20")
    
        
        .findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = *vNilai*
        .findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 8
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]").sendVKey 0
        
        'ActiveCell.Offset(0, 1).Select
        
                
        .findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").Text = *vNilai*
        .findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").SetFocus
        .findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").caretPosition = 2
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]/tbar[0]/btn[11]").press
        
         Next *vNilai*
        
        .findById("wnd[0]/tbar[0]/okcd").Text = "/n"
        .findById("wnd[0]").sendVKey 0
           
    End With

Best regards,

Agung

View Entire Topic
script_man
Active Contributor
0 Kudos

Hi Agung,

my suggestion is as follows:


. . .
'For Each *vNilai* In Workbooks("LoopTrial.xls").Worksheets("Sheet1").Range("A2:A20,B2:B20")
for i = 2 to 20
    
        *vNilai* = cells(i,1).value
        *vNilai*_1 = cells(i,2).value

        .findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = *vNilai*
        '.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 8
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]").sendVKey 0
        
        'ActiveCell.Offset(0, 1).Select
        
                
        .findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").Text = *vNilai*_1
        .findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").SetFocus
        '.findById("wnd[0]/usr/tabsTABSPR1/tabpSP13/ssubTABFRA1:SAPLMGMM:2000/subSUB4:SAPLMGD1:2486/txtMARC-EISBE").caretPosition = 2
        .findById("wnd[0]").sendVKey 0
        .findById("wnd[0]/tbar[0]/btn[11]").press
        
         'Next *vNilai*
         Next i
. . .

Regards,

ScriptMan

Former Member
0 Kudos

Hi ScriptMan ,

Thanks for the reply ...it's works but this will read the value on the cells until row=20 , when the value on the cell let's say only 8 rows this will get an error. Any suggestion for the loop which will only execute until the next rows is empty ?

Thanks.

Best regards,

Agung

script_man
Active Contributor
0 Kudos

Hi Agung,

it could be that you have more than 20 rows in Excel. My suggestion is now as follows:


. . .
for i = 2 to xclapp.ActiveCell.SpecialCells(11).Row
*vNilai* = cells(i,1).value
*vNilai*_1 = cells(i,2).value
if *vNilai* = "" or *vNilai*_1 = "" then exit for
. . .
next i

Regards,

ScriptMan

Former Member
0 Kudos

Thank you so much ScriptMan..