Solved: Automating IE in Excel with VBA No Document?

[myers_co]

I am trying to catch some data off a webpage that once opened, the Locals window in VBA is saying Document - <No Variables> so I can't get any elements or even see the "all" collection.

I've run across this a couple of times and can't figure out how to deal with it. Any help?

Here's the code:

Sub ieLookup()
Dim ieApp As Object
Dim myCollection As Collection

Set ieApp = CreateObject("InternetExplorer.Application")
On Error GoTo errHandler
ieApp.Visible = True
ieApp.navigate "xyz"
With ieApp
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
End With

Set myCollection = ieApp.Document.all
errHandler:
MsgBox "Error: " + Err.Number
ieApp.Quit
Set ieApp = Nothing
Set ieDoc = Nothing
End Sub

I'm new enough here that until I've had more posts, I can't post a link so just replace the "xyz" with www dot officedepot dot com/a/products/144132/1-Key-Tag-1-Sided-Printing/

[stanl]

you might try

Set myCollection = ieApp.Document.Body.CreateTextRange(HTMLText)

then parse for what you need, or

Set myCollection = ieApp.Document.Body.all("element Name")

where "element name" can be something like "iref1".

You may discover this url updates elements with JSON rather than re-drawing new pages each time something is clicked... that is my guess looking at the class and DIV code in the HTML, but just .02

Stan

[myers_co]

you might try

Set myCollection = ieApp.Document.Body.CreateTextRange(HTMLText)



This got me a "Type Mismatch"




then parse for what you need, or

Set myCollection = ieApp.Document.Body.all("element Name")

where "element name" can be something like "iref1".

This got me a "Object Required". I think this error is still relative to the fact that there is no document under ieApp when it is instantiated.

I'm getting really frustrated with this and am starting to consider maybe going another route for automation because the object class "InternetExplorer" is giving me poor performance as far as consistent results go.

[stanl]

Your problem is

Dim myCollection As Collection

try just Dim myVar, then

myVar = ieapp.document.body.CreateTextRange etc..

That that gives back is a string, not a collection. A collection is more like

oCollection = ieapp.document.body.GetElementsByTagName("A")... then you could loop through each item in the collection and extract the href tag - as an example.

[myers_co]

Stan,

I must have something set wrong. Maybe if you take a look at the skeleton that I've attached, you might be able to see what I am seeing.

Open up test.xlsm, open up the Visual Basic editor and step through the code. I would expect that when you hit where the message box would come up, you should see in the treeview of the LOCALS window ieApp which you expand and should see Document under that. When you click to expand Document, I would think you would see all of it's members. Instead, I only see <No Variables>. I've pasted the screen shot into the spreadsheet so you can see what I mean.

[stanl]

The issue is the url is a dynamic site, (probably populated from JSON) into numerous DIV's - therefore, my original thought about the document body was incorrect. I ran a simple script which iterated the document DIVs -



cHlinks = ieApp.Document.Body.GetElementsByTagName("DIV")
If
cHlinks
cLinks = ""
For z = 0 To
cHlinks.length-1


'etc...


Next


Endif


In the loop I looked for all DIVs with an ID attribute and echoed out

1. how you would reference the DIV in by item number
2. the ID
3. The innerHTML for the id

I attached this as a text file. Working with dynamic urls involves more than a simple macro. If you really,really want to see what goes on behind the scenes on this URL open it witf Firefox (with Firebug installed). Yes, I know, you can't automate Firefox with VBA... but it does give you clues for how to handle the URL data in IE.

Stan

[myers_co]

Thanks for continuing to look into this for me but I still can't understand why you are able to see the document object and I cannot. Perhaps I've picked on the wrong example to hone skills with.

[stanl]

Thanks for continuing to look into this for me but I still can't understand why you are able to see the document object and I cannot. Perhaps I've picked on the wrong example to hone skills with.

Might be something as simple as having to set a reference in your VB Editor. IMHO you picked a good example of the direction browser classes are heading.