Consulting

Results 1 to 2 of 2

Thread: VBA to cycle through pre-defined webpages & click on a link

  1. #1

    VBA to cycle through pre-defined webpages & click on a link

    Hey all,

    Been having an issue where I have a number of pre-defined webpages saved into excel, and I would like VBA to scroll through them 1 by 1,and once loaded click on a link on each of the page, then repeat for the number of pages I have in the list.

    Issue is the pages are locked behind a logon ( I have already done a script that logs me into the site), then I need to click a specific link first to get me to the desired region (once off command), once this has happened I then just want to cycle through each weblink in the second tab of excel, and click on a specific link 1 by 1.

    The above would be preferable in IE while visible if possible

    The below code does the job, but I manually have to click the link, and then click the excel message box off for each page, but just want this to be automatic, and can't piece it together.

    Any help would be greatly appreciated. (New to this stuff)

    Sub Shortlist()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Dim wsSheet As Worksheet, Rows As Long, links As Variant, IE As Object, link As Variant
    Set wb = ThisWorkbook
    Set wsSheet = wb.Sheets("Sheet1")
    Set IE = CreateObject("InternetExplorer.application")
    Rows = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row
    links = wsSheet.Range("A1:A" & Rows)
    With IE
        .Visible = True
        For Each link In links
            .navigate (link)
            While .Busy Or .ReadyState <> 4: DoEvents: Wend
            MsgBox .Document.body.innerText
        Next link
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub

  2. #2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •