Wrox Programmer Forums
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 1st, 2004, 10:38 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default Problem using MSHTML

Hi all,

I'm currently working on an application based in Excel's VBA which uses the little-documented MSHTML COM tlb. The app logs onto a webiste for me, navigates through various pages and then downloads certain information from the website and uploads the information into my spreadsheet.

The program used to run absolutely fine, looping through all the buttons on the download page until it found the desired download button and then telling the website that the button had been clicked by using htmlButton.Click(). However Windows XP SP 2 detects this sort of behaviour as programatic downloading, which is now specifically blocked. For various reasons I'd rather not modify my security settings such that this behaviour is no longer blocked.

So I came up with the idea of using Win API calls to move the mouse over the button and force a click (Interet Explorer can't distinguish this behaviour from user action and so this is not blocked). I have worked a solution where I position and size the Internet Explorer window on my screen such that I know which pixel location need to move the mouse to in order to find the download button. The problem is that the layout of the website might change.

I've had a look through the object browser and found the TransformPoint method of the IDisplayServices interface which would appear to give me the ability to get a run-time co-ordinate location of any webpage element. However, I can't initialise the IDisplayServices object and consequently can't use its methods. Could anyone point me in the right direction or suggest a better way round this?

NB the MSDN website seems to suggest I need to use the QueryInterface method on IHTMLDocument2 but I get a compile error: Interface method restricted when I try to use it.

Any help would be appreciated,
Old November 3rd, 2004, 08:04 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

I'm not sure if anyone cares but for completenesses sake I thought I'd post the answer to my earlier query.

It looks like the IDisplayServices interface can't be accessed when scipting with VBA - you have to be using C / C++. There were sevral more obstacles to getting this done but in answer to my specific query I needed to use the getBoundingClientRect mthod on the HTMLInputElement.

Below is some sample code which demonstrates the desired method by opening an internet explorer window, navigating to Google, putting something in the search box, finding the location of the search button, moving the move over the button & simulating a mouse click.

To get the code running in a VBA project you'll need to add references to Microsoft HTML Object Library (MSHTML) & Microsoft Internet Controls (SHDocVW).

Anyway, enjoy ...
Option Explicit

Private Type myRECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Private Enum CoordType
End Enum

' User32 API functions used to determine screen resolution
Declare Function GetDesktopWindow Lib "User32" () As Long
Declare Function GetWindowRect Lib "User32" (ByVal hWnd As Long, rectangle As myRECT) As Long

' User32 API Mouse functions
Private Declare Sub mouse_event Lib "User32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

' Mouse Event Flags

Sub Test()

Dim IExp As SHDocVw.InternetExplorer

Dim hDoc As MSHTML.HTMLDocument
Dim hCol As MSHTML.IHTMLElementCollection
Dim hInp As MSHTML.HTMLInputElement
Dim hPoint As MSHTML.tagPOINT

    Set IExp = New SHDocVw.InternetExplorer
    IExp.Visible = True
    IExp.navigate "http://www.google.co.uk"

    Do Until IExp.Busy = False

    Set hDoc = IExp.document

    ' Find the "search for" input box on the page
    Set hCol = hDoc.getElementsByTagName("input")
    For Each hInp In hCol

        If hInp.Name = "q" Then
            hInp.Value = "Test" ' Put in something to look for
            Exit For
        End If

    Next hInp

    ' Find the search button on the page
    Set hCol = hDoc.getElementsByTagName("input")
    For Each hInp In hCol

        If hInp.DefaultValue = "Google Search" Then

            ' Scroll IE to top left hand corner
            hDoc.parentWindow.scroll 0, 0

            ' Get coordinate of button
            hPoint = GetCoord(hDoc, hInp, Absolute)

            ' Move mouse
            mouse_event MOUSEEVENTF_ABSOLUTE + MOUSEEVENTF_MOVE, hPoint.X, hPoint.Y, 0, 0

            ' Simulate click
            mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
            mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

            Exit For

        End If

    Next hInp

End Sub

Private Function GetCoord(hDoc As MSHTML.HTMLDocument, hEle As MSHTML.HTMLInputElement, Output As CoordType) As MSHTML.tagPOINT

Dim ScreenRes As MSHTML.tagPOINT
Dim Point As MSHTML.tagPOINT

    ' Get the screen resolution
    ScreenRes = GetScreenResolution

    Set hIRectEle = hEle.getBoundingClientRect

    ' Find middle of input element
    Point.X = (hIRectEle.Left + hIRectEle.Right) / 2
    Point.Y = (hIRectEle.Top + hIRectEle.Bottom) / 2

    ' Add in offset for where the internet explorer window is located on screen
    Point.X = Point.X + hDoc.parentWindow.screenLeft
    Point.Y = Point.Y + hDoc.parentWindow.screenTop

    ' Convert to absolute coords, if necessary
    If Output = Absolute Then
        Point.X = (Point.X / ScreenRes.X) * 65000
        Point.Y = (Point.Y / ScreenRes.Y) * 65000
    End If

    GetCoord = Point

End Function

Function GetScreenResolution() As MSHTML.tagPOINT

Dim R As myRECT
Dim hWnd As Long
Dim RetVal As Long

    ' Win API calls
    hWnd = GetDesktopWindow()
    RetVal = GetWindowRect(hWnd, R)

    GetScreenResolution.X = (R.Right - R.Left)
    GetScreenResolution.Y = (R.Bottom - R.Top)

End Function

Similar Threads
Thread Thread Starter Forum Replies Last Post
mshtml - how to get images baburman C# 4 June 9th, 2006 06:18 AM
Problems using MSHTML maccas Pro VB 6 2 February 15th, 2006 11:32 AM
How to use MSHTML in Visual Basic 6? raylynn Pro VB 6 1 November 18th, 2005 09:36 AM
mshtml COM script events daveyc Pro VB.NET 2002/2003 0 June 15th, 2005 01:44 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.