Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old April 19th, 2006, 08:19 AM
Registered User
Join Date: Apr 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default IE automation

yes.. i am automating internet explorer... can u help me..

1) i want to access the menubar of IE
2) there are a few hidden links.. means the hyperlinks are not listed in the form.links... i want to click them..
3) i want to click on the context menu..

can u guide me?


Reply With Quote
  #2 (permalink)  
Old April 19th, 2006, 11:53 AM
Friend of Wrox
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

I'm not sure what the Context menu is and as I mentioned in my previous e-mail you cannot directly interact with the menubar but there are other ways round. Unfortunately I've yet to find a decent resource on how to do this properly so all of this is stuff I've learnt via trail and error - there may be better ways of doing things.

The attached is a stripped down version of a bit of code I use to log onto internet banking (less passwords!). It demonstrates a lot of the tricks I've learnt on manipulating IE via VBA. You'll need to reference two libraries before this will work - they are MS HTML Object Library (MSHTML.TLB) and MS Internet Controls (shdocvw.dll). You can reference libraries by selecting Tools -> References... in the VBE.

Option Explicit

Sub RunAbbeyLogin()

Dim IE As SHDocVw.InternetExplorer

    ' Login in to the Abbey website
    Set IE = AbbeyLogin
    Set IE = Nothing

End Sub

Private Function AbbeyLogin() As SHDocVw.InternetExplorer

Dim IE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim bReturn As Boolean

    ' Open up Internet Explorer
    Set IE = New SHDocVw.InternetExplorer
    IE.Visible = True
    IE.navigate "https://myonlineaccounts2.abbeynational.co.uk/ffStatic/html/logon.html"

    ' Wait for page to load
    WaitForLoad IE

    ' Get the HTML Document
    Set htmlDoc = IE.document

    ' Log into the site
    Call Login(htmlDoc, IE)

    ' Return the Internet Explorer instance
    Set AbbeyLogin = IE

End Function

Private Sub Login(hDoc As MSHTML.HTMLDocument, IExp As SHDocVw.InternetExplorer)
' Logs into the first Login page
' using UserID and Password

Dim hCol As MSHTML.IHTMLElementCollection
Dim hInp As MSHTML.HTMLInputElement
Dim bReturn As Boolean

Const cstrPID As String = ""
Const cstrPasscode As String = ""
Const cstrRegNo As String = ""

    Application.Wait (Now + TimeValue("00:00:03"))

    ' Get the collection of all input elements
    Set hCol = hDoc.getElementsByTagName("input")

    ' Loop though each input element and find the Customer number input box
    For Each hInp In hCol

        ' Put the ID, passcode and registration number into the relevant boxes
        If hInp.ID = "pid" Then hInp.Value = cstrPID
        If hInp.ID = "passcode" Then hInp.Value = cstrPasscode
        If hInp.ID = "ern" Then hInp.Value = cstrRegNo

    Next hInp

    ' Loop though each input element and find the Submit button
    For Each hInp In hCol

        ' If its the Submit button then click it
        If hInp.ID = "dblclk" Then
            Exit For
        End If

    Next hInp

    ' Dereference our variables
    Set hCol = Nothing
    Set hInp = Nothing

    ' Wait for the next page to load
    WaitForLoad IExp

    ' Nagvigate to the account page
    bReturn = ClickLink("account number 0", hDoc, IExp)

End Sub

Private Function ClickLink(LinkText As String, hDoc As MSHTML.HTMLDocument, IExp As SHDocVw.InternetExplorer) As Boolean
' Simulates clicking a link with the input LinkText on the open webpage

Dim hCol As MSHTML.IHTMLElementCollection
Dim hAnch As MSHTML.HTMLAnchorElement
Dim Ans As Boolean

    Ans = False

    ' Get the collection of links on the page
    Set hCol = hDoc.getElementsByTagName("a")

    ' Find the one specifying which characters we need and pull them out
    For Each hAnch In hCol

        If InStr(1, hAnch.innerHTML, LinkText, vbBinaryCompare) > 0 Then
            Ans = True
            Exit For
        End If

    Next hAnch

    ' Dereference our variables
    Set hAnch = Nothing
    Set hCol = Nothing

    ' Wait for page to load
    If Ans Then WaitForLoad IExp

    ClickLink = Ans

End Function

Private Sub WaitForLoad(IExp As SHDocVw.InternetExplorer)
' Wait for page to load before continuing

Dim sngTime As Single

Const MaxTime As Integer = 10

    sngTime = Timer

    ' Wait until the webpage is doing something ...
    Do Until IExp.readyState <> READYSTATE_COMPLETE
        If Timer > sngTime + MaxTime And Left(IExp.StatusText, 4) = "Done" Then Exit Sub

    ' ... and then wait for it to finish
    Do Until IExp.readyState = READYSTATE_COMPLETE
        If Timer > sngTime + MaxTime And Left(IExp.StatusText, 4) = "Done" Then Exit Sub

End Sub
Reply With Quote
The Following User Says Thank You to maccas For This Useful Post:
dewey1973 (June 8th, 2010)
  #3 (permalink)  
Old October 3rd, 2006, 08:19 AM
Authorized User
Join Date: Sep 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts

can we automate selection of all items in list by just checking the checkbox?
can anyone help with code needed to do this


Reply With Quote
  #4 (permalink)  
Old June 8th, 2010, 03:43 PM
Registered User
Join Date: Jun 2010
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts

maccas... I could kiss you right now!

Your WaitForLoad function is the only code I've found online that actually works almost all of the time! (My macro hasn't run long enough for me to say it works 100% of the time, but so far so good!)

I am in your debt!
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
IE Automation jrogers Excel VBA 1 May 3rd, 2007 07:39 AM
automation ASCIIMAN Pro VB 6 1 January 29th, 2007 02:25 PM
automation! Khalifeh VB How-To 6 October 26th, 2006 01:45 PM
Excel Automation ameysun Pro VB 6 0 November 4th, 2004 02:55 AM
help with automation nicka Access VBA 1 December 12th, 2003 05:07 PM

All times are GMT -4. The time now is 09:10 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.