Improve company productivity with a Business Account.Sign Up

Solved

VBA to Read data from IE Website

Posted on 2012-12-20

1 solution

Medium Priority

668 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

9 Comments

LVL 37

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 37

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 37

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 37

Expert Comment

by:Norie

ID: 387120492012-12-20

Glad you got it working.

LVL 37

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

Convert and format imperial distance (feet and inches) with high precision

Article by: Gustav

With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.

Copy Lotus Notes Calendar to Outlook Domain Without Any Hindrance

Article by: SysTools

This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.

Lists and Links

Video by: Mark

This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

How To Make a Graph with Microsoft Access

Video by: crystal

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…