[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy web page text

Posted on 2011-10-13
21
Medium Priority
?
267 Views
Last Modified: 2012-05-12
I am using a function (supplied by Parax77) which, when called, allows me to copy the text of a web page... (if I supply the URL) and then insert the copied text into a table.

What I wish to accomplish is to be be able to loop through a table of URL's (tblURL)....
Call the function using the URL of each record
And then edit tblURL to insert the copied web text into the appropriate row

Sound easy enough but I have had no luck in laying out the syntex of the code.

Below is the code....

1 Dim rsURL As DAO.Recordset
2
3 Set rsURL = CurrentDb.OpenRecordset("Select strURL from tblURL")
4 With rsURL
5  Do Until .EOF
6  
7  Call TextFromHTML(strURL)
8    
9         .Edit
10        ![WebText] = TextFromHTML
11        .Update
12        .MoveNext
13         Loop
14        .Close
15         End With
16    Set rsURL = Nothing
0
Comment
Question by:Worcse
  • 9
  • 7
  • 3
  • +1
21 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 36965965
I don't think you are calling the function correctly.

If it returns a value, eg the HTML of the page then you could try this.
Dim rsURL As DAO.Recordset

Set rsURL = CurrentDb.OpenRecordset("Select strURL from tblURL")

With rsURL
  Do Until .EOF
  
   
         .Edit
        ![WebText] = TextFromHTML(strURL)

        .Update
        .MoveNext
  Loop
  .Close
 End With

 Set rsURL = Nothing

Open in new window

0
 
LVL 75
ID: 36965970
How about this:


    Dim rsURL As DAO.Recordset
    Set rsURL = CurrentDb.OpenRecordset("Select strURL from tblURL")
   
    With rsURL
        Do Until .EOF
               'Call TextFromHTML(strURL)
            .Edit
            ![WebText] = TextFromHTML(![strURL])
            .Update
            .MoveNext
        Loop
        .Close
    End With
    Set rsURL = Nothing
   
0
 

Author Comment

by:Worcse
ID: 36965993
DatabaseMX,
TY for the response
I am getting the following error...

Item not found in this collection

Could the error be that I am trying to use rsURL and the function calls for strURL...?

Below is the funciton i am using

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Function TextFromHTML(strURL) As String
 Dim ie As Object
 Set ie = CreateObject("internetexplorer.application")
 ie.navigate strURL
 Do Until ie.readyState = 4
  Sleep 10
 Loop
 TextFromHTML = ie.Document.Body.innerText
 ie.Quit
 Set ie = Nothing
End Function

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Worcse
ID: 36965998
imnorie -
TY as well.
If it makes a difference.. the function only pulls in the text of the web page
0
 
LVL 75
ID: 36966005
You have this:

Set rsURL = CurrentDb.OpenRecordset("Select strURL from tblURL")

Is strURL really the Name of a Field in tblURL ?

If not, then that is the first problem.  What is the name of the field?

mx
0
 

Author Comment

by:Worcse
ID: 36966012
strURL is the actual field name...
0
 
LVL 75
ID: 36966019
What line of code does the error occur on?

mx
0
 
LVL 35

Expert Comment

by:Norie
ID: 36966025
Can you post a link to the function you are using?
0
 

Author Comment

by:Worcse
ID: 36966035
![WebText] = TextFromHTML(![strURL])
0
 
LVL 75
ID: 36966048
Is [WebText]   a Field in table tblURL ?

mx
0
 

Author Comment

by:Worcse
ID: 36966053
i am able to get the function to work correclty if I assign only one url at at time

i tried to get the fucntion to update my tblURL but had to actually create a form bound to tblURL and was able to get the web text into the field WebText that way.... (long way around)
0
 

Author Comment

by:Worcse
ID: 36966058
DatabaseMX -

[WebText] is a field in my table.
It is a memo field... if that matters.
I changed it to a text field and received the same error.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36966063
<<i tried to get the fucntion to update my tblURL but had to actually create a form bound to tblURL and was able to get the web text into the field WebText that way.... (long way around) >>

That seems like overkill...

mx is on the right track.  The item that is "not in this collection" is WebText.

If it is a field in tblURL all you need to do is include it in your select statement:

 Set rsURL = CurrentDb.OpenRecordset("Select WebText, strURL from tblURL")

Open in new window


The rest of the code should work from there.


0
 
LVL 75
ID: 36966072
I must be webText is missing

Make this mod:


Set rsURL = CurrentDb.OpenRecordset("Select strURL, WebText from tblURL")
0
 

Author Comment

by:Worcse
ID: 36966099
BINGO... adding the WebText in the select seeemed to work.
However...  I have about 50 records in my table that i need to loop through and the process is stopping at the 5th one...
I have attacthed a screen shot of the error message
New-Picture--1-.bmp
0
 
LVL 35

Accepted Solution

by:
Norie earned 500 total points
ID: 36966113
Can you post some of the URLs you are trying to get the text from?
0
 

Author Comment

by:Worcse
ID: 36966125
imnorie -
 
i think if have resolved  the latest error message i was receiving by incresing the sleep time in the function.
hopefully this should give the function enough time to log on.... copy the text... and move to the next record.

Since both you and databaseMX were both spot-on in your advise/conclusion I think it only fair to split the points equally....

Are you both ok with this...?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36966174
ok ...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36968246
Was my post here (http:#a36966063) overlooked?  

I'm actually fine with the proposed split - just a little surprised that there was no response (or Expert acknowledgement).
0
 
LVL 75
ID: 36969506
I think the split s/b between mbizup and myself, she she noted the missing field in Select - although I did not see that post yet when I posted a similar note.

Unfortunately, what  imnorie posted @ http:#a36965965  would not really work, because in

![WebText] = TextFromHTML(strURL)   ... strURL would have been interpreted as a non-existent variable.

mx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question