Loading
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
    Posts
    6

    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

    Code:
    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
       
        PageForm.submit
       
        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
    Posts
    444

    Re: using VBA to open links in internet explorer

    See if this gives you any joy.
    Code:
    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

Bookmarks

Posting Permissions

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