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
285 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
[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
  • 6
  • 6
12 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40550544
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
ID: 40551468
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
ID: 40552744
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:kbay808
ID: 40556745
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
ID: 40556867
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
ID: 40557044
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
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40558639
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
ID: 40558731
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
ID: 40559012
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
ID: 40559309
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
ID: 40559792
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
ID: 40561392
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

729 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