VBA Recorded Macro Web Query for Data, how to repeat the query for next item on the list

[BennyBB]

On sheet1 in Excel 2007 I have a list starting in cell A1=cat, B1=dog, C1=mouse.

I recorded a macro that does a web query for data at Yahoo and for search term "cat".

How can I repeat the macro for the whole list A1, B1, C1?

Sub YahooSearch()
'
' YahooSearch Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://search.yahoo.com/search;_ylt=A2KK_foKYzdS7TQAH36bvZx4?p=cat&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-900" _
        , Destination:=Range("Sheet2!$A$1"))
        .Name = _
        "search;_ylt=A2KK_foKYzdS7TQAH36bvZx4?p=cat&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-900"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=159
End Sub

Thanks,

 

[j_harr]

Try:

Sub YahooSearch()
    Dim i As Integer, n As Integer, SearchString As String, ws As Worksheet
    Set ws = Sheets("Sheet1")
    n = ws.Range("A1").End(xlToRight).Column
    For i = 1 To n
        SearchString = Cells(1, i)
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://search.yahoo.com/search;_ylt=A2KK_foKYzdS7TQAH36bvZx4?p=" & SearchString & "&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-900" _
            , Destination:=Range("Sheet2!$A$1"))
            .Name = _
            "search;_ylt=A2KK_foKYzdS7TQAH36bvZx4?p=" & SearchString & "&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-900"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Sheets("Sheet2").Select
        ActiveWindow.SmallScroll Down:=159
        MsgBox "Hit OK when you wish to proceed to the next search item.", vbOKOnly, "Next Search"
    Next i
End Sub

I haven't tested it in full so make sure to run it in a copy of your workbook just in case. That said, I did the basic concept of selecting A1 and working right through the columns and building the URLs from that and it worked fine. The code grabs the string from the cell and then concatenates it in to the search URL.

 

[BennyBB]

Thanks. Works fine now.