Excel VBA getElementsByTagName() 只返回最后一个输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21838276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA getElementsByTagName() only returning the last input
提问by Jacob Lambert
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:
我的公司需要为超过 1000 人重置用户名和密码，而不是手动操作，我希望让 VBA 为我自动化。我让它很好地连接到 IE 窗口，但 getElementsByTagName("input") 只返回最后一个输入标签。下面是我的代码：
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.
先感谢您。
回答by Tim Williams
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.
不过不太确定：您似乎从问题中省略了一些代码。
回答by Pedrumj
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=xand places it in Cell A1:
另一种方法是使用查询从网页中检索数据。例如，下面的代码从站点http://finance.yahoo.com/q?s=usdCAd=x检索数据并将其放入单元格 A1：
With Sheet1.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", Destination:=Sheet1.Range("$A"))
.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.
您最终将获得从网站检索到的大量行和一些字符串列。然后，您可以处理信息并检索所需的数据。
