getelementbytagname isn't recognized

Question

I'm attempting to have Excel open a web site, populate some fields, submit, and download the resulting data in a file.

My code never gets very far, however, because it looks like Excel doesn't recognize "getelementsbytagname" as an existing operation. I assume this is the issue because it does not correct the case to GetElementsByTagName like it does for everything else.

My References in the editor include Microsoft Internet Controls and Microsoft HTML Object Library. Is there another one that I need to activate?

The code is just a modified version of something found online.

Private Sub IE_automation()
'Retrieve data from Enterprise Reporting with IE
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

'Create Internet Explorer Object
Set IE = CreateObject("InternetExplorer.Application")

'Comment out while troubleshooting
'IE.Visible = False

'Send the form data to URL as POST binary request
IE.Navigate "http://corpprddatawhs1/Reports/Pages/Report.aspx?ItemPath=%2fInventory%2fInventory+By+Branch"

'Set statusbar
Application.StatusBar = "Webhost data is loading. Please wait..."

'Wait while IE loading
Do While IE.busy
    Application.Wait DateAdd("s", 1, Now)
Loop

'Find 2 input tags:
'   1. Text field
'   <input type="text" class="null" name="ct132$ct104$1ct105$txtValue" size="30" value="" />
'
'   2. Button
'   <input type="submit" value="View Report" />

Application.StatusBar = "Searching form submission. Please wait..."

Set objCollection = IE.document.getelementsbytagname("input")

Answer 1

My References in the editor include Microsoft Internet Controls and Microsoft HTML Object Library.

Then you have no need to do this:

Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

Set IE = CreateObject("InternetExplorer.Application")

Declaring everything As Object is a technique called late-binding. In that paradigm you create a new instance of an object by passing a ProgID to the CreateObject function.

Late-bound code is, by definition, resolved at run-time: the compiler is happy with the Object interface, and the onus is on you to use the correct members.

With late-bound code you can't get IntelliSense or auto-complete, because the compiler has no idea what you're up to: all it's seeing is Object.

When you actually reference a type library, you can use early binding, which means the types and member calls are resolved at compile-time instead of run-time. But for this to work, you need to use the types you're referencing. Otherwise you're late-binding against a type library that's referenced... and late-bound code doesn't require a reference (just that some version of the type library is registered on the machine that's running the code).

Dim browser As InternetExplorer
Set browser = New InternetExplorer

browser.Navigate url

'...

Dim dom As HTMLDocument
Set dom = browser.Document

Dim inputElements As HTMLElementCollection
Set inputElements = dom.getElementsByTagName("input")

And now you're coding against an HTML DOM just like you would against any Excel worksheet, with IntelliSense and parameter info and all the goodies.

This is suspicious:

Do While IE.busy
    Application.Wait DateAdd("s", 1, Now)
Loop

That wait-loop isn't even looking at the browser's ReadyState. Try this instead:

Do Until IE.ReadyState = 4 And IE.Busy = False
    DoEvents
Loop

See this post for an allegedly fail-safe way to go about it.

Your code otherwise looks fine though, so my money is on the wait-loop.

Answer 2

Even though VBA is case-insensitive, all the methods/properties under document are case-sensitive.

A safe way to call them with late-binding is to use CallByName:

Set items = CallByName(IE.document, "getElementsByTagName", VbMethod, "input")