Open URL For Input
 

Anyone know a way to use Open For Input on a file from the Internet?

Dim oURL As String
oURL = "http://finance.yahoo.com/d/quotes.csv?s=JPYUSD=X&f=sl1d1t1&e=.csv"
Open oURL For Input As #1

That returns error "Bad file name or number."

Thanks for your help.

Open URL For Input
 

I didn't figure out any way to use Open/For Input directly on a URL, but I
did figure out how to download the CSV file from a URL, save it to a
temporary folder, read from it and then delete it, all in the background.

The first part is used at module level to declare references to an external
procedures in a dynamic-link library (DLL).

Public Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Sub RatesWebQuery()

Dim oSymbol As String
Dim oDate As String
Dim oTime As String
Dim oRate As Double
Dim oLocalFile As String
Dim oURL As String
Dim oReturnValue As Long
Dim oFSO As Scripting.FileSystemObject

Set oFSO = CreateObject("Scripting.FileSystemObject")

oLocalFile = Environ("TMP") & "\quotes.csv"
oURL = "http://finance.yahoo.com/d/quotes.csv?s=JPYUSD=X&f=sl1d1t1&e=.csv"

oReturnValue = URLDownloadToFile(0, oURL, oLocalFile, 0, 0)

If oReturnValue < 0 Then
If MsgBox("Problem downloading.") < 1 Then Exit Sub
Else
Open oLocalFile For Input As #1
Input #1, oSymbol, oRate, oDate, oTime
Close #1
oFSO.DeleteFile (oLocalFile)
End If

MsgBox oRate

End Sub