- 首页
- 其他开发
- 从 url 获取附近位置的 VBA 代码到 excel
从 url 获取附近位置的 VBA 代码到 excel
[英] VBA code to get Near By location from url to excel
本文介绍了从 url 获取附近位置的 VBA 代码到 excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试从 url 获取连接、基础设施和邻里信息到下面的 excel 是我尝试过的 vba 代码，但我收到错误 - 运行时错误91":对象变量或块变量未设置.
子测试()将 oHtml 变暗为 HTMLDocumentDim oElement 作为对象设置 oHtml = 新建 HTMLDocument使用 CreateObject("WINHTTP.WinHTTPRequest.5.1").开 GET"， https://www.99acres.com/dasnac-the-jewel-of-noida-sector-75-noida-npxid-r14046?src=NPSRP&sid=UiB8IFFTIHwgUyB8IzEjICB8IG5vaWRhIzUjIHwgQ1AyNSB8IFkgIzE4I3wgIHwgMjUgfCMzIyAgfCA3ICM1I3wgIHwgMjMgfCM0MyMgIHw=".发送oHtml.body.innerHTML = .responseText结束于ele3 = oHtml.getElementById("locHighlightsSlide")范围(A1")= ele3.innerText结束子
下面是我的html代码
<div class="amnLeftScroll noVis AccessoriesScrollArrow dev_leftlocHightlights"><i class="xid_icons xid_icons_1x xid_leftarrow1x"></i>
<div class="vsp-container"><div class="vsp-itemsFilm clearAfter animTransMrg350"><div class="vsp-item highlightCard"><div class="highlightsCardHeading">连接性</div><div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_metro"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Wave City Centr...</span><span class="overflowItem" style="top:auto;z-index: 1">Wave City Centre</span>
<div class="highlightsCardCol">4 公里 |20分钟/格
<div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_metro"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">高尔夫球场站...</span><span class="overflowItem" style="top:auto;z-index: 1">高尔夫球场站</span>
<div class="highlightsCardCol">5 公里 |25分钟/格
<div class="vsp-item highlightCard" id="viewMore_Connectivity" style="display: none;"><div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_metro"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Wave City Centr...</span><span class="overflowItem" style="top:auto;z-index: 1">Wave City Centre</span>
<div class="highlightsCardCol">4 公里 |20分钟/格
<div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_metro"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">高尔夫球场站...</span><span class="overflowItem" style="top:auto;z-index: 1">高尔夫球场站</span>
<div class="highlightsCardCol">5 公里 |25分钟/格
<div class="vsp-item highlightCard"><div class="highlightsCardHeading">基础设施</div><div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_education"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Manav Rachna In...</span><span class="overflowItem" style="top:auto;z-index: 1">Manav Rachna 国际学校</span>
<div class="highlightsCardCol">2 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_education"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">全球印度人...</span><span class="overflowItem" style="top:auto;z-index: 1">全球印度国际学校</span>
<div class="highlightsCardCol">2 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_health"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">新医院</span>
3 公里 |15分钟/格
<div class="vsp-item highlightCard" id="viewMore_Infrastructure" style="display: none;"><div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_education"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Manav Rachna In...</span><span class="overflowItem" style="top:auto;z-index: 1">Manav Rachna 国际学校</span>
<div class="highlightsCardCol">2 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_education"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">全球印度人...</span><span class="overflowItem" style="top:auto;z-index: 1">全球印度国际学校</span>
<div class="highlightsCardCol">2 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_health"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">新医院</span>
3 公里 |15分钟/格
<div class="vsp-item highlightCard"><div class="highlightsCardHeading">邻里</div><div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-72</span>
3 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-76</span>
3 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-51</span>
3 公里 |15分钟/格
<div class="vsp-item highlightCard" id="viewMore_Neighborhood" style="display: none;"><div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-72</span>
3 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-76</span>
3 公里 |15分钟/格
<div class="highlightsCardRow jsinteraction rel" style="overflow:visible;"><div class="highlightsCardCol"><i class="xid_icons xid_icons_1x xid_loc_village"></i>
<div class="highlightsCardCol jsinteraction" style="overflow:visible;"><span class="highlightPoint">Sector-51</span>
3 公里 |15分钟/格
<div class="amnRightScroll 设施ScrollArrow dev_rightLocHighlights"><i class="xid_icons xid_icons_1x xid_rightarrow1x dev_vsp-Next"></i>
请找到下面的图片，其中显示了我需要的数据.
解决方案
您可以使用 CSS 选择器，它们是用于选择元素的模式.对于您的 HTML 示例模式，有效的有:
- 第一列:
.vsp-item.highlightsCard:first-child - 第二列:
.vsp-item.highlightsCard:nth-child(2) - 第三列:
.vsp-item.highlightsCard:last-child - 标题:
.highlightsCardHeading
. ，在上述选择器中，是
第 2 列基础设施:
第 3 列邻域:
标题:
观察:上面的查询结果表明Columns 1-3需要一个节点，即.querySelector；请注意， 0 是返回的唯一匹配项.而 .querySelectorAll 将被用于 Headers，返回一个 nodeList，我们可以看到 0-2 返回.
<小时>VBA:
我无法访问网站进行查询，但 .querySelector 方法属于 HTMLDocument，并且通常是(使用您的 HTMLDocument 变量 oHTML):
对于单个节点:
oHtml.querySelector(".vsp-item.highlightsCard:first-child")
对于节点列表:
oHtml.querySelectorAll(".highlightsCardHeading")
如果是一个 nodeList，您可以使用以下命令迭代它的项:
Dim headerNodeList As Object, i As Long设置 headerNodeList = oHtml.querySelectorAll(".highlightsCardHeading")对于 i = 0 到 headerNodeList.Length - 1Debug.Print headerNodeList.item(i).innerText '<==this way ''Debug.Print headerNodeList(i).innerText '<==或者这个方法接下来我
对于单个元素使用 .querySelector 和 .innerText:
Dim col1Node As Object: Set col1Node = oHtml.querySelector(".vsp-item.highlightsCard:first-child"): Debug.Print col1Node.innerTextDim col2Node As Object: Set col2Node = oHtml.querySelector(".vsp-item.highlightsCard:nth-child(2)"): Debug.Print col2Node.innerTextDim col3Node As Object: Set col3Node = oHtml.querySelector(".vsp-item.highlightsCard:last-child") : Debug.Print col3Node.innerText
<小时>
注意事项:
我无法从您的请求中检查 .responseText (或者更确切地说，由于与站点的连接失败，它没有意义).可能此方法不会返回所需的信息，特别是对于动态加载的页面.不过，我相信 WinHTTPRequest.5.1 有一个你可以使用的响应状态.
在这种情况下，您可能需要使用其他方法(例如基于 IE 浏览器的方法)并等待页面加载.您仍然可以使用 HTMLDocument 的 .querySelector 或 .querySelectorAll 方法.
<小时>示例脚本:
IE 的方法在这里:https://stackoverflow.com/a/50613063/6241235WinHTTP 的方法:https://stackoverflow.com/a/50759945/6241235 或 https://stackoverflow.com/a/50757822/6241235
<小时>
信息:
- HTMLDOM querySelector() 方法
- CSS 选择器
I am trying to get Connectivity,Infrastructure and Neighborhood information from url to excel below is the vba code which i have tried but i am getting error-Run Time Error '91': Object variable or with block variable not set.
Sub test()
Dim oHtml As HTMLDocument
Dim oElement As Object
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
.Open "GET", "https://www.99acres.com/dasnac-the-jewel-of-noida-sector-75-noida-npxid-r14046?src=NPSRP&sid=UiB8IFFTIHwgUyB8IzEjICB8IG5vaWRhIzUjIHwgQ1AyNSB8IFkgIzE4I3wgIHwgMjUgfCMzIyAgfCA3ICM1I3wgIHwgMjMgfCM0MyMgIHw="
.Send
oHtml.body.innerHTML = .responseText
End With
ele3 = oHtml.getElementById("locHighlightsSlide")
Range("A1") = ele3.innerText
End Sub
Below is my html code
<div class="vsp-SliderBar" id="locHighlightsSlide">
<div class="amnLeftScroll noVis amenitiesScrollArrow dev_leftlocHightlights">
<i class="xid_icons xid_icons_1x xid_leftarrow1x"></i>
</div>
<div class="vsp-container">
<div class="vsp-itemsFilm clearAfter animTransMrg350">
<div class="vsp-item highlightsCard">
<div class="highlightsCardHeading">Connectivity</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_metro"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Wave City Centr...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Wave City Centre</span> </div>
<div class="highlightsCardCol">4 km | 20 min</div>
</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_metro"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Golf Course Sta...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Golf Course Station</span> </div>
<div class="highlightsCardCol">5 km | 25 min</div>
</div>
</div>
<div class="vsp-item highlightsCard" id="viewMore_Connectivity" style="display: none;">
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_metro"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Wave City Centr...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Wave City Centre</span> </div>
<div class="highlightsCardCol">4 km | 20 min</div>
</div>
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_metro"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Golf Course Sta...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Golf Course Station</span> </div>
<div class="highlightsCardCol">5 km | 25 min</div>
</div>
</div>
<div class="vsp-item highlightsCard">
<div class="highlightsCardHeading">Infrastructure</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_education"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Manav Rachna In...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Manav Rachna International School</span> </div>
<div class="highlightsCardCol">2 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_education"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Global Indian I...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Global Indian International School</span> </div>
<div class="highlightsCardCol">2 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_health"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Neo Hospital</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
</div>
<div class="vsp-item highlightsCard" id="viewMore_Infrastructure" style="display: none;">
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_education"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Manav Rachna In...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Manav Rachna International School</span> </div>
<div class="highlightsCardCol">2 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_education"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Global Indian I...</span>
<span class="overflowItem" style="top:auto;z-index: 1">Global Indian International School</span> </div>
<div class="highlightsCardCol">2 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_health"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Neo Hospital</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
</div>
<div class="vsp-item highlightsCard">
<div class="highlightsCardHeading">Neighborhood</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-72</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-76</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-51</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
</div>
<div class="vsp-item highlightsCard" id="viewMore_Neighborhood" style="display: none;">
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-72</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-76</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
<div class="highlightsCardRow jsinteraction rel" style="overflow: visible;">
<div class="highlightsCardCol">
<i class="xid_icons xid_icons_1x xid_loc_village"></i>
</div>
<div class="highlightsCardCol jsinteraction" style="overflow: visible;">
<span class="highlightPoint">Sector-51</span>
</div>
<div class="highlightsCardCol">3 km | 15 min</div>
</div>
</div>
</div>
</div>
<div class="amnRightScroll amenitiesScrollArrow dev_rightLocHighlights">
<i class="xid_icons xid_icons_1x xid_rightarrow1x dev_vsp-Next"></i>
</div>
</div>
Please find the below pic which shows the data which i require.
解决方案
You can use CSS selectors which are patterns used to select element(s). For your HTML example patterns that work are:
- First column with:
.vsp-item.highlightsCard:first-child
- Second column with:
.vsp-item.highlightsCard:nth-child(2)
- Third column with:
.vsp-item.highlightsCard:last-child
- Headers with:
.highlightsCardHeading
The . , in the above selectors, is the class selector i.e. selecting elements by className.
CSS query results:
Column 1 Connectivity:
Column 2 Infrastructure:
Column 3 Neighbourhood:
Headers:
Observations: The query results above indicate that a single node i.e. .querySelector is required for Columns 1-3; note that 0 is the only match returned. Whereas, .querySelectorAll will be required for Headers, returning a nodeList, as we can see 0-2 returned.
VBA:
I cannot access the website to query but the .querySelector method belongs to the HTMLDocument and is generally (using your HTMLDocument variable oHTML):
For a single node:
oHtml.querySelector(".vsp-item.highlightsCard:first-child")
For a nodeList:
oHtml.querySelectorAll(".highlightsCardHeading")
If a nodeList, you iterate over its items with:
Dim headerNodeList As Object, i As Long
Set headerNodeList = oHtml.querySelectorAll(".highlightsCardHeading")
For i = 0 To headerNodeList.Length - 1
Debug.Print headerNodeList.item(i).innerText '<==this way '
'Debug.Print headerNodeList(i).innerText '<==Or this method
Next i
For a single element use .querySelector and .innerText:
Dim col1Node As Object: Set col1Node = oHtml.querySelector(".vsp-item.highlightsCard:first-child"): Debug.Print col1Node.innerText
Dim col2Node As Object: Set col2Node = oHtml.querySelector(".vsp-item.highlightsCard:nth-child(2)"): Debug.Print col2Node.innerText
Dim col3Node As Object: Set col3Node = oHtml.querySelector(".vsp-item.highlightsCard:last-child") : Debug.Print col3Node.innerText
Notes:
I cannot inspect the .responseText from your request (Or rather, it is not meaningful due to connection to site failing). It may be that this method will not return the required information, particularly with dynamically loaded pages. Though, I believe WinHTTPRequest.5.1 has a response status you could use.
In that case you may need to use another method such as IE browser based and wait for page to load. You would still use .querySelector or .querySelectorAll method of HTMLDocument.
Example scripts:
- Method for
IE here: https://stackoverflow.com/a/50613063/6241235
- Method for
WinHTTP: https://stackoverflow.com/a/50759945/6241235 OR https://stackoverflow.com/a/50757822/6241235
Info:
- HTMLDOM querySelector() method
- CSS Selectors
这篇关于从 url 获取附近位置的 VBA 代码到 excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
