none
Import contents from multiple web sources at once in excel RRS feed

  • Question

  • Hi!

    I have a question about importing data from web sources in excel 2010.

    I have a set of webpages links which I want to copy their contents to excel.

    [Edit: these webpages contain a table]

    The first URL is this:

    http://makoo.eu/ranking/main.php?event_id=77&rank=100

    Then the following URLs are all adding a hundred to the value at the end of the URL, for example:

    http://makoo.eu/ranking/main.php?event_id=77&rank=200

    http://makoo.eu/ranking/main.php?event_id=77&rank=300

    http://makoo.eu/ranking/main.php?event_id=77&rank=400

    Etc.

    Actually I open them one by one, ctrl+a and then paste it into an excel worksheet, but it takes a lot of time because the last URL have a value of 104500.

    Is there a way to import all URL's contents into excel at once?

    Thank you in advance.

    Thursday, October 13, 2016 1:24 AM

Answers

  • Given till 400. Just change the 4 of for=1 to 4 to get more pages.

    Sub GetQry()
    
        Dim sConnect As String
        Dim rng As Range
        Dim i As Long
        Dim oQT As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/ranking/main.php?event_id=77&rank="
        
        Worksheets.Add
        
        
        
        For i = 1 To 4
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i * 100, rng)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next i
    
    End Sub
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Thursday, October 13, 2016 4:49 AM
    Answerer

All replies

  • Given till 400. Just change the 4 of for=1 to 4 to get more pages.

    Sub GetQry()
    
        Dim sConnect As String
        Dim rng As Range
        Dim i As Long
        Dim oQT As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/ranking/main.php?event_id=77&rank="
        
        Worksheets.Add
        
        
        
        For i = 1 To 4
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i * 100, rng)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next i
    
    End Sub
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Thursday, October 13, 2016 4:49 AM
    Answerer
  • Thank you so much! It's working!
    Thursday, October 13, 2016 8:26 PM