Get value from web document (intranet) input element with VBA

G

Gregor_1

New Member
Joined
Oct 12, 2023
Messages
11
Office Version
  1. 2021
Platform
  1. Windows
0
I'm having trouble getting the value from an input field called "due date"
Here is my HTML and VBA code.
I resolved error 462, but I can't retrieve certain data.
Actually I'm getting an error: Run-time error '91: Object variable or With block variable not set
Error appears in any code starting with TerminPlatnosc

VBA:
[KOD=vba]
Sub Dane_z_www()

Range("A1").Clear

Dim appIE As InternetExplorerMedium
Dim my_url As String
Dim TerminPlatnosci As String
Dim shellWins As SHDocVw.ShellWindows
Dim IE As SHDocVw.InternetExplorer
Dim Doc As HTMLDocument

my_url = "Example Domain"
Set appIE = New InternetExplorerMedium

With appIE
.Visible = True
.Navigate my_url
End With

Set shellWins = New SHDocVw.ShellWindows

For Each IE In shellWins
If IE.Name = "Internet Explorer" Then
Set IEObject1 = IE
Debug.Print IE.LocationURL
Debug.Print IE.LocationName
End If
Next

Set shellWins = Nothing
Set IE = Nothing


Do While appIE.Busy Or appIE.ReadyState <> 4
DoEvents
Loop

Set Doc = appIE.Document

'TerminPlatnosci = Trim(Doc.getelementsByName("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1")(0).Value)
'TerminPlatnosci = Trim(Doc.getElementsByTagName("tr")(0).getElementsByName("input id")(0).getAttribute("value"))
TerminPlatnosci = Trim(Doc.querySelector("input[name='section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1']").getAttribute("value"))
Range("A1").Value = TerminPlatnosci

appIE.Quit
Set appIE = Nothing

MsgBox "Daty zostały wczytane", , "Komunikat"

End Sub
[/KOD]

HTML:


The code provides three variants that I used when trying to download the data.

  1. TerminPlatnosci = Trim(Doc.getelementsByName("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1")(0).Value)
  2. TerminPlatnosci = Trim(Doc.getElementsByTagName("tr")(0).getElementsByName("input id")(0).getAttribute("value"))
  3. TerminPlatnosci = Trim(Doc.querySelector("input[name='section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1']").getAttribute("value"))
Range("A1").Value = TerminPlatnosci
 

Attachments

  • HTML code.jpg
    HTML code.jpg
    152 KB · Views: 8
Sort by date Sort by votes
There we go, so I created a blank HTML file with only the input element and uploaded it to my Webspace.
Then I tested the VBA which is working fine and outputs the text string "2022-05-23" as you asked.

HTML: 
<!DOCTYPE html>
<html>
<head>
<title>test</title>
</head>
<body>
<input id="section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1" type="text" value="2022-05-23" disabled>
</body>
</html>


Try this VBA Code:

VBA Code: 
Public Sub GetDataFromWebPage()
   Dim request As Object
   Dim response As String
   Dim html As New HTMLDocument
   Dim URL As String
   Dim webdata As Variant
   URL = "ADDRES_OF_THE_HTML_FILE" '<------- CHANGE THIS VALUE
   Set request = CreateObject("MSXML2.XMLHTTP")
   request.Open "GET", URL, False
   request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
   request.send
   response = StrConv(request.responseBody, vbUnicode)
   html.body.innerHTML = response

   webdata = html.getElementById("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")
 
   'MsgBox webdata
End Sub

Replace ADDRES_OF_THE_HTML_FILE with your intranet URL.
The data you need will be stored in the variable webdata
You can "preview" the data that was caught, just remove the single quote before MsgBox at the end of the code and it will pop up in a window.


Feel free to ask if anything doesn't work.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
PeteWright said:
There we go, so I created a blank HTML file with only the input element and uploaded it to my Webspace.
Then I tested the VBA which is working fine and outputs the text string "2022-05-23" as you asked.

HTML: 
<!DOCTYPE html>
<html>
<head>
<title>test</title>
</head>
<body>
<input id="section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1" type="text" value="2022-05-23" disabled>
</body>
</html>


Try this VBA Code:

