Solved

How to create a vba code to extract a string of data from the “View Source” code from an active IE window into a cell in excel?

Posted on 2015-01-14
12
246 Views
Last Modified: 2015-01-20
I need to create a vba code that will complete the following tasks:
1.      Simulate right clicking on the current IE window then select “View Source” and then copy it to the clipboard?
2.      Search for the first case of fixed text strings and extract the text between them
3.      Paste text in to cell A1

http://www.experts-exchange.com/
1st String: <title>”
2nd String: “</title>”
The result should be “Welcome to Experts Exchange”
0
Comment
Question by:kbay808
  • 6
  • 6
12 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
You can use a few objects in VBA that will grab the data for you.

In VBA set references to:
      MICROSOFT INTERNET CONTROLS
      MICROSOFT HTML OBJECT LIBRARY
      
      
Then you can use some code like this:
Sub GetWebTitle()

    Dim objHTML As HTMLDocument
    Dim objTitleElem As IHTMLElementCollection
    Dim objIE As InternetExplorer
    
    Set objIE = New InternetExplorer
    
    With objIE
        ' Enter in the website here
        .navigate "http://www.experts-exchange.com/"
        
        ' Don't show IE as we open it
        .Visible = False
        
        ' Pause while IE Loads
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
        
        ' Load the web page into an HTML Document
        Set objHTML = .document
    End With
    
    ' Look for the all instances of the tag
    Set objTitleElem = objHTML.getElementsByTagName("Title")
    
    
    ' Put the value from the first instance in cell A1
    Range("A1").Value = objTitleElem.Item(0).outerText
    
End Sub

Open in new window

0
 

Author Comment

by:kbay808
Comment Utility
I need the code to work with the active IE window.  The problem is that I’m working within a pane in an online tool and the web address does not change when I navigate through it.
0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
Sorry I forgot about the active window part when I posted my answer.  Below is some code that looks for an active IE window that matches the URL you want.

Now, you mention navigating through the page.  Are you navigating through the site and then want this code to grab the value or do you want something else?

Sub GetWebTitle()

    Dim objHTML As MSHTML.HTMLDocument
    Dim objTitleElem As IHTMLElementCollection
    Dim objIE As InternetExplorer
    Dim objShell As SHDocVw.ShellWindows
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    ' Look for the appropriate open IE Window
    For Each objIE In objShell
            If TypeOf objIE.Document Is MSHTML.HTMLDocument Then
            Set objIEDoc = objIE.Document
            If Not (objIEDoc Is Nothing) Then
                If objIEDoc.URL = "http://www.experts-exchange.com/" Then
                    blnFoundWebSite = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then
        ' Make sure IE is done loading the page
        Do Until Not objIE.Busy
            DoEvents
        Loop
        
        ' Load the web page into an HTML Document
        Set objHTML = objIE.Document
    End If
    
    ' Look for the all instances of the tag
    Set objTitleElem = objHTML.getElementsByTagName("Title")
    
    
    ' Put the value from the first instance in cell A1
    Range("A1").Value = objTitleElem.Item(0).outerText
    
End Sub

Open in new window

0
 

Author Comment

by:kbay808
Comment Utility
I’m navigating through a window pane within a website.  So as I navigate through the web address stays the same.
0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
OK.  You may need to describe in more detail (like step by step) on what process you manually use right now.  The code ideas I have above will essentially do what you posted in your main question above - they will take the source of the active open IE page and search for text by the xml marker.  To go much further, we need more details.
0
 

Author Comment

by:kbay808
Comment Utility
I could not get your code to work.  Please see the attached example file and screen shot to see what I’m looking at.  Here are the step by step directions that you asked for.

1.      Go to the Experts Exchange home page http://www.experts-exchange.com/
2.      Right click on the page and select “View Source”
3.      Search for the first instance of “<title>”
        a.      The result will be on line 16 “<title>Welcome to Experts Exchange</title>”
4.      I want to extract the string between the two “<title>” markers
Screen-Shot.JPG
Get-Title-Macro-Example.xlsm
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
I just downloaded the Excel document you posted and it ran on my machine.  It placed Welcome to Experts Exchange in Cell A1.

For this code to work, you must have Internet Explorer open, You must have Experts Exchange as the active tab and to receive the Welcome Message, you must be logged into EE.  That in essence does the steps you are trying to duplicate.  I will need more details on what the code is doing if it is not working.
0
 

Author Comment

by:kbay808
Comment Utility
I’m sorry, it is working.  I’m able to put the title of the webpage.  Is there a way to extract a string based on a pattern match?  I need to pull the ticket number from the below line in the source view.

Pattern: “TMZ[0-9]{8,0}”
var tpz_formTitle = "Update Incident Number TMZ00120123";

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
OK.  I would use RegEx and I see you already have the pattern defined (except you need to switch the 8 and zero.  Here is an example uploaded.
Get-Title-Macro-Example.2.xlsm
0
 

Author Comment

by:kbay808
Comment Utility
It looks like your code is looking for the pattern within the title.  At first I was asking for the title, thinking that I could easily modify it to look for the ticket number or get the whole line, but that was not the case.  I basically want the code to search the “View Source” data and pull the first case that matches the pattern.  I copied the top portion of the “View Source” data for you.  Please see below.  The ticket number is on the 3rd line from the bottom.
/*SET up global vars*/
    var tpz_browser = "IE";
    var isAccessible = false;
    var isPreview = false;
    var isHover = false;
    var isTelephonyUser = false;
    var isEssuser = false;
    var ddeServerName = "";
    var tpz_printTemplates = false;
    var tpz_dateformat = "1";
    var tpz_dateseparator = "/";
    var tpz_autosubmit = "true";
    var tpz_displayMode = "listdetail";
    var tpz_viewRecordList = true;
    if(tpz_displayMode === 'listdetail'){
      tpz_viewRecordList = true;
    }
    var tpz_viewType = "detail";
    var tpz_scForm = "IM.update.incident";
    var tpz_screenName = "apm.edit.problem"
    var tpz_login = "john.doe";
    var tpz_operator = "John Doe";
    var tpz_formTitle = "Update Incident Number TMZ00120123";
    var tpz_initialRow = "0";
    var smCollaborationCfg={

Open in new window

0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
Comment Utility
Ok.  We can still use RegEx to find what you need, but obviously I am still trying to understand exactly what you need.  A few questions.
Can there be more than one line with a ticket number in the view source data?  If so, do you just want the first line?
Does the line always start with var tpz_formTitle?
Do you just want the TMZ number or the entire line of text (i.e. "Update Incident Number TMZ00120123"?
If you want the preceding text, does it always start with "Update Incident Number"?
Is this section in the Header or Body of the HTML?

I took some assumptions and came up with this - maybe it will help-
Get-Title-Macro-Example.3.xlsm
0
 

Author Closing Comment

by:kbay808
Comment Utility
That worked perfect!!!
I do need more help finding additional strings, but I will ask it on a separate question so you can get additional points.  Thank you very much.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

12 Experts available now in Live!

Get 1:1 Help Now