Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA to Read data from IE Website

Posted on 2012-12-20
9
Medium Priority
?
661 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 35

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 35

Expert Comment

by:Norie
ID: 38711954
What data do you want from the page?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

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

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 35

Expert Comment

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
How to create a custom search shortcut to site-search Experts Exchange using Google in the Firefox browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch your Bookmark Menu: Press 'Ctrl +…

604 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