vbaweb-scrapingmsxml
VBA code to get Near By location from url to excel
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 SubBelow 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.
Solution
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
IEhere: https://stackoverflow.com/a/50613063/6241235 - Method for
WinHTTP: https://stackoverflow.com/a/50759945/6241235 OR https://stackoverflow.com/a/50757822/6241235
Info:
- How to save Image independently in VBA module
- Application.FileDialog vs GetOpenFilename: What is the difference?
- Convert table to reflect column names as row values
- Trying to hide rows in excel based on two values
- Combine every unique value from column A with duplicated values into a new sheet
- Import formula from variable - Excel VBA
- VBA Macro to detect unique entries and copy them from one workbook to another is copying a small fraction of duplicates along with the uniques
- Copy paste cells from button row
- Set two color gradient in PowerPoint cell table
- Find/Replace Row number within Formula
- How to have instructional text in Excel?
- How to copy a range to several worksheets by using a list of worksheets to copy to
- VBA - exclude certain boxes from a general check
- How to count resolved comments only via VBA in Excel
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- How to copy only plain text of cells in Excel?
- VBA - Capitalise range of cells before the last period
- Excel VBA macro to group rows based on condition
- VBA: ListBox Change event firing twice
- Excel VBA offset without activating the cell
- Access 2010 Forms VBA - Set Focus to Record on image click
- Excel's fullname property with OneDrive
- How to I call/trigger a ribbon control sub from a regular sub?
- If certain columns are empty within a range, delete entire row (Excel macro)
- Merging columns by looping
- Using VBA to copy and paste values into new workbooks but it's hardcoding original workbook at end of process
- Copying data into a worksheet when the destination has hidden rows
- Application.OnTime TimeValue - Run Once only
- Is it possible disable the "Go to location" feature when co-authoring in excel?
- Is there a way to talk to OneNote through code?