VBA - Extract HTML data from IE into a cell value

Chexss

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997
HTML:
<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).

VBA Code:
    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

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
HTML:
<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).

VBA Code:
    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

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997

ADVERTISEMENT

This should find the first input element with value="60".
VBA Code:
    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

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
This should find the first input element with value="60".
VBA Code:
    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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997

ADVERTISEMENT

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

Chexss

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997
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:
VBA Code:
Worksheets("sheet1").Range("J2").Value = inputElem.id
 
Solution

Chexss

New Member
Joined
Apr 29, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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!
 

Forum statistics

Threads
1,141,062
Messages
5,704,044
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top