获取"href = link"从html页面并使用vba导航到该链接 [英] Get "href=link" from html page and navigate to that link using vba
问题描述
我正在用Excel VBA编写代码，以获取类的 href 值并导航至该href链接(即)这是我想进入特定Excel工作表的 href 值，并且我想通过我的VBA代码自动导航到该链接.
< a href ="/questions/51509457/how-to-make-the-word-invisible-when-its-checked-without-js" class ="question-hyperlink">如何不使用js</a>进行检查时，使该单词不可见 我得到的结果是我能够得到包含标签的类值的代码.如何在不使用js进行检查的情况下使单词不可见< ----这是标题我在工作表中得到的东西.我想要得到的是这个标题持有一个 href 链接/questions/51509457/how-to-make-the-word-invisible-when-its-checked-without-js 这就是我想要获取并浏览我的代码的地方.
请帮帮我.预先感谢
下面是整个编码:
Sub useClassnames()Dim元素作为IHTMLElement昏暗的元素作为IHTMLElementCollection昏暗即作为InternetExplorer昏暗的html作为HTMLDocument'在内存中打开Internet Explorer，然后转到网站设置ie = New InternetExplorerie.Visible = Trueie.navigate"https://stackoverflow.com/questions"'等到IE加载网页在While.readyState<>中执行READYSTATE_COMPLETEDoEvents环形设置html = ie.document设置元素= html.getElementsByClassName("question-hyperlink")暗淡计数昏昏欲睡计数= 0对于元素中的每个元素如果element.className ="question-hyperlink"然后erow = Sheets("Exec").Cells(Rows.count，1).End(xlUp).Offset(1，0).RowSheets("Exec").Cells(erow，1)= html.getElementsByClassName("question-hyperlink")(count).innerText计数=计数+ 1万一下一个元素范围("H10").选择结束子 我在这个网站上找不到任何人问的任何答案.请不要重复提出这个问题.
< div class ="row hoverSensitive">< div class ="column summary-column summary-column-icon-compact">< img src ="images/app/run32.png" alt =" width ="32" height ="32"></div>< div class ="column summary-column">< div class ="summary-title summary-title-compact text-ppp">< a href ="** index.php?/runs/view/7552 **"> MMDA</a></div>< div class ="summary-description-compact text-secondary text-ppp">在2018年7月9日之前</div></div>< div class ="column summary-column summary-column-bar">< div class ="table">< div class ="column">< div class ="chart-bar">< div class ="chart-bar-custom link-tooltip" tooltip-position ="left" style ="background:#4dba0f; width:125px" tooltip-text ="100％Passed(11/11 tests)">;</div></div></div>< div class ="column chart-bar-percent chart-bar-percent-compact">100％'
方法①
使用XHR通过问题首页URL发出初始请求；应用CSS选择器检索链接，然后将那些链接传递给IE导航到
用于选择元素的CSS选择器:
您需要元素的 href 属性.已经给出了一个示例.您可以使用getAttribute，或者如@Santosh所指出的，将 href 属性CSS选择器与其他CSS选择器结合起来以定位元素.
CSS选择器:
a.question-hyperlink [href] 寻找具有父代 a 标记的元素，该标记具有 question-hyperlink 类和一个 href 属性.
然后将CSS选择器组合与 document 的 querySelectorAll 方法一起应用，以收集链接的nodeList.
XHR以获取链接的初始列表:
我会以更快的速度将其作为XHR首先发布，并将您的链接收集到一个collection/nodeList中，然后您可以在IE浏览器中循环它.
选项显式公共子GetLinks()Dim sResponse作为字符串，HTML作为新的HTMLDocument，linkList作为对象，iconst BASE_URL As String ="https://stackoverflow.com"使用CreateObject("MSXML2.XMLHTTP").打开"GET"，"https://stackoverflow.com/questions"，False.发送sResponse = StrConv(.responseBody，vbUnicode)结束于sResponse = Mid $(sResponse，InStr(1，sResponse，<！DOCTYPE"))使用HTML.body.innerHTML = sResponse设置linkList = .querySelectorAll("a.question-hyperlink [href]")对于i = 0到linkList.Length-1Debug.Print Replace $(linkList.item(i)，关于:"，BASE_URL)接下来我结束于'使用IE和linkList的代码结束子 在上面的 linkList 中是一个nodeList，其中包含主页中所有匹配的元素，即问题登录页面上的所有 href .您可以循环遍历 nodeList 的 .Length ，并对其进行索引以检索特定的 href ，例如linkList.item(i).由于返回的链接是相对的，因此您需要使用协议+域(即"https://stackoverflow.com" .)替换路径的相对 about:部分./p>
现在您已经快速获得该列表，并且可以访问项目，您可以将任何给定的更新的 href 传递到 IE.Navigate .
使用IE和nodeList导航问题
对于i = 0到linkList.Length-1IE.Navigate Replace $(linkList.item(i).getAttribute("href")，"about:"，BASE_URL)接下来我 方法②
使用XHR通过GET请求发出初始请求并搜索问题标题；应用CSS选择器检索链接，然后将那些链接传递到IE进行导航.
选项显式公共子GetLinks()Dim sResponse作为字符串，HTML作为新的HTMLDocument，linkList作为对象，iconst BASE_URL As String ="https://stackoverflow.com"Const TARGET_QUESTION As String =如何在不使用js进行检查的情况下使该单词不可见"使用CreateObject("MSXML2.XMLHTTP").打开获取"，"https://stackoverflow.com/search?q=&URLEncode(TARGET_QUESTION)，否.发送sResponse = StrConv(.responseBody，vbUnicode)结束于sResponse = Mid $(sResponse，InStr(1，sResponse，<！DOCTYPE"))使用HTML.body.innerHTML = sResponse设置linkList = .querySelectorAll("a.question-hyperlink [href]")对于i = 0到linkList.Length-1Debug.Print Replace $(linkList.item(i).getAttribute("href")，"about:"，BASE_URL)接下来我结束于如果linkList无效，则退出Sub'使用IE和linkList的代码结束子'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba @Tomalak公共功能URLEncode(_StringVal作为字符串，_可选的SpaceAsPlus为Boolean = False _)作为字符串昏暗的StringLen长度:StringLen = Len(StringVal)如果StringLen>0然后ReDim结果(StringLen)作为字符串尽可能长，将CharCode保持为整数昏暗的字符作为字符串，空格作为字符串如果SpaceAsPlus则Space ="+"其他Space =％20"对于i = 1到StringLen字符= Mid $(StringVal，i，1)字符代码= Asc(字符)选择大小写CharCode案例97至122、65至90、48至57、45、46、95、126result(i)=字符案例32result(i)=空间病例0至15result(i)=％0"&十六进制(CharCode)其他情况result(i)=％"&十六进制(CharCode)结束选择接下来我URLEncode = Join(结果，")万一结束功能 I am writing a code in Excel VBA to get href value of a class and navigate to that href link
(i.e) here is the href value I want to get into my particular Excel sheet and I want to navigate to that link automatically through my VBA code.
<a href="/questions/51509457/how-to-make-the-word-invisible-when-its-checked-without-js" class="question-hyperlink">How to make the word invisible when it's checked without js</a>
The result I'm getting is that I'm able to get that containing tag's class value How to make the word invisible when it's checked without js <---- this is title is what I am getting in my sheet. What I want to get is this title's holding a href link /questions/51509457/how-to-make-the-word-invisible-when-its-checked-without-js this is what I want to get and navigate through my code.
Please help me out. Thanks in advance
Below are the entire coding:
Sub useClassnames()
Dim element As IHTMLElement
Dim elements As IHTMLElementCollection
Dim ie As InternetExplorer
Dim html As HTMLDocument
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
ie.Visible = True
ie.navigate "https://stackoverflow.com/questions"
'Wait until IE has loaded the web page
Do While ie.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Set html = ie.document
Set elements = html.getElementsByClassName("question-hyperlink")
Dim count As Long
Dim erow As Long
count = 0
For Each element In elements
If element.className = "question-hyperlink" Then
erow = Sheets("Exec").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Sheets("Exec").Cells(erow, 1) = html.getElementsByClassName("question-hyperlink")(count).innerText
count = count + 1
End If
Next element
Range("H10").Select
End Sub
I cant find any answer in this website asked by anyone. Please don't suggest this question as duplicate.
<div class="row hoverSensitive">
<div class="column summary-column summary-column-icon-compact ">
<img src="images/app/run32.png" alt="" width="32" height="32">
</div>
<div class="column summary-column ">
<div class="summary-title summary-title-compact text-ppp">
<a href="**index.php?/runs/view/7552**">MMDA</a>
</div>
<div class="summary-description-compact text-secondary text-ppp">
By on 7/9/2018 </div>
</div>
<div class="column summary-column summary-column-bar ">
<div class="table">
<div class="column">
<div class="chart-bar ">
<div class="chart-bar-custom link-tooltip" tooltip-position="left" style="background: #4dba0f; width: 125px" tooltip-text="100% Passed (11/11 tests)"></div>
</div>
</div>
<div class="column chart-bar-percent chart-bar-percent-compact">
100%'
Method ①
Use XHR to make initial request using question homepage URL; apply CSS selector to retrieve links and then pass those links to IE to navigate to
CSS selectors to select elements:
You want the href attribute of the element.You have been given an example already. You can use getAttribute, or, as pointed out by @Santosh, combine an href attribute CSS selector with other CSS selectors to target the elements.
CSS selector:
a.question-hyperlink[href]
Looks for elements with parent a tag having class of question-hyperlink and an href attribute.
You then apply the CSS selector combination with the querySelectorAll method of document to gather a nodeList of the links.
XHR to get initial list of links:
I would issue this first as an XHR, as much faster, and gather your links into a collection/nodeList you can later loop with your IE browser.
Option Explicit
Public Sub GetLinks()
Dim sResponse As String, HTML As New HTMLDocument, linkList As Object, i As Long
Const BASE_URL As String = "https://stackoverflow.com"
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "https://stackoverflow.com/questions", False
.send
sResponse = StrConv(.responseBody, vbUnicode)
End With
sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
With HTML
.body.innerHTML = sResponse
Set linkList = .querySelectorAll("a.question-hyperlink[href]")
For i = 0 To linkList.Length - 1
Debug.Print Replace$(linkList.item(i), "about:", BASE_URL)
Next i
End With
'Code using IE and linkList
End Sub
In the above linkList is a nodeList holding all the matched elements from the homepage i.e. all the hrefs on the question landing page. You can loop the .Length of the nodeList and index into it to retrieve a particular href e.g. linkList.item(i). As the link returned is relative, you need to replace the relative about: part of the path with the protocol + domain i.e. "https://stackoverflow.com".
Now that you have quickly obtained that list, and can access items, you can pass any given updated href onto IE.Navigate.
Navigating to questions using IE and nodeList
For i = 0 To linkList.Length - 1
IE.Navigate Replace$(linkList.item(i).getAttribute("href"), "about:", BASE_URL)
Next i
Method ②
Use XHR to make initial request using GET request and searched for question title; apply CSS selector to retrieve links and then pass those links to IE to navigate to.
Option Explicit
Public Sub GetLinks()
Dim sResponse As String, HTML As New HTMLDocument, linkList As Object, i As Long
Const BASE_URL As String = "https://stackoverflow.com"
Const TARGET_QUESTION As String = "How to make the word invisible when it's checked without js"
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "https://stackoverflow.com/search?q=" & URLEncode(TARGET_QUESTION), False
.send
sResponse = StrConv(.responseBody, vbUnicode)
End With
sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
With HTML
.body.innerHTML = sResponse
Set linkList = .querySelectorAll("a.question-hyperlink[href]")
For i = 0 To linkList.Length - 1
Debug.Print Replace$(linkList.item(i).getAttribute("href"), "about:", BASE_URL)
Next i
End With
If linkList Is Nothing Then Exit Sub
'Code using IE and linkList
End Sub
'https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba @Tomalak
Public Function URLEncode( _
StringVal As String, _
Optional SpaceAsPlus As Boolean = False _
) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
这篇关于获取"href = link"从html页面并使用vba导航到该链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
