Excel VBA Get Internet Explorer Data

[d4vem]

Hi All,

I have researched this problem endlessly without success so without expecting someone to do all the donkey work to resolve. I have tried lots of different methods but still arrive back to the initial issue. (So gladly share my experiences).

Objective
Extract prices (in this case Coffee retail prices) from retailers to C&C against (1) Coffee exchange Index prices
(2) Effect on subsquent retail prices

Issue
In the attachment there are two macros extracting data from Asda & Tesco (they are the same) the URLs are next to each button.
The URL's are OK when you copy and paste into Internet Explorer.
Asda is returning no data but Tesco is working fine. I have looked into HTML frames etc with no success.

Current Solution
Workaround is to do an Excel web query for Asda which is fine, but (and its happened) the URL may change and that means editing each of the queries (100+) where as updating a static list and dynamically running one macro based on content of table is fair more efficient.

Any assistance would be much appreciated, and I will gladly share any research I have already conducted.

Regards

David

[Svet B.]

It seems the page causing you troubles just doesn't have .innerText; my suggestion is to try .innerHTML instead.

[d4vem]

Does not work either, and returns source data not displayed data on web page.

[Svet B.]

Sorry. I'm afraid I didn't pay enough attention to your problem. Here we go...

The page has frames! This is why you cannot get the .innerText. You need 1 more step to get there.

1. Declare 2 more variables:
Dim frameMain As Object, frameDoc As Object

2. After you get the IE doc:
Set ieDoc = ie.Document

you need to get the frame you are looking for, 0 = 1st frame
Set frameMain = ieDoc.frames.Item(0)
Set frameDoc = frameMain.Document

3. Then you can read it
Data = frameDoc.body.innerText

4. It's a good idea to clear all objects when done like this:
Set ie = Nothing

Hope this helps.

[d4vem]

Thanks for the response. The problem I have is identifying the frame data. If you look on the URL its the various products that I cant get,

David

[Svet B.]

David,

I'm not quite sure what do you mean. If getting the correct frame is the problem, this is easy. With

Set frameMain = ieDoc.frames.Item(0)

I just gave an example how to get to a frame. In this particular case the frame I believe you are looking for is the 2nd one, so the code would be

Set frameMain = ieDoc.frames.Item(1)

and then again

Set frameDoc = frameMain.Document
Data = frameDoc.body.innerText

If you want to click on separate items to display the full details then it's another story. It's doable, too. I'm just not sure what do you need.

Svet

[d4vem]

You are superstar thanks much appreciated will let you know if data validates from macro to URL

Thanks again

David

[Svet B.]

Welcome!