Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading a text file from the web
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading a text file from the web
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 On Apr 30, 7:56*pm, "Steve Yandl" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading from a text file | Excel Discussion (Misc queries) | |||
reading from text file to excel file | Excel Programming | |||
Reading a text file ? | Excel Worksheet Functions | |||
Reading a text file | Excel Discussion (Misc queries) | |||
Help reading in a text file | Excel Programming |