Web query refreshes inconsistently

bob135

New Member
Joined
Sep 4, 2007
Messages
6
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

bob135

New Member
Joined
Sep 4, 2007
Messages
6
anyone know? if this has already been asked about please just link me to the post, i looked already but didnt find anything.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top