Late binding of an IHTML element
I am trying to create a limited VBA binding project for web search. At some point I have the following code (early bound):
Dim currPage as HTMLDocument: Set currPage = objIE.document 'where objIE is set with Set objIE = CreateObject("InternetExplorer.application")
'(late bound as it is dim'd as Object)
Dim myDiv As HTMLDivElement: Set myDiv = currPage.getElementById("fbar")
Dim elemRect As IHTMLRect: Set elemRect = myDiv.getBoundingClientRect
'Scroll until bottom of page is in view
Do Until elemRect.bottom > 0
currPage.parentWindow.scrollBy 0, 10000
Set elemRect = myDiv.getBoundingClientRect
Loop
This code becomes this on late communication: (or so I thought)
Dim currPage as Object: Set currPage = objIE.document
Dim myDiv As Object: Set myDiv = currPage.getElementById("fbar")
Dim elemRect As Object: Set elemRect = myDiv.getBoundingClientRect
'Scroll until bottom of page is in view
Do Until elemRect.bottom > 0
currPage.parentWindow.scrollBy 0, 10000
Set elemRect = myDiv.getBoundingClientRect
Loop
The problem, I guess, lies in I
infront of IHTMLRect
, which MSDN tells me to designate an element on a web page that has no actual object associated with it - hence assigning it to undefined Object
just doesn't make sense in the code. (This is a complete guess)
Anyway, the early linked code works fine, the late linked code exits in elemRect.bottom
Why is this and how can I fix it?
source to share
Objects in VBA can implement multiple interfaces, and the methods / properties you can call depend on the interface you are using to access the object. Simple example:
' This means access the object via the IUnknown interface
' IUnknown is the interface from which all other COM
' interfaces inherit
Dim x As IUnknown
Set x = ThisWorkbook.Worksheets(1)
' Commented out as this won't compile because the
' Name property isn't defined in IUnknown
' MsgBox x.Name
' This means access the object through the default
' interface associated with the Worksheet object type
Dim w As Worksheet
Set w = x
' Now we can get to the name (same object, different interface)
MsgBox w.Name
In the case of MSHTML, I would assume that methods such as getElementById
return an interface similar to one of the versions IHTMLElement
. This means that methods / properties defined in the type's interface IHTMLDivElement
cannot be accessed.
IUnknown has a QueryInterface method that is used to access the various interfaces that the object implements. However, this cannot be called directly in VBA, as the VBA way of doing this is to use Dim
with the appropriate interface and then use Set
. This will only compile if the necessary links are set, which in turn defeats the goal of late linking.
There is a workaround using CallByName . To go back to the worksheet example, this works:
Dim x As IUnknown
Set x = ThisWorkbook.Worksheets(1)
' Commented out as this won't compile because the
' Name property isn't defined in IUnknown
' MsgBox x.Name
' Can get to the property via CallByName
MsgBox CallByName(x, "Name", VbGet)
For the MSHTML problem, this works (note the call type changed to VbMethod
):
Dim elemRect As Object: Set elemRect = CallByName(myDiv, "getBoundingClientRect",
VbMethod)
stTimer = Timer
'Scroll until bottom of page is in view
Do Until elemRect.bottom > 0 Or tElapsed > timeout 'timeout after n seconds
currPage.parentWindow.scrollBy 0, 10000
Set elemRect = CallByName(myDiv, "getBoundingClientRect", VbMethod)
tElapsed = Timer - stTimer
Loop
I know very little about COM objects, so there may be other problems that I have not considered
Complete code (adapted from your answer to another question ). Re-executing the function in quick succession throws errors due to IE taking time to shutdown (see this question for a similar issue). Reuse the same IE object if you need to make multiple requests in a row:
Option Explicit
Public Function GOOGLE_COUNT(searchTerm As String, xRes As Long, yRes As Long, Optional timeout As Long = 10) As Long
Dim url As String
Dim objIE As Object
Dim currPage As Object
Dim stTimer As Double, tElapsed As Single
Dim valueResult As Object
'create URL to page with these image criteria
url = "https://www.google.com/search?q=" & searchTerm & _
"&tbm=isch&source=lnt&tbs=isz:ex,iszw:" & xRes & ",iszh:" & yRes
'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = CreateObject("InternetExplorer.Application")
'Google images search
objIE.navigate url
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Set currPage = objIE.document
Dim myDiv As Object: Set myDiv = currPage.getElementById("fbar")
Dim elemRect As Object: Set elemRect = CallByName(myDiv, "getBoundingClientRect", VbMethod)
stTimer = Timer
'Scroll until bottom of page is in view
Do Until elemRect.bottom > 0 Or tElapsed > timeout 'timeout after n seconds
currPage.parentWindow.scrollBy 0, 10000
Set elemRect = CallByName(myDiv, "getBoundingClientRect", VbMethod)
tElapsed = Timer - stTimer
Loop
myDiv.ScrollIntoView
'Count the images
Set valueResult = currPage.getElementById("rg_s").getElementsByTagName("IMG")
GOOGLE_COUNT = valueResult.Length
objIE.Quit
End Function
Sub foo()
MsgBox GOOGLE_COUNT("St. Mary", 1366, 768)
End Sub
source to share