VBA - Extract HTML data from IE into a cell value

[Chexss]

Hello,

I'm currently trying to get a specific element ID that changes when the array re-orders itself depending on the page. I'm trying to search for the hidden value and then grab that hidden ID or Name (either works). Any help / guidance you can give would be much appreciated! I just need to be able to reference the value to pull the ID or Name and then I'll be good to go. Thanks again!

The HTML I'm working with:

<div class="row">
<div class="col-md-5">
<input type="hidden" name="MyNumber[25].ID" id="MyNumber[25].ID" value="60">

Here's some of the stuff that has got me close but has not completely worked:

Sub TextToRight()
Dim ie As InternetExplorer
Dim txt
Dim ToFind As String
Set ie = New InternetExplorerMedium
ie.navigate "https://NonPublicSite.com
' Make IE browser visible (False would allow IE to run in the background)
ie.Visible = True
'####Loop until fully loaded####
Do Until ie.readyState = READYSTATE_COMPLETE
Loop

####This one worked but its the opposite of what I need. I need to search for the Attribute with the value of "60" and return the Element. This one however searched for the ID and returned the value.
test = ie.document.getElementById("MyNumber[25].ID").getAttribute("value")
Worksheets("sheet1").Range("A2").Value = test

####Failed
test = ie.document.getAttribute("60").getAttribute("value")
Worksheets("sheet1").Range("A2").Value = test

####Failed
test = ie.document.getAttribute("60").getElementById()
Worksheets("sheet1").Range("A2").Value = test

ie.Quit
Set ie = Nothing
End Sub

 

[John_w]

<input type="hidden" name="MyNumber[25].ID" id="MyNumber[25].ID" value="60">

Are you saying that the MyNumber part doesn't change, but the [25] part does change?

Try something like this (requires reference to Microsoft HTML Object Library).

    Dim HTMLdoc As HTMLDocument
    Dim elems As IHTMLDOMChildrenCollection
    Dim inputElem As HTMLInputElement
    
    Set HTMLdoc = IE.document
    'Find all elements with id starting with MyNumber
    Set elems = HTMLdoc.querySelectorAll("[id^='MyNumber']")
    Debug.Print elems.Length
    Debug.Print "1st: ", elems(0).id, elems(0).Name, elems(0).Value
    For Each inputElem In elems
        Debug.Print inputElem.id, inputElem.Name, inputElem.Value
    Next

 

[Chexss]

<input type="hidden" name="MyNumber[25].ID" id="MyNumber[25].ID" value="60">

Are you saying that the MyNumber part doesn't change, but the [25] part does change?

Try something like this (requires reference to Microsoft HTML Object Library).

    Dim HTMLdoc As HTMLDocument
    Dim elems As IHTMLDOMChildrenCollection
    Dim inputElem As HTMLInputElement
   
    Set HTMLdoc = IE.document
    'Find all elements with id starting with MyNumber
    Set elems = HTMLdoc.querySelectorAll("[id^='MyNumber']")
    Debug.Print elems.Length
    Debug.Print "1st: ", elems(0).id, elems(0).Name, elems(0).Value
    For Each inputElem In elems
        Debug.Print inputElem.id, inputElem.Name, inputElem.Value
    Next

Sorry for not being clear enough. The number in the brackets does change. The value there "60" does not. What I'm trying to do is search for where value="60" and then return the name or id on that same line. In this case I would search for 60 and it would return MyNumber[25].ID. The 60 is always the same but the 25 is not. Does that make sense?

 

[Chexss]

Also I did give this a try but I didn't get anything popping up. with the print. Also I tried to search for "60" but that didn't work. The numbers are unique in here and are the only way I believe for me to accurately grab the correct ID every time since the ID changes.

Dim HTMLdoc As HTMLDocument
Dim elems As IHTMLDOMChildrenCollection
Dim inputElem As HTMLInputElement

Set HTMLdoc = IE.document
'Find all elements with id starting with MyNumber
Set elems = HTMLdoc.querySelectorAll("[id^='MyNumber']")
Debug.Print elems.Length
Debug.Print "1st: ", elems(0).id, elems(0).Name, elems(0).Value
For Each inputElem In elems
Debug.Print inputElem.id, inputElem.Name, inputElem.Value
Next

 

[John_w]

