Get text from HTML table

[mdo8105]

I'm trying to obtain the inner text from an HTML table, but the elements are returning empty:
[HTML




















][/HTML]
Above is the address I am trying to grab.

This is the VBA code I am using, which it never errors out, but it states the element is empty:

Dim IE As InternetExplorerDim aEle As HTMLDivElement
Dim Result As String
Dim y As Integer
Dim objDivs, objDivs1 As IHTMLElementCollection
Dim objAnchors, objAnchors1 As IHTMLElementCollection
Dim intCounter As Integer
Dim myElement, myElement1 As Object
Dim WB As Workbook
Dim WS As Worksheet
Dim dashpos As Long
Dim Sex As Long
Dim TitleArray As Variant
Dim doc As HTMLDocument






Set WB = ThisWorkbook
Set WS = WB.Worksheets("NPI")


'Application.ScreenUpdating = False
'On Error GoTo Handler
lrow = WS.Cells(Rows.Count, 1).End(xlUp).Row
Set IE = CreateObject("InternetExplorer.Application")




WS.Activate


'make IE browser visible
IE.Visible = True


srow = WS.Cells(Rows.Count, 2).End(xlUp).Row
CLrow = WB.Worksheets("Category").Cells(Rows.Count, 4).End(xlUp).Row


TitleArray = WB.Worksheets("Category").Range("D2", "D" & CLrow)
On Error Resume Next
For r = srow To lrow
srow = WS.Cells(Rows.Count, 2).End(xlUp).Row
'navigate IE to this web page


IE.navigate "https://test.com" & WS.Cells(r + 1, 1).Value
Set doc = IE.document






















'wait here a few seconds whilte the browser is busy
Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop


 


  Set elems = doc.getElementsByClassName("table table-hover").getElementsByTagName("tr")
  For Each e In elems
      Debug.Print Trim(e.getElementsByTagName("td")(1).innerText)
   
  Next e

I would love some help on this if possible.

NPI	Name	NPI Type	Primary Practice Address	Phone	Primary Taxonomy
123456	TEST PROV		530 N STREET SAN DIEGO, CA 12345-0004

<thead>
</thead> <tbody>
</tbody>

 

[Domenic]

You should only assign your document to doc once the browser is ready. So you should navigate to your URL, wait until your browser is ready, and then assign the document accordingly...

IE.navigate "https://test.com" & WS.Cells(r + 1, 1).Value


'wait here a few seconds whilte the browser is busy
Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop


Set doc = IE.document

Sometimes you may need to pause the macro for a few more seconds before assigning the document to doc in order to allow the page to fully load.

Hope this helps!

 

[mdo8105]

Thank you so much, that was it. Now I am able to find the data. Now I'm running into the issue of grabbing the specific text element. Using the code below I am able to find the text element I want about the 7th time stepping through the code; however, if I change the 1 after getElementsByTagName("td")(1).innerText then it doesn't find anything:

Set elems = oHTML.getElementById("top").getElementsByTagName("tr")    
    For Each e In elems
    
        
            Debug.Print e.getElementsByTagName("td")(1).innerText
          
          
            
       
    Next e

 

[mdo8105]

So here my modified code:

Set Alllinks = oHTML.getElementsByClassName("lead")

For Each Hyperlink In Alllinks
    If InStr(1, Hyperlink.itemprop, "address", vbTextCompare) > 0 Then
        Debug.Print oHTML.getElementByClassName("lead")(3).innerText
        Exit For
    End If
Next

When I debug and look through the Locals window under my Alllinks Object, item 3 Variant/Object/HTMLBlockElement has the innertext I am looking for; however, we I debug print nothing is coming out.

 

[Domenic]

You have a typo, it should be plural...

Debug.Print oHTML.getElement[B]s[/B]ByClassName("lead")(3).innerText

Normally, you would have gotten an error, but the On Error Resume Next hides it.

 

[mdo8105]

Thank you so much for seeing that..