Solved

VBA to Read data from IE Website

Posted on 2012-12-20
9
612 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
  • 5
  • 4
9 Comments
 
LVL 33

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 33

Expert Comment

by:Norie
ID: 38711954
What data do you want from the page?
0
 

Author Comment

by:Kaprice
ID: 38711961
the ones i circled in the screenshots.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 33

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 33

Expert Comment

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
This article offers some helpful and general tips for safe browsing and online shopping. It offers simple and manageable procedures that help to ensure the safety of one's personal information and the security of any devices.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. 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.: (CODE)
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 +…

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now