Code Fails at this Line: .Refresh BackgroundQuery:=False

[ryguy7272]

Application.ScreenUpdating = False

Dim sngStart As Double
sngStart = Now


Dim str1 As String
Dim str2 As String
Dim c As Range
Dim Stocks As Range
Dim bFound As Boolean
Dim ws As Worksheet


Set Stocks = Application.InputBox( _
"Type 'Symbols' in the input box below", Type:=8)

For Each c In Sheets("Firms, Import").Range("Symbols")

bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws

If bFound = False Then
Worksheets.Add.Name = c.Value
End If

'----------------------------------------------------------
Sheets(c.Value).Select
Cells.Select
Range("A1:IV65536").ClearContents

str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _
c.Value

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))

.Name = str1

.Name = "ks?s=c.Value"


.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
On Error GoTo Errorhandler:
.Refresh BackgroundQuery:=False
End With

Range("B").Select
Selection.Delete Shift:=xlToLeft
‘etc………….


Code sometimes fails on this line:
..Refresh BackgroundQuery:=False
(just a few lines above)

I’m baffled; I’m not sure why this happens. Sometimes the code runs fine,
and completes all import request (stock symbols listed in a column) and
sometimes it fails for no apparent reason. I added a few simple error
handlers (On Error Resume Next) and then the code ceases to do anything at
all. Does anyone know what may cause this behavior?

TIA,
Ryan---

 

[ryguy7272]

After doing several more tests and some research on the web, I came to the
conclusion that it just fails when it is trying to find stocks that are not
followed very faithfully, or not well researched, or the like. It appears to
work fine for all the 'blue chips' and the popular securities; it definitely
becomes unreliable when querying for lesser known issues. I guess it's a
suggestion, of sorts, to stay away from those esoteric securities.