Input from Excel to SAP - make cycle.

[Former Member]

Hi masters.

Could someone help me with my different problem...again?

now Im working in trscn QM02. Excel create numbers of lines and add part number, then it fills long text of each part number... Script looks like this:

Private Sub ITEMS_Click()

Dim App, Connection, session As Object

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set session = Connection.Children(0)

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "qm02"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtRIWO00-QMNUM").Text = Range("L37").Value

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10").Select

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/ctxtVIQMFE-BAUTL[9,0]").SetFocus

Application.Wait DateAdd("s", 1, Now)

Range("C2:C25").Copy   '''''''''Add part numbers...thats ok

SendKeys "^v", True

Application.Wait DateAdd("s", 1, Now)

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/txtVIQMFE-POSNR[0,0]").SetFocus

Application.Wait DateAdd("s", 1, Now)

Range("Q2:Q25").Copy '''''''''Add numbers of lines...thats ok

SendKeys "^v", True

Application.Wait DateAdd("s", 1, Now)

SendKeys "{ENTER}", True

'' Now it opens first line long text and insert the value of copied cell.

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8,0]").SetFocus

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8,0]").press

Application.Wait DateAdd("s", 1 / 3, Now)

Range("G2").Copy

SendKeys "{PGDN}"

Application.Wait DateAdd("s", 1, Now)

SendKeys "^v", True

Application.Wait DateAdd("s", 1 / 3, Now)

session.findById("wnd[0]/tbar[0]/btn[3]").press   

''First Long text is filled. Now I have the same script but it focus on second row/line long text in sap and takes value from next cell in excel:

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8,1]").SetFocus

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8,1]").press

Application.Wait DateAdd("s", 1 / 3, Now)

Range("G3").Copy

SendKeys "{PGDN}"

Application.Wait DateAdd("s", 1, Now)

SendKeys "^v", True

Application.Wait DateAdd("s", 1 / 3, Now)

session.findById("wnd[0]/tbar[0]/btn[3]").press

.......

""And so on 24times...

End Sub

So is there a way how to make a loop in SAP, when I have for example 5 part numbers to have just one of this block - something like i, For and Loop function in excel? Its working now but there is error when it comes to row/line in SAP where it isnt long text (which is created by entering a number of line and adding PN, so 6th line in this case). Using error handler I jump to another command to save the inserted values in SAP and everything is OK, but I find it extremely unpractical, loooong and unsafe due other errors that may occur.

If there is a thread with this problem I will be pleased to just link me there or explain here.

And as usual thanks in advance for your effort and time.

[script_man · Accepted solution]

Hi Miroslav,

You could try the following:

. . .

'' Now it opens first line long text and insert the value of copied cell.

i = 0

do

if Range("G" & cstr(i + 2)).Value = 0 then exit do

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8," & cstr(i) & "]").SetFocus

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB10/ssubSUB_GROUP_10:SAPLIQS0:7210/tabsTAB_GROUP_20/tabp20\TAB01/ssubSUB_GROUP_20:SAPLIQS0:7110/tblSAPLIQS0POSITION_VIEWER/btnQMICON-LTFEHLER[8," & cstr(i) & "]").press

Application.Wait DateAdd("s", 1 / 3, Now)

Range("G" & cstr(i +2)).Copy

SendKeys "{PGDN}"

Application.Wait DateAdd("s", 1, Now)

SendKeys "^v", True

Application.Wait DateAdd("s", 1 / 3, Now)

session.findById("wnd[0]/tbar[0]/btn[3]").press

i = i + 1

loop

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

Exactly.

Cstr() - for me new, very useful command.

Thanks Maestro.