Create urls to cycle through for Web Queries

[tmsmyer]

I am trying to write a macro that will create several web queries. The urls will be the something like "www.example.com/xxxx"

The xxxx will change depending on cell values. On sheet "numbers" I have a series of numbers that start on A2 and go down an x amount of cells

For instance:
A2=1234
A3=2345
A4=4578
A5=3497

the urls would be: www.example.com/1234, www.example.com/2345, etc.


My question is, how do I get a vba macro to cycle through each possible url and create a web query. The amount of numbers in Column A always change, so I would want the look to end when there is a blank cell.

 

[John_w]

Try this to get started. As well as the sheet named "numbers", it needs a sheet named "queries" on which the web queries are created and data imported.

Public Sub Web_Queries()

    Dim numberCells As Range, numberCell As Range
    Dim webQueryDestCell As Range
    Dim webQuery As QueryTable
    
    With Worksheets("numbers")
        Set numberCells = .Range("A2", .Cells(Rows.Count, "A").End(xlUp))
    End With
    
    Set webQueryDestCell = Worksheets("queries").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    
    For Each numberCell In numberCells
    
        With webQueryDestCell.Worksheet
            Set webQuery = .QueryTables.Add(Connection:="URL;http://www.example.com/" & numberCell.Value, Destination:=webQueryDestCell)
            With webQuery
                .WebSelectionType = xlEntirePage
                .WebFormatting = xlWebFormattingNone
                .Refresh False
            End With
            Set webQueryDestCell = webQueryDestCell.Offset(webQuery.ResultRange.Rows.Count, 0)
            'webQuery.Delete   'Optional - delete web query
        End With
        
    Next
        
End Sub

The code imports each entire web page into the "queries" sheet. To import the data for your specific URLs, use the macro recorder whilst creating a web query for a specific URL and incorporate the web query part of the code generated within the "With webQuery .... End With" block.