This should find the first input element with value="60".

    Set inputElem = HTMLdoc.querySelector("input[value='60']")
    If Not inputElem Is Nothing Then
        Debug.Print inputElem.outerHTML
        Debug.Print inputElem.id, inputElem.Name
    Else
        Debug.Print "Not found"
    End If

 

[Chexss]

This should find the first input element with value="60".

    Set inputElem = HTMLdoc.querySelector("input[value='60']")
    If Not inputElem Is Nothing Then
        Debug.Print inputElem.outerHTML
        Debug.Print inputElem.id, inputElem.Name
    Else
        Debug.Print "Not found"
    End If

Apologies I'm a bit new to this and am having trouble figuring out why this one isn't working. The code I have below gives me an error 91 if I i use HTMLdoc.queryselector and if I use HTMLDocument.queryselector I get an error of 424.

It does work if I replace
"Set inputElem = HTMLdoc.querySelector("input[value='60']")" with "Set inputElem = ie.document.querySelector("input[value='60']")". If I use ie.document instead of html.document then I get no error and the value that gets returned to me in cell J2 is "[object HTMLInputElement]". I'm not sure what to do with that and either way for some reason Debug doesn't show me anything. Thanks again for any help you may provide!

Sub test()

Dim ie As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim inputElem As HTMLInputElement

Set ie = New InternetExplorerMedium
ie.navigate "MySite.com"
' Make IE browser visible (False would allow IE to run in the background)
ie.Visible = True

'####Loop until fully loaded####
Do Until ie.readyState = READYSTATE_COMPLETE
Loop

Set inputElem = HTMLdoc.querySelector("input[value='60']")
If Not inputElem Is Nothing Then
Debug.Print inputElem.outerHTML
Debug.Print inputElem.ID, inputElem.Name
Worksheets("sheet1").Range("J2").Value = inputElem

Else

Debug.Print "Not found"

End If

End Sub

 

[John_w]

You're missing Set HTMLdoc = IE.document after the ready wait loop.

 

[Chexss]

You're missing Set HTMLdoc = IE.document after the ready wait loop.

I have been trying different things and appear to have left that out. When I try the below code I get a 424 error.

Sub test()

Dim ie As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim inputElem As HTMLInputElement

Set ie = New InternetExplorerMedium
ie.navigate "MySite.com"
' Make IE browser visible (False would allow IE to run in the background)
ie.Visible = True

'####Loop until fully loaded####
Do Until ie.readyState = READYSTATE_COMPLETE
Loop

Set HTMLdoc = IE.document
Set inputElem = HTMLdoc.querySelector("input[value='60']")
If Not inputElem Is Nothing Then
Debug.Print inputElem.outerHTML
Debug.Print inputElem.ID, inputElem.Name
Worksheets("sheet1").Range("J2").Value = inputElem

Else

Debug.Print "Not found"

End If

End Sub

When I try this code, I get no error but the value that is returned is "[object HTMLInputElement]". Do I perhaps need to convert this to a string or?

Sub test()

Dim ie As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim inputElem As HTMLInputElement

Set ie = New InternetExplorerMedium
ie.navigate "MySite.com"
' Make IE browser visible (False would allow IE to run in the background)
ie.Visible = True

'####Loop until fully loaded####
Do Until ie.readyState = READYSTATE_COMPLETE
Loop

Set HTMLdoc = IE.document
Set inputElem = ie.document.querySelector("input[value='60']")
If Not inputElem Is Nothing Then
Debug.Print inputElem.outerHTML
Debug.Print inputElem.ID, inputElem.Name
Worksheets("sheet1").Range("J2").Value = inputElem

Else

Debug.Print "Not found"

End If

End Sub

 

[John_w]

When I try this code, I get no error but the value that is returned is "[object HTMLInputElement]".

Because you're specifying the whole inputElem object, and VBA outputs the object type string.

You want a property of the object, i.e. inputElem.id, as I showed in the Debug.Print line:

Worksheets("sheet1").Range("J2").Value = inputElem.id

 

[Chexss]

Because you're specifying the whole inputElem object, and VBA outputs the object type string.

You want a property of the object, i.e. inputElem.id, as I showed in the Debug.Print line:

Worksheets("sheet1").Range("J2").Value = inputElem.id

Thank you so much for the help! Definitely learned a lot...and have much to learn lol. Again thanks so much!