Query several internet pages with a loop

Gaston0327

New Member
Joined
Jul 1, 2012
Messages
2
Hi there,

I have the following macro which pulls several tables from an internet page.

Verb Macro
'
' Keyboard Shortcut: Ctrl+z
'
Sheets("Sheet5").Select
ActiveCell.Select
Sheets("Sheet5").Select
ActiveCell.Cells.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.verbs.cat/es/conjugacion/35", Destination:=Range("$A$1"))
.Name = "35"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3,4,5,8,9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub

What I need to know is how to create a loop so I may change the number in the URL (i.e. 35 to 36, 37, etc) and insert the table results in diferent cells.

Thanks in advance for your help !
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
As requested

Code:
Option Explicit
Sub Process()
    Dim wb As Workbook
    Dim I As Integer
    
    Dim SheetNo As Integer
    
    Set wb = ThisWorkbook
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
    
    SheetNo = 1
    For I = 35 To 37
        wb.Worksheets(SheetNo).Activate
        wb.Worksheets(SheetNo).Cells.Clear
        With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.verbs.cat/es/conjugacion/" & I, Destination:=Range("$A$1"))
            .Name = "QueryTable: " & I
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "2,3,4,5,8,9"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        SheetNo = SheetNo + 1
    Next I
End Sub
 

Forum statistics

Threads
1,089,578
Messages
5,409,110
Members
403,251
Latest member
BAMORAN

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top