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

 

[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