on 03-06-2012 9:41 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.