Solved Read website content and write to formfield (1 Viewer)

[Louisv]

Hi,
In my access form are the fields: NUM en TITLE, the purpose is that the bold red underlined intranettext is inserted in the formfield TITLE.

At our intranet we have a fixed location where default documents are placed with heir unique number.

With all the articles I found on the internet I was only able to open the browserpage with the specific document after the user had typed the number in the form field NUM.

In references I added:
- Microsoft HTML object library
- Microsoft XML, v 6.0

The code in Access:

Private Sub NR_AfterUpdate() '(after the user has typed the number een nr)
DoCmd.GoToControl "TITLE"
Dim Header As String
Dim IE As Object
Dim URL As String
Dim Doc As HTMLDocument
URL = "https://intranetwebsite/pagina/" & NUM
Set IE = CreateObject("InternetExplorer.Application")

IE.Silent = True
IE.Visible = True

IE.Navigate URL '(until here the code works fine, apart from a slow server)

'Below the code does not wait until the browser page is completed let alone gets the right content)
While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy
DoEvents
Wend

Set Doc = IE.HTMLDocument
Header = Doc.getElementsByTagName("h1")(0).innerText

TITLE = Header
End Sub

On our intranetpage part of the content is:
<h1 class="mx-text mx-name-text2 inline-block" id="mxui_widget_Wrapper_111" widgetId="mxui_widget_Wrapper_111" data-mendix-id="89_14_21">Beheersmodel Grote Projecten</h1>

Who can point me in the right direction?

Kind Regards,
Louis

 

[isladogs]

There are several methods of extracting data from a web page depending on the structure of your page. One of the following should do wha you want

One method is to download the page as a text file, read the page searching for a specific string and extracting the text that follows. I use that idea so clients can check for newer versions of my apps online. This approach is demonstrated here https://www.access-programmers.co.uk/forums/showthread.php?t=293970

Another method uses 'GetElementByID'. Do a site search or Google search for this.

Both methods will only take a fraction of a second to run.

 

[Louisv]

Hi Ridders,

Fraction of a second will be several days because I am a beginner but thanks for your suggestion.
I will look in to it next week.

Kind Regards,
Louis

 

[isladogs]

Hi Ridders,

Fraction of a second will be several days because I am a beginner but thanks for your suggestion.
I will look in to it next week.

Kind Regards,
Louis

LOL!!!!
But once you get there, it will only take a fraction of a second to run!

 

[Louisv]

Keep getting error on WHILE-statement: "method Busy failed"

Private Sub NR_AfterUpdate()
DoCmd.GoToControl "TITLE"
Dim Header As String
Dim IE As Object
Dim URL As String
URL = "https://intranetwebsite/pagina/" & NR
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = 0
.Navigate URL

While IE.Busy Or IE.ReadyState <> 4:banghead:
DoEvents
Wend
End With

Dim Doc As HTMLDocument
Set Doc = IE.Document
Header = Trim(Doc.getElementsByName("h1")(0).innerText)

TITLE= Header


End Sub

 

[isladogs]

OK so the code you're using isn't mine and you are using GetElementByName rather than GetElementByID as I suggested

Anyway, here are some things to check
1. Use Debug.Print URL to check it is a valid URL
2. Check you have an internet connection
3. Try replacing your problem section with this alternative code to allow time to grab the data

   Do Until Not .Busy And .ReadyState = 4
        Loop
        
        DoEvents

You may need to build in a further delay using the sleep api as well.
For an example see this example database Get Current Geolocation

 

[Louisv]

Hi Ridders,

I certainly looked at your solution 293970 but for a beginner it looks too complicated, with all the codelines in the sample database moreover I was stil not up to the line "getelement...."

But I have come a few lines further with your advise because now the code returns a failure on the line "Set Doc = IE.HTMLDocument", returning the message "the property or method is not supported by this object" (translated)

Thanks and kind regards, Louis

 

[isladogs]

Make sure you have the Vba reference 'Microsoft HTML Object Library' ticked in the vb editor.

 

[Louisv]

Hi,

I don't have sthat specific one but I have ticked:

