Read text from .txt file via hyperlink

  • Thread starter Thread starter bravofoxtrotuk
  • Start date Start date
B

bravofoxtrotuk

This seemed easy at first...
I have a database which users can download from a web site in a zipped file,
and then use on their machines - completely free-standing.

How can I get the database to go to a .txt file on the website when it
opens, read the text (which will be one line consisting of the latest version
number which I will put in there), then read the version number and compare
it with the version of the database they are running? This is so that I can
put up a message saying "New version is available".

I've tried FollowHyperlink and OpenTextFile, but can't quite get to read the
text from the file.
 
bravofoxtrotuk said:
This seemed easy at first...
I have a database which users can download from a web site in a zipped
file,
and then use on their machines - completely free-standing.

How can I get the database to go to a .txt file on the website when it
opens, read the text (which will be one line consisting of the latest
version
number which I will put in there), then read the version number and
compare
it with the version of the database they are running? This is so that I
can
put up a message saying "New version is available".

I've tried FollowHyperlink and OpenTextFile, but can't quite get to read
the
text from the file.
Click to expand...

Dim f As Integer
Dim buffer as string

f = FreeFile()
Open "Filename.txt" For Binary Access Read As f
buffer = Space$(LOF(f))
Get #f, , buffer
Close f

The string variable buffer will then contain the whole contents of
Filename.txt, which you can then use for your comparison.

Tip: When you create or update your text file, just type the version info
and don't press Return. This will save you having to remove the
carriage-return/linefeed in your comparison code.
 
Stuart McCall said:
Dim f As Integer
Dim buffer as string

f = FreeFile()
Open "Filename.txt" For Binary Access Read As f
buffer = Space$(LOF(f))
Get #f, , buffer
Close f

The string variable buffer will then contain the whole contents of
Filename.txt, which you can then use for your comparison.

Tip: When you create or update your text file, just type the version info
and don't press Return. This will save you having to remove the
carriage-return/linefeed in your comparison code.
Click to expand...
Stuart,
thanks for quick reply, but I'm getting the same error message as in my
previous messing about - "run time error 52, bad file name or number". I'm
using "http://website.net/test.txt" for the filename so I guess it's that, as
the f variable is fine.
If I use IE it opens the file with that address OK.
Bob
 
bravofoxtrotuk said:
Stuart,
thanks for quick reply, but I'm getting the same error message as in my
previous messing about - "run time error 52, bad file name or number".
I'm
using "http://website.net/test.txt" for the filename so I guess it's that,
as
the f variable is fine.
If I use IE it opens the file with that address OK.
Bob
Click to expand...

Yes that's the problem. You're trying to use a URL as a filename. Web
servers require that you use relative paths. If the text file resides in the
same folder as your database file then use just the filename, ie:

Open "test.txt" For Binary Access Read As f

or, failing that:

Open "./test.txt" For Binary Access Read As f

If the file resides elsewhere, you need to bone up on relative paths. You're
sure to turn up something on Google...
 
Stuart McCall said:
Yes that's the problem. You're trying to use a URL as a filename. Web
servers require that you use relative paths. If the text file resides in the
same folder as your database file then use just the filename, ie:

Open "test.txt" For Binary Access Read As f

or, failing that:

Open "./test.txt" For Binary Access Read As f

If the file resides elsewhere, you need to bone up on relative paths. You're
sure to turn up something on Google...
Click to expand...
Hi Stuart,
well, as the file's on a remote web server it would need to be an absolute
reference. However, many hours further research reveals that Open can't be
used with ftp or http, but elsewhere on the forum in an old thread, Doug
Steele has once again come up with an answer. I've copied the reference
below:

[ See whether what I wrote about in my November, 2003 "Access Answers" column
for Pinnacle Publication's "Smart Access" has what you need.

You can download the column (and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html ]

There's also info in there about extracting what you want from the html
which is returned. In my case I don't need that as I'm getting data from a
plain .txt file which consists of just text (actually, a number). As I'm the
one putting the file there, I can be sure of the format.

This is the code of Doug's I've tested in case anyone else is struggling
with this:

Function GetFromWebpage(URL As String) As String
On Error GoTo Err_GetFromWebpage
Dim objWeb As Object
Dim strXML As String
' Instantiate an instance of the web object
Set objWeb = CreateObject("Microsoft.XMLHTTP")
' Pass the URL to the web object, and send the request
objWeb.Open "GET", URL, False
objWeb.Send
' Look at the HTML string returned
strXML = objWeb.responseText
GetFromWebpage = strXML
Debug.Print GetFromWebpage
End_GetFromWebpage:
' Clean up after ourselves!
Set objWeb = Nothing
Exit Function
Err_GetFromWebpage:
' Just in case there's an error!
MsgBox Err.Description & " (" & Err.number & ")"
Resume End_GetFromWebpage
End Function

So using my example from earlier in this post:

GetFromWebpage "http://website.net/test.txt"

- returns my text (number) - I just need to sort out what I do with it now...

Bob
 
bravofoxtrotuk said:
Stuart McCall said:
message


Yes that's the problem. You're trying to use a URL as a filename. Web
servers require that you use relative paths. If the text file resides in
the
same folder as your database file then use just the filename, ie:

Open "test.txt" For Binary Access Read As f

or, failing that:

Open "./test.txt" For Binary Access Read As f

If the file resides elsewhere, you need to bone up on relative paths.
You're
sure to turn up something on Google...
Click to expand...
Hi Stuart,
well, as the file's on a remote web server it would need to be an absolute
reference. However, many hours further research reveals that Open can't be
used with ftp or http, but elsewhere on the forum in an old thread, Doug
Steele has once again come up with an answer. I've copied the reference
below:

[ See whether what I wrote about in my November, 2003 "Access Answers"
column
for Pinnacle Publication's "Smart Access" has what you need.

You can download the column (and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html ]

There's also info in there about extracting what you want from the html
which is returned. In my case I don't need that as I'm getting data from
a
plain .txt file which consists of just text (actually, a number). As I'm
the
one putting the file there, I can be sure of the format.

This is the code of Doug's I've tested in case anyone else is struggling
with this:

Function GetFromWebpage(URL As String) As String
On Error GoTo Err_GetFromWebpage
Dim objWeb As Object
Dim strXML As String
' Instantiate an instance of the web object
Set objWeb = CreateObject("Microsoft.XMLHTTP")
' Pass the URL to the web object, and send the request
objWeb.Open "GET", URL, False
objWeb.Send
' Look at the HTML string returned
strXML = objWeb.responseText
GetFromWebpage = strXML
Debug.Print GetFromWebpage
End_GetFromWebpage:
' Clean up after ourselves!
Set objWeb = Nothing
Exit Function
Err_GetFromWebpage:
' Just in case there's an error!
MsgBox Err.Description & " (" & Err.number & ")"
Resume End_GetFromWebpage
End Function

So using my example from earlier in this post:

GetFromWebpage "http://website.net/test.txt"

- returns my text (number) - I just need to sort out what I do with it
now...

Bob
Click to expand...

Yes that's the correct way to get the text. Sorry for leading you astray
(don't know what I was thinking). Incidentally, there's another method,
similar to Doug's that uses InternetReadFile from wininet.dll:

http://www.smccall.demon.co.uk/MiscApi.htm#Scrape

(purely out of interest, being as you have a solution)
 
Back
Top