Extract Web Page URL inmport to Excel

  • [SIZE=14px]Hi

    I have a Command Button on Sheet1 and a List of URL in Sheet2 Column A. When the command button is clicked my code takes the first URL from Sheet2 Column A, opens IE and then imports the URL from that page into Sheet1 Column A, then closes IE and deletes any duplicates, this bit is fine . I can't get it to do this : -

    1) I need the code to repeat but this time go to the next url in Sheet2 and do the same, the code does this until no more URL are left on Sheet2

    2) It pastes the new date in the next blank row, currently it over writes the previous data.

    [CODE[/SIZE]Private Sub CommandButton2_Click()

    'For url extraction
    Dim ie, items, elem As Object
    Dim i, j, k, l As Integer

    i = 2
    k = 2
    l = 2

    'Create IE, open it for limited time, get url from sheet
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate Sheets("Sheet2").Range("A1").Value
    Application.Wait (Now + TimeValue("00:00:5"))

    Do While ie.Busy Or ie.ReadyState <> 4


    For i = 0 To 500
    On Error Resume Next

    'Paste in sheet and column
    Sheets("Sheet1").Range("A" & i).Value = ie.document.getelementsbytagname("a").Item(i).innerText

    'Close IE Browser
    Next i
    ie.Visible = Quit

    'Deletes duplicates in column A
    Columns(1).RemoveDuplicates Columns:=Array(1)

    End Sub[SIZE=14px]][/CODE]

    I've been stuck on this for weeks, this is the best i can do. can some please help


    If I could VBA, life wouldn't be such a pain in the A$$, ;(;(

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!