Web query for multiple webpages - shift down

**Yossarian1** · 10-27-2011, 03:21 PM

Hello everyone,
I am new to this type of programming, I am trying to download the historical data of yahoo economical calendar, for example:
http://biz.yahoo.com/c/ec/201142.html

I figured out how to download a year of data (for instance from page 201101 to page 201142) with the modified macro below, but I am stuck on a easier part: the tables are copied in the excel worksheet one right next to the other, instead I would like them to be copied one below the other, so I can sort them once it has finished.
How can I do that?
Thanks in advance

Sub Macro2()
'
' Macro2 Macro
'

'
 Dim StartPage As Long
  Dim StopPage As Long
  Dim I As Long
  
      StartPage = 201101
    StopPage = 201142
  
  For I = StartPage To StopPage
      
        PageIndex = Format(I, "000000.html")
    Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://biz.yahoo.com/c/ec/" & PageIndex, Destination:=Range("A2"))
        .Name = "201142_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingRTF
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Next I
End Sub

**JBeaucaire** · 10-27-2011, 03:31 PM

Try this:

Option Explicit

Sub ImportMacro()
Dim StartPage As Long
Dim StopPage As Long
Dim NextRow As Long
Dim I As Long
Dim PageIndex As String
Dim RootPath As String

RootPath = "URL;http://biz.yahoo.com/c/ec/"
StartPage = 201101
StopPage = 201142
  
    For I = StartPage To StopPage
        PageIndex = Format(I, "000000.html")
        With ActiveSheet.QueryTables.Add(Connection:=RootPath & PageIndex, _
            Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1))
            .Name = PageIndex
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingRTF
            .WebTables = "4"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    Next I
End Sub