VBA Code: 
Public Sub GetDataFromWebPage()
   Dim request As Object
   Dim response As String
   Dim html As New HTMLDocument
   Dim URL As String
   Dim webdata As Variant
   URL = "ADDRES_OF_THE_HTML_FILE" '<------- CHANGE THIS VALUE
   Set request = CreateObject("MSXML2.XMLHTTP")
   request.Open "GET", URL, False
   request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
   request.send
   response = StrConv(request.responseBody, vbUnicode)
   html.body.innerHTML = response

   webdata = html.getElementById("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")
 
   'MsgBox webdata
End Sub

Replace ADDRES_OF_THE_HTML_FILE with your intranet URL.
The data you need will be stored in the variable webdata
You can "preview" the data that was caught, just remove the single quote before MsgBox at the end of the code and it will pop up in a window.


Feel free to ask if anything doesn't work.
Click to expand...
thank you for the vba code. Unfortunately, an error occurs at the request.Open step. "Method 'open' of object 'IServerXMLHTTPRequest2' failed".
 
Upvote 0
Gregor_1 said:
thank you for the vba code. Unfortunately, an error occurs at the request.Open step. "Method 'open' of object 'IServerXMLHTTPRequest2' failed".
Click to expand...

Oh, I forgot to mention that you need some reference libraries prior to launch the Macro.

In your VBA Editor (Excel > [Alt]+[F11]) go to Tools > References... and then browse for the following (they may be located somewhere in the list, not necessarily at the top:
  • Microsoft HTML Object Library
  • Microsoft XML, v6.0
1698100577329.png
 
Upvote 0
PeteWright said:
Oh, I forgot to mention that you need some reference libraries prior to launch the Macro.

In your VBA Editor (Excel > [Alt]+[F11]) go to Tools > References... and then browse for the following (they may be located somewhere in the list, not necessarily at the top:
  • Microsoft HTML Object Library
  • Microsoft XML, v6.0
View attachment 100853
Click to expand...
I was convinced that I had these two libraries selected. it turned out that Microsoft XML version 6.0 was missing.

Now the procedure stops at the code "webdata = html.getElementById..."

Error message "run time error '91 Object variable or With blog variable not set
 
Upvote 0
Gregor_1 said:
I was convinced that I had these two libraries selected. it turned out that Microsoft XML version 6.0 was missing.

Now the procedure stops at the code "webdata = html.getElementById..."

Error message "run time error '91 Object variable or With blog variable not set
Click to expand...

That's because you need to change the Element ID to match the correct one on the site.

Incorrect Element ID:
webdata = html.getElementById("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")

Correct Element ID:
webdata = html.getElementById("section-36-control$xf-781$xf-794$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")
 
Last edited:
Upvote 0
Trixterz said:
That's because you need to change the Element ID to match the correct one on the site.

Incorrect Element ID:
webdata = html.getElementById("section-36-control$xf-771$xf-784$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")

Correct Element ID:
webdata = html.getElementById("section-36-control$xf-781$xf-794$xml_termin_platnosci-control$xforms-input-1").getAttribute("value")
Click to expand...
still the same error. In the Locals window, webdata has the value "empty"
 
Upvote 0
Gregor_1 said:
still the same error. In the Locals window, webdata has the value "empty"
Click to expand...
It is possible that the ID has changed. Do you know if the website has dynamic content that changes periodically?
If the website is dynamic you probably will never catch the right ID.
 
Upvote 0
PeteWright said:
It is possible that the ID has changed. Do you know if the website has dynamic content that changes periodically?
If the website is dynamic you probably will never catch the right ID.
Click to expand...
the data (payment date) that I want to download is constant. I checked the Element ID today and it is the same.
 
Upvote 0
I checked the html code more carefully and noticed that "<table class="fr-grid fr-grid- fr-norepeat>" (this table contains input id="section-36-control$xf-771$xf-784$xml_termin_platnosci -control$xforms-input-1" type="text" value="2022-05-23" disabled>) is nested in: "table id="pContainerControl_tabControl"
In this particular table there is an Iframe Tag that contains the src attribute to another HTML page
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Get values from HTML Input tag
Replies
0
Views
566
Razag
R
I
  • Question
Tweaked Internet Explorer web scrape code will not work using Get request
2
Replies
12
Views
2K
Indominus
I
A
  • Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
715
auglocqnuk
A
A
  • Question
VBA getElementsByTagName fail to catch "td" tag elements
Replies
5
Views
9K
RonSch
R
N
  • Question
How to get Value by VBA from Source code
Replies
1
Views
708
Legacy 456155
L

Forum statistics

Threads
1,215,111
Messages
6,123,151
Members
449,098
Latest member
Doanvanhieu

Share this page

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
Back
Top