webquery in a loop not giving complete results

hariimmadi

New Member
Joined
Dec 13, 2009
Messages
6
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?

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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

hariimmadi

New Member
Joined
Dec 13, 2009
Messages
6
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

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