VBA IE automation: can't access objects inside a frame: run-time error 438

[destructa]

Hi, long-time listener, first-time caller…

I have about 2 months’ experience programming with VBA. I just wanted to make Excel do a few repetitive tasks for me, and now I’m hooked. I have a whole new respect for the Office suite, now that I know it’s capable of so much. I have a hair-trigger Google finger, so believe me, I really tried to solve this on my own.

I am at my wits' end trying to build a VBA automation for IE. The company has an internal database website where I look up part numbers and get information. It was created with JavaScript. I'd like the macro to go down a spreadsheet and retrieve the information for each part automatically. It should go like this:
<CODE>
</CODE>

Sub SearchByKeyID()
'This project includes references to "Microsoft Internet Controls" and
'"Microsoft HTML Object Library" – really, it does.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
'Variable declarations
Dim myIE As New InternetExplorer
Dim urlFindItem As String
Dim myDoc As HTMLDocument<o:p></o:p>
Dim c As Range<o:p></o:p>
<o:p></o:p>
'Set starting URL and search string
urlFindItem = "<THE url page search the of>"
<o:p></o:p>
'Make IE navigate to the URL and make browser visible
myIE.Navigate urlFindItem
myIE.Visible = True
 
'Wait for the page to load
Do While myIE.Busy Or myIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
<o:p></o:p>
'Set IE document into object
Set myDoc = myIE.Document
Set c = ActiveCell
 
'Paste search string on form
myDoc.forms(0).txtName.Value = c.Value
<o:p></o:p>
'Submit form
myDoc.forms(0).submit
 
'Wait for the page to load
Do While myIE.Busy Or myIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop

<CODE></CODE>
Up to now, everything works fine. The sub pulls up the search page, inserts the part number in the correct field, and submits the search. Now, the search results have come up in the same window. I can click the link that comes up on the page, but couldn’t get it to click programmatically.

Using IE’s built-in Developer Tools to view the source code and hierarchy, I have found an object in there that has a very useful value. I can use this value to navigate straight to the page that contains the part number’s information—call it the “container” number. And, I found a neat trick where VBA can copy innertext from the current IE window (or object within the source code) and deposit it in a string variable.

Here’s what has me banging my head on the desk: I can’t capture that value. I can see it clearly with my eyes, but VBA can’t penetrate the frame. I’ve “walked the nodes” like childnodes(0), childnodes(0).childnodes(0), childnodes(0).childnodes(1) and so on, but every time I try to get into that frame I get “Run-Time error '438': Object Doesn't support this property or method”. I know the trick about setting each frame into its own HTMLDocument, but this hasn’t worked either. I’ve used the names of the objects, to no avail. Every time, I get the error.

Here is a shot of the hierarchy. What path do you think I can put in my code to extract the value of “emxTableRowId”?

http://www.flickr.com/photos/70412643@N06/6392134727/

I use Excel 2007, Windows 7, IE8...thanks for looking

 

[Norie]

Not sure what you mean by 'path', and I've never seen code that really uses the nodes in that way.

There are various ways to 'get' elements, here's 2.

getElementByID(id)

getElementsByTagName(tagname)

The first one can sometimes work with the element's name.

Both can be used with the document object.

As for the frame thing, you might not need to get a reference to it for the above to work.

There's other things you could try but without seeing a URL for the page it's hard to be specific.

PS What are the 'tricks' you mention? How didn't they work?

 

[destructa]

Hi Norie,

I'm afraid the database is company proprietary and requires password login, so I didn't want to post any identifying info...the urls are real hairballs, very long.

I forgot, I did try getElementById and getElementsByTagName, and got the same run-time error.

As for the tricks, I got the one to extract source code from here:
http://www.mrexcel.com/forum/showthread.php?t=342939&page=2

...but it didn't extract nearly all of the stuff I see in Developer Tools view. Probably defeated by tables & frames.

If you don't know a 'magic trick', I'll just have to come back after the holiday and try to getElements etc. again with a clear head. I may be too close to the forest to see the trees right now.

 

[Norie]

There are no 'magic tricks' that I know of.

How exactly did you try getelementbyid etc?

I can't recall the exact hierarchy but I think I would start something like this.

1 Get reference to frame.

2 Get reference to form in that frame.

3 Get reference to control you want in the form.

1 & 2 should be straightforward, for 3 a little work would be needed.

For that I'd probably get the collection of all input elements on the form and loop through them to fond the one I want.

Alternatively just try to grab a reference to the control using it's name via the form it's on.