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 General
    Replies: 4
    Last Post: April 17th, 2007, 04: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