How to loop at existing Excel rows till the last row

Question

I need to update an Excel sheet table from a SAP GUI table that has a different number of rows each time. Therefore, I need to count the cells in column "A" and set them as in my code. See below an excerpt of the code.

My code works fine but I think there is an easier way than to write such a code for any number of rows.

Sub SAP_OpenSessionFromLogon()
Dim SapGui
Dim Applic
Dim connection
Dim session
Dim WSHShell
Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe"
Set WSHShell = CreateObject("WScript.Shell")
Do Until WSHShell.AppActivate("SAP Logon ")
Application.Wait Now + TimeValue("0:00:01")
Loop
Set WSHShell = Nothing
Set SapGui = GetObject("SAPGUI")
Set Applic = SapGui.GetScriptingEngine
Set connection = Applic.OpenConnection("System", True)
Set session = connection.Children(0)
session.findById("wnd[0]").Maximize
session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "Name"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "PW"
session.findById("wnd[0]").sendVKey 0
session.SendCommand ("/nZPS_RE008K_NEW")
session.findById("wnd[0]").Maximize
session.findById("wnd[0]/usr/ctxtI_VBUKR").Text = "1200"
Set xclsht = ActiveSheet
Set sht = Nothing
Set wbk = Nothing
Set xcl = Nothing
If Sheets("FORMAT").Cells(6, 1) Like "*IE-12-2007984*" Then
End If
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(6, 1)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Format").Cells(6, 3).Value = myGrid.getcellvalue(i, "ZZ_PTREL")
Sheets("Format").Cells(6, 30).Value = myGrid.getcellvalue(i, "CASTKA_ROZPOCTU")
session.findById("wnd[0]/tbar[0]/btn[3]").press
If Sheets("FORMAT").Cells(7, 1) Like "*IE-12-2007984*" Then
End If
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(7, 1)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Format").Cells(7, 3).Value = myGrid.getcellvalue(i, "ZZ_PTREL")
Sheets("Format").Cells(7, 30).Value = myGrid.getcellvalue(i, "CASTKA_ROZPOCTU")
session.findById("wnd[0]/tbar[0]/btn[3]").press
If Sheets("Format").Cells(8, 1) Like "*IE-12-2007984*" Then
End If
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(8, 1)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Format").Cells(8, 3).Value = myGrid.getcellvalue(i, "ZZ_PTREL")
Sheets("Format").Cells(8, 30).Value = myGrid.getcellvalue(i, "CASTKA_ROZPOCTU")
session.findById("wnd[0]/tbar[0]/btn[3]").press
If Sheets("Format").Cells(9, 1) Like "*IE-12-2007984*" Then
End If
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(9, 1)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Format").Cells(9, 3).Value = myGrid.getcellvalue(i, "ZZ_PTREL")
Sheets("Format").Cells(9, 30).Value = myGrid.getcellvalue(i, "CASTKA_ROZPOCTU")
session.findById("wnd[0]/tbar[0]/btn[3]").press
If Sheets("Format").Cells(10, 1) Like "*IE-12-2007984*" Then
End If
session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(10, 1)
session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
session.findById("wnd[0]/tbar[1]/btn[8]").press
Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
Sheets("Format").Cells(10, 3).Value = myGrid.getcellvalue(i, "ZZ_PTREL")
Sheets("Format").Cells(10, 30).Value = myGrid.getcellvalue(i, "CASTKA_ROZPOCTU")
session.findById("wnd[0]/tbar[0]/btn[3]").press
End Sub

Answer 1

You could test the following:

...
Set xcl = Nothing
i = 6
do
 'If Sheets("FORMAT").Cells(i, 1) Like "*IE-12-2007984*" Then
  'End If
  session.findById("wnd[0]/usr/ctxtI_PSPID-LOW").Text = Sheets("Format").Cells(i, 1)
  session.findById("wnd[0]/usr/ctxtP_VARI").Text = "/NEW 008K"
  session.findById("wnd[0]/tbar[1]/btn[8]").press
  Set myGrid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
  Sheets("Format").Cells(i, 3).Value = myGrid.getcellvalue(0, "ZZ_PTREL")
  Sheets("Format").Cells(i, 30).Value = myGrid.getcellvalue(0, "CASTKA_ROZPOCTU")
  session.findById("wnd[0]/tbar[0]/btn[3]").press
 i = i + 1
 If Sheets("Format").Cells(i, 1).Value = 0 Then Exit Do
Loop
End Sub 

Ahoj, ScriptMan