I submitted a post on this DG a few days ago, and got some help at that time,
but now I've hit a wall again... I am looking up data for about 100 stock
symbols on finance.yahoo.com. I am importing information for these stocks
and, approximately 90% of the time the data is fine, but the rest of the
time, I seem to access the wrong data. The problem is with something called
WebTables. A recorded macro will yield something like .WebTables = "48,53"
and this usually gives me what I want, but for certain stock symbols, the
Web.Tables are slightly different, like .WebTables = "46,51" or .WebTables =
"47,52". The WebTables data should correspond to data from "KEY STATISTICS"
and data from "ANALYST". (see: http://finance.yahoo.com/q?s=pfe). The
Yahoo people always report the relevant information in "KEY STATISTICS" and
"ANALYST", but the WebTables are sometimes numbered differently (i.e.,
"48,53", or "47,52", or "46,51"). Do I need to use XML to reference the
correct "KEY STATISTICS" and "ANALYST" information or can VBA do it for me?
If so, how do I do this (I don't know anything about XML). Any assistance
would be Gretel appreciated!

My code is listed below:

Sub HistData()

Application.ScreenUpdating = False

Dim str1 As String
Dim str2 As String
Dim c As Range
Dim d As Range

Dim bFound As Boolean
Dim ws As Worksheet

For Each c In Sheets("ZZZ - USA Firms").Range("D3:D92")


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:IV50000").ClearContents

str1 = "URL;http://finance.yahoo.com/q/hp?s=" & _
c.Value & "&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"
str2 = "hp?s=" & c.Value & "a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"

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

.Name = str2

.Name = "hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d "

.FieldNames = True
.RowNumbers = False
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 11.14

Cells.Select
With Selection
.MergeCells = False
End With

'----------------------------------

Columns("C:C").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft

For Each d In Sheets("ZZZ - USA Firms").Range("D3:D4")

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

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

.Name = str2

.Name = "hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d "

.FieldNames = True
.RowNumbers = False
.WebTables = "48,53"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 11.14

Cells.Select
With Selection
.MergeCells = False
End With

Range("H:D").Select
Selection.Delete Shift:=xlToLeft

' Range("A1").Select
'----------------------------------
Next d
Next c

Sheets("ZZZ - USA Firms").Activate
Range("A1:B1").Select

End Sub

--
RyGuy
PS, thanks for the solution to the looping problem Merjet!!