Solved

VBA to Read data from IE Website

Posted on 2012-12-20
9
641 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?
0
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
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 38711871
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?
0
 

Author Comment

by:Kaprice
ID: 38711887
0
 
LVL 34

Expert Comment

by:Norie
ID: 38711954
What data do you want from the page?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Kaprice
ID: 38711961
the ones i circled in the screenshots.
0
 
LVL 34

Expert Comment

by:Norie
ID: 38711974
Right, I see now - had trouble opening the image files for some reason.
0
 

Accepted Solution

by:
Kaprice earned 0 total points
ID: 38712010
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.
0
 
LVL 34

Expert Comment

by:Norie
ID: 38712049
Glad you got it working.
0
 
LVL 34

Expert Comment

by:Norie
ID: 38712050
Glad you got it working.
0
 

Author Closing Comment

by:Kaprice
ID: 38719891
I figured it out even though the expert said the iMacros info would not help.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Google currently has a new report that is in beta and coming soon to Webmaster Tool accounts. This Micro Tutorial will highlight new features for Google Webmaster Tools.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question