Right now I have a spreadsheet with a couple of web queries on different sheets. I have a macro take the data from each sheet, update parameters, refresh the sheet, and take the data again for every parameter (in this case a stock ticker symbol).
The error usually looks like this:
Run-time error '1004':
Unable to open http://finance.yahoo.com/q/ks?s=PSPT . Cannot locate the internet server or proxy server.
Sometimes it returns another error which I will post when I get it next. Its another run-time error talking about file not found, suggesting I limit the file path length to 218 characters and make sure the file exists and so forth. This seems strange for a web query but thats what I got.
The line of code the error points to is this:
Sheets("Financial Data").Range("A1").QueryTable.Refresh BackgroundQuery:=False
Thats just a single cell where the web query is located. The web query code looks like this:
WEB
1
http://finance.yahoo.com/q/ks?s=["Ticker"]
Selection=10,11,12,13,14,15,16
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
I just used 'data > import external data' to get this query into cell A1 of the sheet 'Financial Data.' It refreshes manually just fine.
My guess is that excel tries refreshing a couple of times but then gives up and returns an error. If this is so, how do I change amount of time it waits for the data?
How do I get it to skip the refresh and move on to the next ticker instead of spitting out an error if it waits too long?
Thanks!
PS: The only code related to this question I found on this board was the following:
If Application.Wait(Now + TimeValue("0:00:10")) Then
With Worksheets(1).QueryTables(1)
If .Refreshing Then .CancelRefresh
End With
End If
However, I'm not sure where to put it, since the refresh is a single line of code.
The error usually looks like this:
Run-time error '1004':
Unable to open http://finance.yahoo.com/q/ks?s=PSPT . Cannot locate the internet server or proxy server.
Sometimes it returns another error which I will post when I get it next. Its another run-time error talking about file not found, suggesting I limit the file path length to 218 characters and make sure the file exists and so forth. This seems strange for a web query but thats what I got.
The line of code the error points to is this:
Sheets("Financial Data").Range("A1").QueryTable.Refresh BackgroundQuery:=False
Thats just a single cell where the web query is located. The web query code looks like this:
WEB
1
http://finance.yahoo.com/q/ks?s=["Ticker"]
Selection=10,11,12,13,14,15,16
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
I just used 'data > import external data' to get this query into cell A1 of the sheet 'Financial Data.' It refreshes manually just fine.
My guess is that excel tries refreshing a couple of times but then gives up and returns an error. If this is so, how do I change amount of time it waits for the data?
How do I get it to skip the refresh and move on to the next ticker instead of spitting out an error if it waits too long?
Thanks!
PS: The only code related to this question I found on this board was the following:
If Application.Wait(Now + TimeValue("0:00:10")) Then
With Worksheets(1).QueryTables(1)
If .Refreshing Then .CancelRefresh
End With
End If
However, I'm not sure where to put it, since the refresh is a single line of code.
