Reading a text file from the web

[plizak]

I'm looking to read a text file from the web. I can load the file
into a new workbook or I can read in the data line by line, but I
can't get both to be done. What I would like done is to open an input
stream to the web file. Any suggestions would be appreciate. What
I've go so far is listed below.

Thanks,
Peter

Load the file from the web
- Creates a whole new workbook to read the text file
- I want this hidden from user
=======================================
Workbooks.Open Filename:=filename_to_open


Reading in the file and from local directory line by line
- If I use test_to_open (local file) it works
- If I use filename_to_open with a web link it fails.
(Runtime error 76 - path not found)
=======================================
filename_to_open = "http://www.lizak.ca/time.html"
test_to_open = "C:\output.txt"


Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim oStream
Dim sData
Dim aData

'Set oStream = FSO.OpenTextFile(filename_to_open)
Set oStream = FSO.OpenTextFile(test_to_open)


sData = oStream.readall
aData = Split(sData, vbNewLine)

Debug.Print aData(0)


I can also access the file line by line with:
- Same as above basically.
==============================
Dim strItem
Open test_to_open For Input As #1

Line Input #1, strItem
Debug.Print strItem
Close #1

 

[Steve Yandl]

Below is an example of a routine that I recently did for a friend who wanted
to retrieve a currency exchange rate and have it appear in a worksheet cell
that he could reference with his worksheet functions. It's got a few things
you're not asking for but it will allow you to return a text stream from a
web page and then you can parse the stream out however you need to.

'------------------------------------------

Sub ConvertCurrency()
Dim myRange As Range
Dim a As Long
Dim b As Long
Dim strResp As String
Dim factor As String

Set myRange = Range("B3")

On Error Resume Next

strURL = "http://www.x-rates.com/d/ZAR/table.html"

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", strURL, False
objHTTP.Send

strResp = objHTTP.ResponseText

a = InStr(strResp, "New Zealand Dollar")
b = InStr(a, strResp, "class=")
factor = Mid(strResp, b + 13, 8)

myRange.Value = factor

Set objHTTP = Nothing

End Sub


'------------------------------------------

Steve Yandl

 

[plizak]

Excellent, thank you very much for the reply, works like a charm!

Much more elegant than my open sheet, grab data, close sheet and hope
the user doesn't catch me

Cheers,
Peter