Open URL then copy text...programmatically

[Guest]

Hi,

Is it possible to programmatically open an URL then copy the text within the
page. I would have several (hundreds) pages from Statistics Canada to get
data from and dont want to cut and paste by hand. All pages have the same URL
except that the end of the string is a number that I would change in a 'for
next' I would use VBA from Office XP.

Merci !
Jean-Francois Grenier.

 

[Douglas J. Steele]

My November, 2003 "Access Answers" column in Pinnacle Publication's "Smart
Access" shows how you can retrieve the content of a webpage. (You can
download the column and sample database for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

Unfortunately, that'll give you the HTML, not the plain text, but perhaps
you can use the technique and write a routine to parse out the information
in which you're interested.

 

[Guest]

Thanks Doug, you point me in the right direction. I'll try your code then get
back here with the results.
-----------------------------

 

[Guest]

Finaly got it to work but had big problem with the charset since it was
french... char è,à,â,é, returned strange char ... spent one day trying to
find a way.... So here is the code:

Dont forget to set reference to:
Microsoft VBscript Regular Expressions 5.5
Microsoft XML, v3.0

Private Sub Commande1_Click()

Dim objXMLHTTP As New XMLHTTP
Dim RegularExpressionObject As New RegExp
Dim URL As String
Dim strHTML As String

URL = "http://www.... your page"
objXMLHTTP.Open "GET", URL, False
objXMLHTTP.send

'french patch
strHTML = StrConv(objXMLHTTP.responseBody, vbUnicode)

'remove all the HTLM tag
With RegularExpressionObject
.Pattern = "<[^>]+>"
.IgnoreCase = True
.Global = True
End With
strHTML = RegularExpressionObject.Replace(strHTML, "")

'Do some string manipulation to get what you want

debug.print strHTML

Set objXMLHTTP = Nothing
Set RegularExpressionObject = Nothing

end sub

working code for ACCESS XP (2003) sp3

Hope this help someone !
(e-mail address removed)
thanks to Doug !