R
Roy
I've been using a web query to download Yahoo historical quotes for the
past year with no problems. Recently the same VBA that's been working
all this time has quit working, or only works sporadically.
(Sporadically means that the first query sometimes works correctly, but
not any subsequent queries. Even restarting the machine doesn't fix
it). Here's an illustration of my web query with all the dynamic
parameters replaced with constants.
With ActiveSheet.QueryTables.Add _
(Connection:="URL;http://ichart.finance.yahoo.com/table.csv?_
s=ibm&d=7&e=20&f=2006&g=d&a=11&b=16&c=1962&ignore=.csv", _
Destination:=ActiveSheet.Range("A1"))
'//active sheet is a newly created sheet for each new query
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
'//error thrown here
End With
This URL is the same as I get if I right click on the "Download to
Spreadsheet" link in Yahoo and copy the link location, but when I use
it in a web query it doesn't work.
Using BackgroundQuery:=False I get a 1004 error, and if I change this
to
BackgroundQuery:=True so it has time to think about it, I get an error
that says:
"Unable to open <URL> ... The Internet site reports that a connection
was established but the data is not available".
This doesn't seem to be the correct error being reported because, 1) I
can paste the URL into my web browser and it works correctly, and 2) if
I change the starting date from 1962 to 2005 then the delay between the
start of the Refresh and the time when the error window comes up is
reduced from several seconds to almost instantanous, which would seem
to indicate that the data is indeed being downloaded but that Excel
just doesn't want to give it up.
This works the same (same error) with both Excel2k and Excel 2003 (same
machine). I've run out of ideas. Does anybody have any clue about
what's happening here?
Thanks,
Roy
past year with no problems. Recently the same VBA that's been working
all this time has quit working, or only works sporadically.
(Sporadically means that the first query sometimes works correctly, but
not any subsequent queries. Even restarting the machine doesn't fix
it). Here's an illustration of my web query with all the dynamic
parameters replaced with constants.
With ActiveSheet.QueryTables.Add _
(Connection:="URL;http://ichart.finance.yahoo.com/table.csv?_
s=ibm&d=7&e=20&f=2006&g=d&a=11&b=16&c=1962&ignore=.csv", _
Destination:=ActiveSheet.Range("A1"))
'//active sheet is a newly created sheet for each new query
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
'//error thrown here
End With
This URL is the same as I get if I right click on the "Download to
Spreadsheet" link in Yahoo and copy the link location, but when I use
it in a web query it doesn't work.
Using BackgroundQuery:=False I get a 1004 error, and if I change this
to
BackgroundQuery:=True so it has time to think about it, I get an error
that says:
"Unable to open <URL> ... The Internet site reports that a connection
was established but the data is not available".
This doesn't seem to be the correct error being reported because, 1) I
can paste the URL into my web browser and it works correctly, and 2) if
I change the starting date from 1962 to 2005 then the delay between the
start of the Refresh and the time when the error window comes up is
reduced from several seconds to almost instantanous, which would seem
to indicate that the data is indeed being downloaded but that Excel
just doesn't want to give it up.
This works the same (same error) with both Excel2k and Excel 2003 (same
machine). I've run out of ideas. Does anybody have any clue about
what's happening here?
Thanks,
Roy