bob135
03-31-2008, 05:50 AM
I have several web queries that get data from yahoo finance and google finance. It generates the following Error:
Run Time Error 1004
the file could not be accessed. Try one of the following:
-Make sure the folder exists
-Make sure the file isn't read only
-Make sure no < > | [and other special characters] are in the filename
-Make sure the file path is less than 218 [characters]
This is the code for the refresh:
Range("YHOOCompanyData").QueryTable.Refresh BackgroundQuery:=False
Range("GoogleWebData").QueryTable.Refresh BackgroundQuery:=False
These are the actual queries: ([Ticker] just refers to some other cell)
WEB
1
[yahoo finance url]lookup?s=["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
WEB
1
[google finance url]["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
NOTE: URLS are a bit inaccurate since I couldn't post links, but you get the idea and I don't think I'm querying from a bad page (since it works some of the time)
It gets the data inconsistently...sometimes it works fine and sometimes it fails. This is also true of manual refresh. At one point I attempted 9 manual refreshes and they all failed, but at other times I can do 10-20 in a row with no problems.
Ideally, I could avoid this error completely.
The other, less elegant solution (which I am trying to implement now) just involves looping again and again until the refresh works or it loops too many times. I would like to get a better solution if anyone has one (like identifying the cause of the error).
On the topic of this less elegant solution - do you think it woudl help to add a 5-10 second wait between refreshes, or doesn't it matter? Is it possible that google and yahoo would temporarily block you from querying data if you send too many requests?
Run Time Error 1004
the file could not be accessed. Try one of the following:
-Make sure the folder exists
-Make sure the file isn't read only
-Make sure no < > | [and other special characters] are in the filename
-Make sure the file path is less than 218 [characters]
This is the code for the refresh:
Range("YHOOCompanyData").QueryTable.Refresh BackgroundQuery:=False
Range("GoogleWebData").QueryTable.Refresh BackgroundQuery:=False
These are the actual queries: ([Ticker] just refers to some other cell)
WEB
1
[yahoo finance url]lookup?s=["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
WEB
1
[google finance url]["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
NOTE: URLS are a bit inaccurate since I couldn't post links, but you get the idea and I don't think I'm querying from a bad page (since it works some of the time)
It gets the data inconsistently...sometimes it works fine and sometimes it fails. This is also true of manual refresh. At one point I attempted 9 manual refreshes and they all failed, but at other times I can do 10-20 in a row with no problems.
Ideally, I could avoid this error completely.
The other, less elegant solution (which I am trying to implement now) just involves looping again and again until the refresh works or it loops too many times. I would like to get a better solution if anyone has one (like identifying the cause of the error).
On the topic of this less elegant solution - do you think it woudl help to add a 5-10 second wait between refreshes, or doesn't it matter? Is it possible that google and yahoo would temporarily block you from querying data if you send too many requests?