Excel VBA getElementsByTagName() only returning th

Question

My company is needing to reset usernames and passwords for over 1000 people and instead of doing it emanually, i would like to have VBA automate it for me. I have it connect to the IE window fine but the getElementsByTagName("input") only returns the last input tag. Below is my code:

Dim shellWins As ShellWindows
Dim IE As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim objElement As Object
Dim objCollection As IHTMLElementCollection
Dim name As String
Dim val As String, val2 As String
Dim a

Set shellWins = New ShellWindows

If shellWins.Count > 0 Then
  ' Get IE
  Set IE = shellWins.Item(0)
Else
  ' Create IE
  Set IE = New InternetExplorer
  IE.Visible = True
End If

Set objCollection = IE.Document.getElementsByTagName("input")


For i = 0 To objCollection.Length
   name = objCollection(, i).name
   If Left(name, 6) = username Then
        val = objCollection(i).Value
        a = Split(val, "@")
        If Left(a(1), 1) <> "s" Then
            val2 = a(0) & sa & a(1)
        Else
            val2 = val
        End If
        objCollection(i).Value = val2
    ElseIf Left(name, 6) = pswd Then
        objCollection(i).Value = nPswd
    End If
Next

Set shellWins = Nothing
Set IE = Nothing

The input tags that i am wanting are in table tags, could that be causing it? If so, how would i reference the tags inside the table?

Thank you in advance.

Answer 1

A different approach would be using a query to retrieve the data from a webpage. for example the code below retrieves the data from the site http://finance.yahoo.com/q?s=usdCAd=x and places it in Cell A1:

With Sheet1.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=usdCAd=x", Destination:=Sheet1.Range("$A$1"))
    .Name = "q?s=usdCAd=x_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With 

You will end up with a large amount of rows and some columns of strings retrieved from the website. You can then process the information and retrieve the data you want.

Answer 2

Try this loop instead:

Dim el as object
Set objCollection = IE.document.getElementsByTagName("input")
For Each el In objCollection
   If Left(el.Name, 6) = UserName Then '?"username"?
        a = Split(el.Value, "@")
        If Left(a(1), 1) <> "s" Then
            val2 = a(0) & sa & a(1)
            el.Value = val2
        End If
    ElseIf Left(el.Name, 6) = pswd Then
        el.Value = nPswd
    End If
Next el

Not quite sure though: you seem to have omitted some code from your question.