Solved

VBA to Read data from IE Website

Posted on 2012-12-20

1 solution

Medium Priority

653 Views

Last Modified: 2012-12-25

I'm working in MS Access 2010.

I've got code that opens an IE browser and navigates to a page.

Dim mobjIE As SHDocVw.InternetExplorer

Set mobjIE = New SHDocVw.InternetExplorer

  mstrPage = vNewValue
  mobjIE.navigate mstrPage

Open in new window

Now I want to read various elements on the page. I have iMacros but it's not an ideal solution for me so I'm trying to do it all from VBA.

But, iMacros does appear to be very good at grabbing element names/ids.

Here's what iMacros came up with:

TAG POS=4 TYPE=P ATTR=TXT:* EXTRACT=TXT
TAG POS=1 TYPE=SPAN ATTR=CLASS:style15 EXTRACT=TXT
TAG POS=1 TYPE=I ATTR=TXT:* EXTRACT=TXT
TAG POS=1 TYPE=SPAN ATTR=CLASS:style5 EXTRACT=TXT
TAG POS=2 TYPE=SPAN ATTR=CLASS:style5 EXTRACT=TXT
TAG POS=4 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=5 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=6 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=7 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=8 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=9 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT
TAG POS=10 TYPE=STRONG ATTR=TXT:* EXTRACT=TXT

' Loop Reviews
TAG POS=1 TYPE=SPAN ATTR=CLASS:style11 EXTRACT=TXT
TAG POS=1 TYPE=IMG ATTR=SRC:*/images/star*.gif EXTRACT=HTM
TAG POS=1 TYPE=FONT ATTR=* EXTRACT=TXT

Open in new window

Apparently, POS indicates WHICH element it is.

Is that enough info to help me come up with VBA code that will read the text in those elements and store in a variable?

Comment

Question by:Kaprice

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 34

Expert Comment

by:Norie

ID: 387118712012-12-20

I don't really think that information like that is much use.

Do you know what elements/data you want from the page?

Can you post the URL for the page?

Author Comment

by:Kaprice

ID: 387118872012-12-20

Here's the page:

http://www.zmagic.com/@test/report.htm

I've circled the elements I want to capture

I've circled the elements I want to capture. For the stars, I want to know the image name.

LVL 34

Expert Comment

by:Norie

ID: 387119542012-12-20

What data do you want from the page?

Author Comment

by:Kaprice

ID: 387119612012-12-20

the ones i circled in the screenshots.

LVL 34

Expert Comment

by:Norie

ID: 387119742012-12-20

Right, I see now - had trouble opening the image files for some reason.

Accepted Solution

by:Kaprice

Kaprice earned 0 total points

ID: 387120102012-12-20

This works for everything except the image (stars):

  
  Dim oItem As Object
  Dim intPos As Integer
  Dim strTag As String
  Dim strAttr As String
  Dim strAttrValue As String

  intPos = 1
  strTag = "I"
  strAttr = "Txt"
  strAttrValue = ""

  With mobjIE.Document

    For Each oItem In .getElementsByTagName(strTag)
      If "|" & oItem.getAttribute(strAttr) = "|" & strAttrValue Then
        ctr = ctr + 1
        If ctr = intPos Then
          Debug.Print oItem.innerText
        End If
      End If
    Next
  End With

Open in new window

The iMacros data did, indeed, provide the what I needed.

intPos is from POS
strTag is from TYPE
strAttr is from ATTR
strValue is from after the "=" after ATTR (i.e. "style15")

in the case of the empty TXTs, I set to "" and add the pipes to the IF statement.

I'll convert these variables to parameters so it will work with all cases.

LVL 34

Expert Comment

by:Norie

ID: 387120492012-12-20

Glad you got it working.

LVL 34

Expert Comment

by:Norie

ID: 387120502012-12-20

Glad you got it working.

Author Closing Comment

by:Kaprice

ID: 387198912012-12-25

I figured it out even though the expert said the iMacros info would not help.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recovering from an AutoNumber Disaster

Article by: Helen

AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.

Microsoft Access

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

Copying Site's CSS Using Chrome Developer Tools

Video by: AnnieCushing

This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.