Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: using VBA to open links in internet explorer

  1. #1
    Join Date
    22nd November 2011

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    4th July 2011

    Re: using VBA to open links in internet explorer

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

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Open File Not In Internet Explorer
    By whisperinghill in forum EXCEL HELP
    Replies: 4
    Last Post: April 17th, 2007, 05:44


Posting Permissions

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