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---



--
RyGuy

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.



--
RyGuy


"ryguy7272" wrote:

> 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---
>
>
>
> --
> RyGuy