macro web query

[DodgeyNY]

Hi guys,

I was wondering if you could help me out I want to import data into excel from a large number of tables which are on seperate webpages. The pages run in sequence up to 10000 (see below). What code do I need to add to the macro to add 1 to the id=00001 each time. Cheers.

http://www.example.com/quote_id=00001
http://www.example.com/quote_id=00002
http://www.example.com/quote_id=00003

and carry on up to id=10000?

Sub URL_Static_Query()
   
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://www.example.com/quote_id=00001", _
         Destination:=Range("a1"))
   
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

[Leith Ross]

Hello DodgeyNY,

This will format the number with the correct number of leading zeroes.

Sub URL_Static_Query()
   
  Dim Id As String
  Dim N As Integer

   N = 1
   Id = Format(Str(N), "0000#")

   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://www.example.com/quote_id=" & Id, _
         Destination:=Range("a1"))
   
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With

End Sub

Sincerely,
Leith Ross