- VBAfor applications
- Microsoft 14.0 Object library
- Ole automation
- Microsoft Office 14.0 access database engine object
- Microsoft HTML object library
- Microsoft internet controls
- Microsoft xml v 6.0

 

[isladogs]

Hi,

I don't have sthat specific one but I have ticked:

- VBAfor applications
- Microsoft 14.0 Object library
- Ole automation
- Microsoft Office 14.0 access database engine object
- Microsoft HTML object library
- Microsoft internet controls
- Microsoft xml v 6.0

Sorry I made a mistake. Now corrected.
Did you check it was a valid URL?

You must have changed your Set doc line as well

Please post your latest code and provide a valid NR value so I can test it
Also what you would expect the result to be.

 

[Louisv]

Hi,

The url is correct because the ie explorer opens perfectly at the right page.
But you can not test it because the page is intranet page...

At our intranet we have a fixed location where default documents are placed with their unique number.

In my access form are the fields: NUM en TITLE, the purpose is that the bold blue underlined intranettext is inserted in the formfield TITLE.

Steps to perform:

1 User types in Access form the number in field NR (689+CR)
2 the code opens explorer an the page 689, this works perfectly!

Inranetpage content:

<h1 class="mx-text mx-name-text2 inline-block" id="mxui_widget_Wrapper_111" widgetId="mxui_widget_Wrapper_111" data-mendix-id="89_14_21">Beheersmodel Grote Projecten</h1>


3 Search interanetpage for text title (h1, blue text)
4 The accessform the field TITLE is filled with text in field h1 (blue text)


below is the current code:

Private Sub NR_AfterUpdate()
DoCmd.GoToControl "TITLE"
Dim Header As String
Dim IE As Object
Dim URL As String
URL = "https://werkwijzer.cf-prod.intranet.rws.nl/link/standaard/" & NR
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = 0
.Navigate URL

Do Until Not IE.Busy Or IE.ReadyState = 4
Loop
DoEvents
End With

Dim Doc As HTMLDocument 'up to here no errors
Set Doc = IE.HTMLDocument '<<<error message "the property or method is not supported by this object"
Header = Trim(Doc.getElementsByID("89_14_21")(0).innerText)

TITLE= Header

End Sub

 

[arnelgp]

use IE.Document instead.

with ie.document
Header=.getElementByID("89_14_21")(0).innerText
end with

 

[isladogs]

Arnel beat me to it.
In fact you don't need to define doc at all. Replace:

Dim Doc As HTMLDocument 
Set Doc = IE.HTMLDocument
Header = Trim(Doc.getElementsByID("89_14_21")(0).innerText)

with

Header = Trim(IE.Document.GetElementsByID("89_14_21")(0).innerText)

and i'm not sure the (0) is needed either

 

[Louisv]

Both suggestions respond with (translated):

This property or method is not supported by this object

at the line:

Header = IE.getElementById("89_14_21")(0).innerText

or

Header = Trim(IE.Document.GetElementsByID("89_14_21")(0).innerText)

 

[arnelgp]

you forgot the Document, see the post again.

 

[Louisv]

Hi,

Also tried that but unfortunately the translated message:

Method Document of object IWebbrowser2 has failed

 

[Louisv]

Hi,

Also tried that one but I got the message:

Method Document of object Webbrowser2 has failed

 

[Louisv]

I get the impression that the vba error message is earlier than the explorer is completed loading the page

 

[arnelgp]

review the liading of ie again.
i am not on my pc, so this might fail too.
add Object to ie;

while ie.Object.Busy or ie.Object.ReadyState <> 4
doevents
wend

if that is ok, add Object to all intance of ie:

header=ie.Object.document....

 

[isladogs]

Aha - spotted a simple typing error
It should be GetElementbyId NOT GetElementsbyId

Header = Trim(IE.Document.GetElementByID("89_14_21")(0).innerText)

If it still fails, try this next:

Header = IE.Document.GetElementByID("89_14_21").innerText

i.e. omit the Trim and the (0)

I wouldn't go back to While...Wend (as it caused you problems at the beginning)