Reading a text file from the web



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.


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 = ""
test_to_open = "C:\output.txt"

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


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 :)


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question