Needing to Loop Data from Excel to Website

[Patty28715]

I have VBA that pulls datas from cell, inputs into field on website and submits. I then extract data back from website to excel sheet. Right now I'm using a specific cell.

Set HTMLInput = HTMLDoc.getElementById("ssn")
HTMLInput.Value = Sheets(1).Cells(2, 6)


Set HTMLInput = HTMLDoc.getElementById("fName")
HTMLInput.Value = Sheets(1).Cells(2, 4)


Set HTMLInput = HTMLDoc.getElementById("lName")
HTMLInput.Value = Sheets(1).Cells(2, 5)


IE.document.getElementsByName("submit")(0).Click

Adding to specific cell:

ThisWorkbook.Sheets(1).Cells(2, 7) = Mid(Name.innerText, InStr(Name.innerText, "") + 7, 50)
ThisWorkbook.Sheets(1).Cells(2, 8) = Mid(SSN.innerText, 33, 5)
ThisWorkbook.Sheets(1).Cells(2, 9) = Mid(License.innerText, InStr(License.innerText, "") + 30, 50)
ThisWorkbook.Sheets(1).Cells(2, 10) = Mid(Original_Date.innerText, InStr(Original_Date.innerText, "") + 21, 50)
ThisWorkbook.Sheets(1).Cells(2, 11) = Mid(Expiration_Date.innerText, InStr(Expiration_Date.innerText, "") + 26, 50)

I want to loop through the workbook, pull first line of data, extract into excel, go to next row in excel and repeat.

 

[tonyyy]

Patty28715,

You might consider the following...

Dim LastRow As Long, i As Long
LastRow = Sheets(1).Cells(Rows.Count, 6).End(xlUp).Row
For i = 2 To LastRow
    Set HTMLInput = HTMLDoc.getElementById("ssn")
    HTMLInput.Value = Sheets(1).Cells(i, 6)
    
    Set HTMLInput = HTMLDoc.getElementById("fName")
    HTMLInput.Value = Sheets(1).Cells(i, 4)
    
    Set HTMLInput = HTMLDoc.getElementById("lName")
    HTMLInput.Value = Sheets(1).Cells(i, 5)
    
    IE.document.getElementsByName("submit")(0).Click
    Do Until IE.readystate = 4
        DoEvents
    Loop
    
    'Adding to specific cell:
    ThisWorkbook.Sheets(1).Cells(i, 7) = Mid(Name.innerText, InStr(Name.innerText, "") + 7, 50)
    ThisWorkbook.Sheets(1).Cells(i, 8) = Mid(SSN.innerText, 33, 5)
    ThisWorkbook.Sheets(1).Cells(i, 9) = Mid(License.innerText, InStr(License.innerText, "") + 30, 50)
    ThisWorkbook.Sheets(1).Cells(i, 10) = Mid(Original_Date.innerText, InStr(Original_Date.innerText, "") + 21, 50)
    ThisWorkbook.Sheets(1).Cells(i, 11) = Mid(Expiration_Date.innerText, InStr(Expiration_Date.innerText, "") + 26, 50)
Next i

Please note the code is untested.

Cheers,

tonyyy

 

[Patty28715]

I figured it out. Thanks. Now I'm trying If statements