webquery in a loop not giving complete results

[hariimmadi]

Hi,
could someone help me why following code not working. greatly appreciate any help.
this code is getting results for only 3 cells no matter if i loop through 5 or 10 cell values. what could be wrong?

Sub WebQueryQuotes()
Set dest = Range("A8")
For Each cell In [A1:A7]
cell.Select
Item = cell.Value
ConnectString = "URL;http://finance.yahoo.com/q/ks?s=" & cell.Value
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=dest)
With QT
.Name = cell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "45"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
 
End With
'QT.Refresh BackgroundQuery:=True
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next QT
 
Set dest = dest.Offset(11, 0)
Next cell
End Sub

 

[Andrew Poulsom]

What do you have in A1:A7?

 

[hariimmadi]

Hi Andrew,
thanks for your reply. I've following values in cells A1 through A7. for reference, I'm also providing a link with inputs & output i'm getting.
As you can see, output is generating from row 30 when am expecting from row 8. (row 8 through row 30 are blank... missing output also?). output is coming for only 3 cells when i've a total of 7 cells.
http://spreadsheets.google.com/pub?key=t-DQKF9cKHfI9yxlLFbzBag&single=true&gid=0&output=html
<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=218 x:str><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7972" width=218><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 164pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=218>BAC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>CIEN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>WFR</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>MW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>JPM</TD></TR></TBODY></TABLE>

 

[Andrew Poulsom]

If you run the code below you will see the names of the query tables in column C. It seems that BAC, C, F and JPM aren't returning any data:

Sub WebQueryQuotes()
    Dim Dest As Range
    Dim Cell As Range
    Dim ConnectString As String
    Dim QT As QueryTable
    Set Dest = Range("A8")
    For Each Cell In [A1:A7]
        ConnectString = "URL;http://finance.yahoo.com/q/ks?s=" & Cell.Value
        Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Dest)
        With QT
            .Name = Cell.Value
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "45"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Dest.Offset(, 2).Value = QT.Name
'       QT.Refresh BackgroundQuery:=True
        For Each QT In ActiveSheet.QueryTables
            QT.Delete
        Next QT
        Set Dest = Dest.Offset(11, 0)
    Next Cell
End Sub

The macro recorder used table 47 (not 45) for BAC.

 

[hariimmadi]

Thanks Andrew.
you nailed it down.. thank you so much.