No announcement yet.

Unconfigured Ad Widget


using VBA to open links in internet explorer

  • Filter
  • Time
  • Show
Clear All
new posts

  • using VBA to open links in internet explorer

    Hi guys – I am at my wits end (3 or 4 days of on and off work on this!) in regards to doing some automation tasks in IE using Excel VBA.

    The website in question is a custodian (I work for a fund manager) where we download daily cash reporting.

    I need to login to the website, click a link to go to another page, then click another link to actually download the report.

    I have been successful in getting onto the website, entering username and password, submitting, clicking the link on the next page (the Favourites part as per below) but when it comes to the final link I am hitting a brick wall.

    The code I was using for attempting to click the 2nd Link was the same as the first but it didn’t seem to do anything at all (i.e using ielement wording/code). I note that I have the Public Sleep function to wait in between webpage loads for now, the Do While i.eBusy etc didn’t seem to work real well in that it didn’t actually wait until the next page was loaded. I am not fussed about that for now. I then changed it to try via a slightly different method (see below) but the same nil result.

    I have the Debug Print link.innertext in there just to verify the correct name and it comes out as per the “Cash Balances – Cash Balances” but nothing happens when I attempt to click it via the code.

    Any ideas? Code is below

    Option Explicit
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Public Enum IE_READYSTATE
        Uninitialised = 0
        Loading = 1
        Loaded = 2
        Interactive = 3
        complete = 4
    End Enum
    Sub LoadNCS()
    Dim CPwd As String
    Application.DisplayAlerts = False
        Const cURL = "xxx"
        Const cUserID = "xxxx"     'REPLACE XXXX WITH YOUR USER ID
        CPwd = Range("CurrentPassword")       'REPLACE YYYY WITH YOUR PASSWORD
        Dim ie As Object
        Dim doc As HTMLDocument
        Dim PageForm As HTMLFormElement
        Dim UserIdBox As HTMLInputElement
        Dim PasswordBox As HTMLInputElement
        Dim FormButton As HTMLInputButtonElement
        Dim IeDoc As Object
        Dim Elem As IHTMLElement
        Set ie = CreateObject("InternetExplorer.Application")
        ie.Visible = True
        ie.navigate cURL
        Do While ie.Busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop
        Set doc = ie.document
        Set PageForm = doc.forms(0)
        Set UserIdBox = PageForm.elements("UserName")
        UserIdBox.Value = cUserID
        Set PasswordBox = PageForm.elements("Password")
        PasswordBox.Value = CPwd
        Sleep 15000
    Set doc = ie.document
    Dim ieelement
    For Each ieelement In doc.links
    If ieelement.innerText = "Favourites" Then ieelement.Click
    Next ieelement
    Sleep 12000
    Dim link As Object
    For Each link In ie.document.links
    Debug.Print link.innerText
    Next link
    Sleep 12000
    Set doc = ie.document
    For Each link In ie.document.links
    If link.innerText = "Cash Balances - Cash Balances" Then link.Click
    Next link
    End Sub

  • #2
    Re: using VBA to open links in internet explorer

    See if this gives you any joy.
    ThisWorkbook.FollowHyperlink Address:= link




    There are no results that meet this criteria.