title: VB Script To Loop Excel Data by Row
body: <p>I am working on a VB script that grabs data from Excel and inputs it into a screen within an IBM 3270 mainframe.  With the code below, I am able to open the excel workbook and copy the data by cell and then input the value from the cell chosen into the 3270 screen using subEnterData and subMovecursor procedures that I've defined.  It works greats.  But as you can see from my code below that I am only grabbing data from the cells located in Row 2 of the excel object (Row 1 is headers).  I need to grab data from each cell within each row and then move to the next Row. So, after Row 2 is finished, I need to move to Row 3, go to each cell within Row 3, copy data from each cell and paste it to a screen in 3270, then the same with Row 4 and so on.  They're about 50 Rows, but there could be more or less.</p>
  
  <p>Below is the main body of the code:</p>
  
  <pre><code>Option Explicit
  Dim objExcel, objExcel1, objExcel2
  Dim atlDirectorObject, oFileObject
  Dim atl3270Tool 
  Dim oMstrCmp 
  Dim ErrMsg
  '---------------------Excel Column Placement
  Dim strLoanNumber_3_7
  Dim strFlag_11_5
  Dim strDate_11_10 
  Dim strINV_11_21
  Dim strCAT_11_27
  Dim strEBalance_11_53
  Dim strLPIDate_11_35
  Dim strNewLoanNumber_11_68
  Dim strServiceFee_16_7
  Dim strADDLInvstor_21_66
  Dim strRemitCTRL_11_76
  Dim strINVBalance_13_68
  '---------------------Excel Column Placement
  Set atlDirectorObject = CreateObject("atlDirectorObject.atlDirector")
  Set oFileObject       = CreateObject("Scripting.FileSystemObject")
  Set objExcel          = createobject("Excel.Application")
  Set objExcel1         = 
  objExcel.Workbooks.Open("S:\Scripting\0711_Settlement2.xlsx")
  Set objExcel2         = objExcel1.Worksheets("Import")
  
  subGetSession
  subDelpScreen
  
  'objExcel1.Close
  'objExcel.Quit
   Set objExcel          = Nothing
   Set objExcel1         = Nothing
   Set objExcel2         = Nothing
   Set atlDirectorObject = Nothing
   Set oFileObject       = Nothing
   Set atl3270Tool       = Nothing
  </code></pre>
  
  <p>You can see in subStringExcelData, that I am invoking the object and getting the value:</p>
  
  <pre><code>Sub subStringExcelData
  objExcel.Visible = True
  strLoanNumber_3_7       = objExcel1.Worksheets("Import").Cells(2,1).Value
  strFlag_11_5            = objExcel1.Worksheets("Import").Cells(2,2).Value
  strDate_11_10           = objExcel1.Worksheets("Import").Cells(2,3).Value
  strINV_11_21            = objExcel1.Worksheets("Import").Cells(2,4).Value
  strCAT_11_27            = objExcel1.Worksheets("Import").Cells(2,5).Value
  strLPIDate_11_35        = objExcel1.Worksheets("Import").Cells(2,6).Value
  strEBalance_11_53       = objExcel1.Worksheets("Import").Cells(2,7).Value
  strNewLoanNumber_11_68  = objExcel1.Worksheets("Import").Cells(2,8).Value
  strServiceFee_16_7      = objExcel1.Worksheets("Import").Cells(2,9).Value
  strADDLInvstor_21_66    = objExcel1.Worksheets("Import").Cells(2,10).Value
  strRemitCTRL_11_76      = objExcel1.Worksheets("Import").Cells(2,11).Value
  strINVBalance_13_68      = objExcel1.Worksheets("Import").Cells(2,12).Value
  End Sub
  </code></pre>
  
  <p>Then I am using subDoWork, to find the right location on the 3270 screen and paste the value in the correct location.  It works great, but I need to be able to do this with many rows and the way I have done this, I can currently only get one row at a time.  Please Help!</p>
  
  <p>Sub subDelpScreen holds the trigger to run subDoWork.</p>
  
  <pre><code>Sub subDoWork
  subClearScreen
  subGoToScreen "DELP", "********", ""
  subStringExcelData
  'subPressKey "@E"
      subMoveCursor 3, 7 
      subEnterData strLoanNumber_3_7
  subPressKey "@E"
      subMoveCursor 11, 5 
      subEnterData strFlag_11_5
      subMoveCursor 11, 10
      subEnterData strDate_11_10
      subMoveCursor 11, 21
      subEnterData strINV_11_21
      subMoveCursor 11, 27
      subEnterData strCAT_11_27
      subMoveCursor 11, 35
      subEnterData strLPIDate_11_35
      subMoveCursor 11, 56
      subEnterData strEBalance_11_53
      subMoveCursor 11, 68
      subEnterData strNewLoanNumber_11_68
      subMoveCursor 13, 71
      subEnterData strINVBalance_13_68
      subMoveCursor 16, 7
      subEnterData strServiceFee_16_7
      subMoveCursor 21, 66
      subEnterData strADDLInvstor_21_66
  subPressKey "@E" ' takes you to the second screen
      subMoveCursor 11, 76
      subEnterData strRemitCTRL_11_76
  subPressKey "@E"  'Saves the data
  End Sub
  </code></pre>