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-21
126
278 Views
Last Modified: 2016-02-11
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.      Run a pattern search for the below strings
         a.      Each line will always look exactly the same with the except for the target data and never duplicates
3.      Extract the string within the “” into the corresponding cell in the example file
         b.      Desired string is marked with xxxxxxxxxx
         c.      The target data will range in length

Search 1:
" value="xxxxxxxxxx" scripttype="text"></div><img

Search 2:
" value="xxxxxxxxxx" scripttype="text"></div></div><span id="X184" type="label"

Search 3:
 " value="xxxxxxxxxx" scripttype="text"></div></div><span id="X192" type="label"

Search 4:
" value="xxxxxxxxxx" scripttype="text"></div></div><div id="X196Border" MyAttrib="EditBorder"

Search 5:
" value="xxxxxxxxxx" scripttype="text"></div><img id="X94Mandatory" src="images/mandatory.gif"

Search 6:
height:19px;text-align:left;"><label for="X70">Affected CI:</label></span><input type="hidden" id="X70Hidden" value="xxxxxxxxxx"

Search 7:
t;]/instance[1]/depend.call','xxxxxxxxxx','8');tpzStoreFocusValue('references/model[@id="vj.n
IE-Pattern-Search-Example.xlsm
0
Comment
Question by:kbay808
  • 54
  • 40
  • 31
  • +1
126 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40562051
I assume this is related to another one of your questions Here.  If you have other related questions, you may want to post some links so that we can get a fuller picture.

I am little confused as to exactly for what we need to search.  Are we looking for all numbers that follow the word Value= or do we need the specific text that follows each one.  If so is that specific text always the same?  Does it only show once?  How about for search #7, is that text string only shown once or if it is multiple times do you want each instance?
0
 

Author Comment

by:kbay808
ID: 40562508
This is related to that other question.  I am looking for the value found in the place of xxxxxxxxxx.  The string before and after the xxxxxxxxxx's are fixed.  I should of used "*" instead of all of the x's.  The value will be a mix of letters and or numbers.

For example
Search 1:
 " value="xxxxxxxxxx" scripttype="text"></div><img

Fixed values:
" value="
" scripttype="text"></div><img
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40563465
Ok.  I would still use RegEx, but it gets more complicated since VBA does not have a lookbehind feature for RegEx.  You end up needing to do two searches for each string.  See the attached which has some of the code from the previous questions.  Hopefully this heads you in the right direction.

I made an assumption that the xxxxxx string will be between 1-9 characters.  If that is incorrect, you will need to update the RegEx pattern.
IE-Pattern-Search-Example.2.xlsm
0
 

Author Comment

by:kbay808
ID: 40563636
I’m getting a compile error.  Please see the attached screen shot for details.  I also listed below in detail the string lengths, character types and an example for each search.  For troubleshooting purposes, it may be easier if we limited the macro to just 2 searches.  One that searches for the “value” and either search 6 or 7.

Search 1:       1-100 characters                       Any character type      
Search 2:       0-50   characters                       Any character type      
Search 3:       14 characters (Fixed)      Phone number                                       (555) 555-5555
Search 4:       4 characters   (Fixed)      All letters                                                        ABCD
Search 5:       10 characters (Fixed)      2 letters, 8 numbers                                       AB00009531
Search 6:       15 characters (Fixed)      6 letters, 6 numbers, 0-3 letters      ABCDEF015265 or ABCDEF015265GHI
Search 7:       11 characters (Fixed)      3 letters, 8 numbers                                       ABC04168970
Compile-error-screen-shot.JPG
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40563663
NO POINTS FOR THIS

You need an

End If

before each of the Problem Else statements


***** Edited *****
0
 

Author Comment

by:kbay808
ID: 40563717
That fixed the error and the macro runs through, but was unable to return with any data.  They all return with “Not Found”.  Could you please review the below lines and tell me how I can refine the search to search for [A-Z]{4}?  I also included the actual line from the “View Source” file.  I'm not sure if it matters, but there is a space between " and value at the beginning.

' From View Source file (Need to capture "WNYD" and it will always be 4 tetters)
" value="WNYD" scripttype="text"></div></div><div id="X196Border" MyAttrib="EditBorder" style="position:absolute;


' Your code
objRegEx.Pattern = "value\=""" & "\w{4,}""" & " scripttype\=""" & "text""" & "></div><div id=""" & "X196Border""" & " MyAttrib=""" & "EditBorder"""
objRegEx2.Pattern = """" & "\w{4,}"""

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40564187
Sorry about missing the End If's  - I took my working code and tried to cut and paste and missed a line.

The problem you are having now is that the text you are searching for does not match the text you want to find.

Here is what you want to find:
" value="WNYD" scripttype="text"></div></div><div id="X196Border" MyAttrib="EditBorder" style="position:absolute;
Here is what you are searching for
" value="WNYD" scripttype="text"></div><div id="X196Border" MyAttrib="EditBorder" style="position:absolute;

Notice the extra "</div>" in the first line.  You have to make sure the pattern matches.  To change to only finding upper case letters, just swap [A-Z] for \w.  Therefore update the code to:
    objRegEx.Pattern = "value\=""" & "[A-Z]{4,}""" & " scripttype\=""" & "text""" & "></div></div><div id=""" & "X196Border""" & " MyAttrib=""" & "EditBorder"""
    objRegEx2.Pattern = """" & "[A-Z]{4,}"""

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40564236
Now to update for your specific search criteria.  Note that a \w searches for any letter or any number.  When you mention any character type are you just wanting letters or numbers or could some other characters appear?

Assuming you just want letters and numbers, try this version.


By the way here are some links for reading up on RegEx:
http://msdn.microsoft.com/en-us/library/ms974570.aspx
http://msdn.microsoft.com/en-us/library/1400241x%28VS.85%29.aspx
http://www.regular-expressions.info/
IE-Pattern-Search-Example.3.xlsm
0
 

Author Comment

by:kbay808
ID: 40582183
Sorry for the long delay.  I could not get the macro to work.  Would it be easier to extract the entire line base on the beginning or end of the string?  I tried using the "^" and the "$", but I could not get it to work.  Below is the end of string from search 1 and the begging of the string from search 6 in the previous example file.

'End of string
scripttype="text"></div><img 


'Start of string
 height:19px;text-align:left;"><label for="X70">Affected CI:</label></span><input type="hidden" id="X70Hidden" value="

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40582347
I reran my tests and they all still work.  I suggest posting the entire View source document if you can (You may need to remove any confidential information) so that I can see what is different.
0
 

Author Comment

by:kbay808
ID: 40582692
Here is the View source document that you requested.  In the document I highlighted the data that I'm trying to extract and on the IE-Pattern-Search-Example.4 spreadsheet, I have the target data in the corresponding cells.
IE-Pattern-Search-Example.4.xlsm
Source.docx
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40583258
Thanks for the sample.  It really helps.  I see a few problems happening.  On search #1 there is a match with this line (589):

 " value="SD00000123" scripttype="text"></div><img

I had made the assumption we only want the first match and that line is the first match.  Therefore SD00000123 will be put into the Excel spreadsheet.  We need to know the logic to use to make the code skip this line.  Since I don't enough details about your project, I cannot code this.  I need to know how much will the source change?  Since this is kind of a screen scrape where we are pulling data based on position, it could always changed and any moment and break the code.  Will the line you want always be the second match on this type search?  Will the match always be letters only?  If so we could change the RegEx Pattern.

For the second search you had listed 0-50 characters, but a space is a special character in the RegEx search.  You need to update that RegEx (Any others that might include a space?)
        objRegEx.Pattern = " value\=""" & "[\w\s]{0,50}""" & " scripttype\=""" & "text""" & "></div></div><span id=""" & "X184""" & " type=""" & "label"""

Open in new window


I had a problem on Search #6 as I had it looking for 6 letters, 6 numbers, 3 letters and I see now that you wanted 0-3 at the end.  Please update that pattern to:
        objRegEx.Pattern = "height:19px;text-align:left;""" & "><label for=""" & "X70""" & ">Affected CI:</label></span><input type=""" & "hidden""" & " id=""" & "X70Hidden""" & " value=""" & "[A-Z]{6}[0-9]{6}[A-Z]{0,3}"""

Open in new window


On search #7 I had an extra space in the search criteria.  It should be:
        objRegEx.Pattern = "t\;\]\/instance\[1\]\/depend.call','[A-Z]{3}[0-9]{8}','8'\)\;tpzStoreFocusValue\('references\/model\[\@id=\&quot\;vj.n"

Open in new window

0
 

Author Comment

by:kbay808
ID: 40590208
I updated the file, but I still was not able to pull and data.  It recognizes the website and runs without any errors, but no data was extracted.  I'm guessing that some of the objRegEx2.Pattern lines needs to be updated as well.  I tried modifying it where it looked like matching objRegEx1.Pattern without any success.  Could you please look at the attached file and tell me what you think?

As for the first search, " value="SD00000123" scripttype="text"></div><img", your assumption is correct.  I do want the first one.
IE-Pattern-Search-Example.4.xlsm
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40590326
You are correct in that some of the objRegEx2.Pattern needed to be updated.  Try this file.
IE-Pattern-Search-Example.5.xlsm
0
 

Author Comment

by:kbay808
ID: 40595237
I still cannot get any of the searches to work.  I was able to search and find all of the data manually in all of my tests.  Could there be another factor at play?  Also, could you modify the code to copy the file to the clipboard so that I can paste it into a word document to see if there are any changes and to verify its looking at the entire document?

Other potential factors to consider:
I'm running
Excel 2010
Windows 7

References:
VISUAL BASIC FOR APPLICATIONS
MICROSOFT EXCEL 14.0 OBJECT LIBRARY
MICROSOFT HTML OBJECT LIBRARY
MICROSOFT INTERNET CONTROLS
MICROSOFT VBSCRIPT REGULAR EXPRESSIONS 5.5

Manual searches that work 100% of the time:
" scripttype="text"></div><img
" scripttype="text"></div></div><span id="X184" type="label"
" scripttype="text"></div></div><span id="X192" type="label"
" scripttype="text"></div></div><div id="X196Border" MyAttrib="EditBorder"
" scripttype="text"></div><img id="X94Mandatory" src="images/mandatory.gif"

height:19px;text-align:left;"><label for="X70">Affected CI:</label></span><input type="hidden" id="X70Hidden" value="

','8');tpzStoreFocusValue('references/model[@id=&quot;vj.n
0
 

Author Comment

by:kbay808
ID: 40595251
Also running IE10
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40595964
Does it put the words "Not Found" for the results on each search or does it show nothing?
0
 

Author Comment

by:kbay808
ID: 40595973
The results are all "Not Found"
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40596108
Ok.  So it is finding the document and loading it.  Just not finding the text.  As you have asked, I updated the file to allow you to save the source.  It saves it in two parts, the outerHTML and the innerHTML.  The code is using the innerhtml right now.
IE-Pattern-Search-Example.6.xlsm
0
 

Author Comment

by:kbay808
ID: 40596234
Both of the files are very small compared to the source file and its missing all of the information that I’m looking for.  I attached a copy of both of the files for you review.
Script.InnerHTML.txt
Script.OuterHTML.txt
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40596431
Looking at those files compared with the view source data you posted earlier it looks like the code is pulling from a different web page.  Without being able to run the code against the actual web page, I am at a loss for the next steps.    [It pulls what I expect on the web sites I test]  You can request attention to see if another expert see something I am missing.
0
 

Author Comment

by:kbay808
ID: 40597710
I can confirm that it’s pulling form the same webpage.  The trouble ticket number can be found in both files that your code created.  I found some code that does pull the entire source code.  I tested your code and the code below on the website http://www.mrexcel.com/.  The 2 files created by your code were missing data, but the code below was able to pull the entire source code.  My thought process is that if you were able to modify your code to extract the entire source code like the one below, then all of your searches should work.

Sub ImportHTMLSource()
    Dim FileName As String
    Dim FileNum As Long
    Dim Sh As Worksheet
    FileName = "C:\Temp\Source.txt"
    FileNum = FreeFile
    Open FileName For Output As FileNum
    Print #FileNum, GetSource("http://www.mrexcel.com")
    Close FileNum
    Set Sh = Worksheets.Add
    With Sh.QueryTables.Add(Connection:="TEXT;C:\TEMP\Source.txt", Destination:=Range("A1"))
        .Name = "Source"
        .AdjustColumnWidth = True
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileColumnDataTypes = Array(2)
        .Refresh BackgroundQuery:=False
    End With
End Sub

Function GetSource(sURL As String) As String
'   Tim Williams
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", sURL, False
    oXHTTP.send
    GetSource = oXHTTP.responsetext
    Set oXHTTP = Nothing
End Function

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40598590
That is another way to pull the data but it creates it's own connection to the Web Page and does not use the information on the active web page.  That is why I did not use that concept.  I will test on the mrexcel site tonight when I have more time to see if I can come up with options for pulling from the active site.  We can pursue the other option, but we would have to be able to determine the appropriate URL to display the values that you wanted displayed.  In other words if you need to see a particular ticket number does the address bar show that ticket number in the bar?  Can you create a URL that will navigate directly to the screen you want displayed?  If so, then we can covert the code to the HTTP style.  (For an example notice the address bar for EE - it includes the Question number in the URL.)
0
 

Author Comment

by:kbay808
ID: 40599491
The URL is https://servman/sm/index.do.  Unfortunately the URL does not change as you navigate to the different tickets.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40599981
I took a look at my code again and ran it against the Mr Excel Website.  The only thing I can see missing is the HTML header which is expected.  I have not put that in your code as the sample source has the values in the body.  However, I have updated your sheet to export that just in case it really shows up there.
IE-Pattern-Search-Example.7.xlsm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40600064
Since you already have a reference to the HTMLDocument, why not just get the values by their ID's?  For instance you can get the values in your sample searches numbered 2 thru 6 this way:
strValue2 = objIE.Document.GetElementByID(“X183”)
strValue3 = objIE.Document.GetElementByID(“X191”)
strValue4 = objIE.Document.GetElementByID(“X195”)
strValue5 = objIE.Document.GetElementByID(“X94”)
strValue6 = objIE.Document.GetElementByID(“X70Hidden”)

Open in new window


You can easily get many of the ID’s by using the Developer Tools found on most browsers.  It’s a simple matter of clicking the Identification tool and then selecting the element on the page that you want to identify.  Obviously you can’t do this for hidden elements so that’s when you’d look through the source code

Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40600082
I checked around and teylyn suggested taking a step back and using the built in Web Query.  Have you thought about using that process?  Click on the Data tab in Excel.  Then click on Web Query.  Navigate to your site and use the arrows to pull in data.  I am not sure it will work for you, but it is worth a try.
0
 

Author Comment

by:kbay808
ID: 40600140
@ Itlbearand3
I tried using the web query first.  That failed big time.  It would only pull data from the home screen.

@IrogSinta
The issue is not the search criteria, but the source that it’s searching.  By right clicking on the webpage and selecting “View Source” I get the below source file that contains 6,332 lines.  The vba code searches the attached Script.OutterHTML file which only has 870 lines.  When I search for the target data, it’s not in the file.  The limited data in the Script.OutterHTML file is missing the key data that I need.  It does though have key information in the file that matches up with the webpage so it’s pulling from the right webpage.  It’s just not pulling all of it.  I also attached a screen shot of the webpage.  There seems to be 3 parts to it.  The red boxes indicate the 2 panes within the webpage.  The big box on the bottom has all of the data from the selected record from the top pane.  The 3rd part is everything else.  I pasted the text that I captured using the IE developer tab for one of the IDs in the screen shot with an arrow pointing to the physical location. As I navigate throughout the website, the URL stays the same so targeting the direct webpage is not an option.
Source.docx
Script.InnerHTML.txt.docx
Script.OutterHTML.txt.docx
Screen-shot.JPG
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40600619
Since you are able to get the whole source using XML DOM, then why not try something like this.  Here I'm able to get the value of the ID called "sa" which returns the string "Search":
    Dim hDoc As MSHTML.HTMLDocument
    Dim xHttp As Object
    
    Set xHttp = CreateObject("MSXML2.XMLHTTP")

    xHttp.Open "GET", "http://www.mrexcel.com/", False
    xHttp.send
    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText
    
    Debug.Print hDoc.GetElementByID("sa").Value

    Set xHttp = Nothing
    

Open in new window


For your particular site, you would use these:
    Debug.Print hDoc.GetElementByID("sa").Value
    Debug.Print hDoc.GetElementByID(“X183”).Value
    Debug.Print hDoc.GetElementByID(“X191”).Value
    Debug.Print hDoc.GetElementByID(“X195”).Value
    Debug.Print hDoc.Document.GetElementByID(“X94”).Value
    Debug.Print hDoc.Document.GetElementByID(“X70Hidden”).Value

Open in new window


Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40600706
@kbay808 - I was afraid it would not work, but it was worth looking into.  Thanks for the screen shot as that helps some.  I am curious if you click on the top box an do a view source is it different than if you click on the bottom box and view source?  I ask because the source doc you sent only has the html for the bottom section.  Now right click on the bottom section and view page info.  That will have a URL address in it.  Is this different than what is displayed up at the top?  If so, try that URL in the code.

@IrogSinta - Ron.  the problem is not with parsing the data.  )I went down the regex path first since I did not have enough information and chose not to move away from it.)  Right now the problem is that the Dom Document does not contain the data we need not that we cannot find the data.  Also, kbay808 has to navigate to the appropriate page and we need to access the active browser window (You can see the question I linked up at the start as a reference point).  That is why we don't use the HTTP object and instead search for the active browser window.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40600932
@ltlbearand3 - I'm aware of the problem using HTML DOM but the author mentioned that he was able to get the whole source code using XML DOM and the XMLHttpRequest Object.  So that's version I posted.

Ron
0
 

Author Comment

by:kbay808
ID: 40604359
@ltlbearand3
Your assumption is correct.  There is a different source file for the top and bottom portion.  When I view the source file there is no web address listed in either source file, but when I press F12 I get the below line that includes a web address.  I tried using that in the macro, but the site could not be found.  Is there another way to target the bottom portion?

<!-- DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd" -->
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40604582
So now I'm confused. You mentioned in your post #a40597710 that you were able to pull in the whole source code.  Is this true?  If it is, then did you try the code I posted last?

Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40604603
kbay808,  Instead of viewing the source file we need to look at the properties.  I put instructions for firefox above, sorry.  From IE, right click on the bottom section and then click on properties.  There should be an address (URL) line listed in the box that displays.  From the F12 Developer Tools you may also be able to see this on the Network Tab.  If nothing shows, could you add this code right under where we set the URL.  Then make sure only IE is open with Excel, run your code and show us what is displayed in the immediate window in VBA (Cntrl-G or View >> Immediate).  

    
    ' Show in the Immediate Window what URLs we find.
    For Each objIE In objShell
            If TypeOf objIE.Document Is MSHTML.HTMLDocument Then
            Set objHTML = objIE.Document
            If Not (objHTML Is Nothing) Then
                Debug.Print objHTML.URL
            End If
        End If
    Next

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40604614
@IrogSinta,  

Ron - if you re-read that post, you will notice he tested on the Mr Excel site and not on his own site.  The site he is trying to get data from has to be the active window.  The code you are posting will not pull from the active page, but creates it's own connection and does not pull from the active pages.  Seeing as how kbay808's URL has a secure connection I am guessing they have to log in and navigate to the page they want to scrape from.

@kbay808

It might be helpful if you document for as (removing the confidential information), the steps you take in IE before you want us to pull the data.
0
 

Author Comment

by:kbay808
ID: 40604645
@ ltlbearand3
Here are the steps to navigate to the target data.
1.      Logon to https://servman/sm/index.do
2.      Click on a ticket from the top box
3.      Bottom box is populated with target data

@IrogSinta
The web address stays the same while navigating
0
 

Author Comment

by:kbay808
ID: 40604744
Here are the screen shots for steps 2 and 3.
Step-2.JPG
Step-3.JPG
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40604763
Okay so your site is using an iFrame tag and I gather from the InnerHTML you uploaded previously that it's ID is "ext-gen69".  Can you test if the following gives you the source code for the bottom window?  If it does, it will be in a file called test.txt located in your C:\Temp folder:
    Dim sText As String
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    
    URL = "https://servman/sm/index.do"
    ie.Navigate URL
    
    
    State = 0
    Do Until State = 4
        DoEvents
        State = ie.ReadyState
    Loop
    
    
    Text = ie.Document.getElementById("ext-gen69").contentWindow.Document.body.innerHTML

    Open "C:\temp\test.txt" For Append As #1
    Write #1, Text
    Close #1
    MsgBox "Done"

Open in new window

0
 

Author Comment

by:kbay808
ID: 40604911
I received an error.  Please see the attached screen shots for more info.  There were 2 things that I noticed.  The first was that the code opened a new IE window instead of the using the active window.  By doing that there will be no target data to pull from.  I have to navigate to a ticket first to populate the target data.  The second thing is that I’m on the company network and as a user I don’t have rights the C: drive.
Debug-screen-shot.jpg
Error.JPG
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40605044
I didn't realize you were working off of an active window.  In that case, I'll borrow some of ltlbearand3's code to find the right window.  If the code below works it should save the source code of the bottom window to a file (just change the path c:\temp to one you have access to).  It should also hopefully give you the values of your Searches 2 through 6 and place the results in the debug window.

    Dim objIE As InternetExplorer
    Dim objIEDoc As MSHTML.HTMLDocument
    Dim objShell As SHDocVw.ShellWindows
    Dim strURL As String
    Dim strHTML As String
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    strURL = "https://servman/sm/index.do"
    
   
    ' 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 = strURL Then
                    blnFoundWebSite = True
                    objIE.Visible = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then

        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
     
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
    
        'extract data (on error added in case id is not found
        On Error Resume Next
        Debug.Print hDoc.getElementById("X183").Value
        Debug.Print hDoc.getElementById(“X191”).Value
        Debug.Print hDoc.getElementById(“X195”).Value
        Debug.Print hDoc.Document.getElementById(“X94”).Value
        Debug.Print hDoc.Document.getElementById(“X70Hidden”).Value
        On Error GoTo 0
    
        'save HTML to temp file
        strHTML = hDoc.body.innerHTML
        Open "C:\temp\test.txt" For Append As #1
        Write #1, strHTML
        Close #1

        MsgBox "Done"
            
    End If
    

Open in new window

0
 

Author Comment

by:kbay808
ID: 40605061
I'm getting a "Run-time error 91" on the below line.

hDoc.body.innerHTML = objIEDoc.getElementById("iframed").contentWindow.Document.body.innerHTML
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40605143
Oops, I just now corrected that line.  That's supposed to be:

hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
0
 

Author Comment

by:kbay808
ID: 40606226
I'm getting the same error for the new line too.
0
 

Author Comment

by:kbay808
ID: 40606242
Do I need any additional references?

Current References:
VISUAL BASIC FOR APPLICATIONS
MICROSOFT EXCEL 14.0 OBJECT LIBRARY
MICROSOFT INTERNET CONTROLS
MICROSOFT VBSCRIPT REGULAR EXPRESSIONS 5.5
MICROSOFT HTML OBJECT LIBRARY
MICROSOFT OFFICE 14.0 OBJECT LIBRARY
MICROSOFT FORMS 2.0 OBJECT LABRARY
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606305
I neglected to declare hDoc:
Dim hDoc As MSHTML.HTMLDocument

Also, this line is unnecessary:
objIE.Visible = True

Here's the revised code:
    Dim objIE As Internet Explorer
    Dim hDoc As MSHTML.HTMLDocument
    Dim objIEDoc As MSHTML.HTMLDocument
    Dim objShell As SHDocVw.ShellWindows
    Dim strURL As String
    Dim strHTML As String
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    strURL = "https://servman/sm/index.do"
    
   
    ' 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 = strURL Then
                    blnFoundWebSite = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then

        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
     
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
    
        'extract data (on error added in case id is not found
        On Error Resume Next
        Debug.Print hDoc.getElementById("X183").Value
        Debug.Print hDoc.getElementById(“X191”).Value
        Debug.Print hDoc.getElementById(“X195”).Value
        Debug.Print hDoc.Document.getElementById(“X94”).Value
        Debug.Print hDoc.Document.getElementById(“X70Hidden”).Value
        On Error GoTo 0
    
        'save HTML to temp file
        strHTML = hDoc.body.innerHTML
        Open "C:\temp\test.txt" For Append As #1
        Write #1, strHTML
        Close #1

        MsgBox "Done"
            
    End If
    

Open in new window

0
 

Author Comment

by:kbay808
ID: 40606879
Line 1 "Dim objIE As Internet Explorer" is red and won't run.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40606952
A reference for MICROSOFT INTERNET CONTROLS must be missing.  I have combined IrogSinta's code into the Excel Sheet from earlier and now is a good time to move away from RegEx and actually use the Elements to find your values.  Give this a try (If it works, give the points to IrogSinta).
IE-Pattern-Search-Example.8.xlsm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40607032
That's my typo.  It should be:
  Dim objIE As InternetExplorer

If the code works, I'd rather that the points be split.  It was a joint effort.
Here's hoping it works though.
:-)
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40607076
I hope it works also - I learned something new from you in it all too - the contentWindow object.  Thanks for jumping in and helping out.
0
 

Author Comment

by:kbay808
ID: 40607082
Back to the Run-time error '91' at the below line:
objIEDoc.body.innerHTML = objHTML.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40607169
I'm thinking you have parts of the code I posted mixed with the revision made by ltlbearand3 since the one error on declaring ObjIE As InternetExplorer was what you copied from my post but this latest error is from ltlbearand3's combined version since in my version I was using hDoc.  

Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40607302
@IrogSinta - I was trying to read up on the .currentWindow object.  I don't see it show up in Intellisense as an Element object.  Everything I read online references java.  I wonder if it is not available as an Element object in VBA?  (I did find it as part of a Frame object).

@kbay808 - Can you do a few things for us?
1.  Run this code from IrogSinta:
    Dim objIE As InternetExplorer
    Dim hDoc As MSHTML.HTMLDocument
    Dim objIEDoc As MSHTML.HTMLDocument
    Dim objShell As SHDocVw.ShellWindows
    Dim strURL As String
    Dim strHTML As String
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    strURL = "https://servman/sm/index.do"
    
   
    ' 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 = strURL Then
                    blnFoundWebSite = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then

        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
     
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
    
        'extract data (on error added in case id is not found
        On Error Resume Next
        Debug.Print hDoc.getElementById("X183").Value
        Debug.Print hDoc.getElementById(“X191”).Value
        Debug.Print hDoc.getElementById(“X195”).Value
        Debug.Print hDoc.Document.getElementById(“X94”).Value
        Debug.Print hDoc.Document.getElementById(“X70Hidden”).Value
        On Error GoTo 0
    
        'save HTML to temp file
        strHTML = hDoc.body.innerHTML
        Open "C:\temp\test.txt" For Append As #1
        Write #1, strHTML
        Close #1

        MsgBox "Done"
            
    End If

Open in new window


2.  Run the attached document.  If it works, let us know as we need to clean it up.  If it does not, please post the EE_Debug text file that it creates.
IE-Pattern-Search-Example.9.xlsm
0
 

Author Comment

by:kbay808
ID: 40607311
Your right, I was mixing them up.  Now the macro will run, but it freezes and excel stops responding.   Prior to excel freezing, I get the message box that says done and the test.txt file is created then I get pinged for my credentials. The test file does not contain any recognizable data from the website.  Because it’s a secure website that requires my credentials, is there a way to delay the part of the macro that is capturing the target data until the credentials are verified?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40607844
@ltlbearand3, I'm not sure why Intellisence doesn't pull it up.  I know it works though because I tested it with another framed site prior to posting the code.

@kbay808, aren't you working off of an active window?  If so, wouldn't you already have put in your credentials?
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40607864
@kbay808 - Please try this.  Log into your website, pull up the data you want to see and then run the code from IrogSinta.  Make sure to look at the immediate window in the VBA Editor to verify that the values you want are in the immediate window.  Also, post the test.txt file that is created.  Then please do part two from my post above and run the macro (#9)  and post the debug file.  Thanks.
0
 

Author Comment

by:kbay808
ID: 40609229
I verified that the values listed in the VBA Editor are the values that I want, ran the macro and attached the test file from IrogSinta code.  I then ran the macro in IE-Pattern-Search-Example.9 and receive the below compile error.

Compile error: Duplicate declaration in current scope
Dim objFrame As MSHTML.HTMLFrameElement

I added a ‘ in front of that line to skip it and ran it again.  I then received the below message box.
Message Box: “C:\Users\My user name\AppData\Local\Temp\1”

After clicking ok, a window appeared to select the location to save the EE_DebugFile.txt file.  I navigated to my desktop and clicked save.  The file was saved, but it was empty.  I then received the run-time error below.
objText.WriteLine "Debug File from " & Now()
EE-DebugFile.txt
test.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40609304
So if you were able to get the values you wanted then, you can just assign them to whatever cells you want to.  Also you don't need to save the file anymore so we could take out that portion of the code.  Here's the revised version:

    Dim objIE As InternetExplorer
    Dim hDoc As MSHTML.HTMLDocument
    Dim objIEDoc As MSHTML.HTMLDocument
    Dim objShell As SHDocVw.ShellWindows
    Dim strURL As String
    Dim strHTML As String
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    strURL = "https://servman/sm/index.do"
    
   
    ' 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 = strURL Then
                    blnFoundWebSite = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then

        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
     
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML
    
        'extract data (on error added in case id is not found
        On Error Resume Next
        Range("A5").Value = hDoc.getElementById("X183").Value
        Range("A6").Value = hDoc.getElementById(“X191”).Value
        Range("A7").Value = hDoc.getElementById(“X195”).Value
        Range("A8").Value = hDoc.Document.getElementById(“X94”).Value
        Range("A9").Value = hDoc.Document.getElementById(“X70Hidden”).Value
        On Error GoTo 0
               
    End If

Open in new window


ltlbearand3 would probably still want to work with you on the other version since he was testing some ideas.
0
 

Author Comment

by:kbay808
ID: 40609314
@IrogSinta - I only verified that that the code was calling for the info that I wanted, but in my last post my mention that the code has an error and does not run all of the way.  The test file does not contain the target data and the EE_DebugFile was blank.
0
 

Author Comment

by:kbay808
ID: 40609316
I also tried the code that you just posted.  There was no error, but there was also no result.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40609331
I have put in the code from IrogSinta into the spreadsheet with a few other ideas to help keep it from hanging.  Give this a try.
IE-Pattern-Search-Example.10.xlsm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40609341
When you said that you verified the values I assumed you meant that the Debug window showed the values that you were wanting to get.  That's why there is a Debug.Print command so that we can see if it's getting any data.

Now did your bottom window show any of the values you are looking for before you ran the code?  If it wasn't, have it navigate first to one that has the data you're trying to get, then run the code.
0
 

Author Comment

by:kbay808
ID: 40609383
@ltlbearand3 - I received a new error.  There was no option to debug it so I attached a screen shot of the error.  

@IrogSinta - I made sure that the ticket in the bottom pane contained all of the target data that we are searching for and running the code again I confirmed that the DebugFile was empty.

@ltlbearand3 & IrogSinta - I truly appreciate all of your help.  I know it’s a long weekend so if there is no response until next week I understand.  Again, thank you very much for all of your help.
VBA-Code-Error.JPG
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40610178
Ok I found a mistake in my sheet.  Please try A listed Below.  I also put IrogSinta's code into Excel with a couple things and that is sheet B.  Let us know.
IE-Pattern-Search-Example.A.xlsm
IE-Pattern-Search-Example.B.xlsm
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:kbay808
ID: 40610306
Example A - I received the error 424 "Object required".  No debug option

Example B - No error, but no result
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40610348
So I looked at the text that my code pulled in and apparently the IFrame it points to is the row of buttons that say Cancel, Previous, Next, Save & Exit, Save, Resolve, Find, Fill, Apply Template, and More.  I noticed in your screenshot included in your earlier post that you could still scroll down the page.  Is there another row of buttons just like the first row further down the page.  I'm just trying to figure out the html that's in the IFrame that got pulled.  It looks like there are a more IFrames embedded in there.
0
 

Author Comment

by:kbay808
ID: 40611195
No, there is only 1 row of those buttons.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40611239
Can you do a test for me?  With the code I sent you, change line 34 to the following:
        hDoc.body.innerHTML = objIEDoc.getElementById("iframed").contentWindow.Document.getElementById("detail-ext-gen12-683714").contentWindow.Document.body.innerHTML

Open in new window

I'm not sure it will work, but if there are no errors, upload the test file that it creates.  And then try it again with this line instead and upload the file as well.
        hDoc.body.innerHTML = objIEDoc.getElementById("iframed").contentWindow.Document.getElementById("detail-ext-gen12-259966").contentWindow.Document.body.innerHTML

Open in new window

0
 

Author Comment

by:kbay808
ID: 40611246
Please take a look at the attached screen shots.  This may be causing the problem.  The All collapse shows all what I would think are sub menus.  By default the Incident Update sub menu is open.  I have to click on the "+" sign next to Contact Info to view the rest of the target data.  I also indicated where each target data is located.  If need be I can provide screen shots with the "F12" box open to show the code for each box.
All-collapse.JPG
Incident-Update.JPG
Contact-Info.JPG
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40614408
Still waiting for some feedback about my post here.
0
 

Author Comment

by:kbay808
ID: 40615609
Sorry IroqSinta, it is still causing an error
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40615830
Since you said that it is still causing an error, I'm thinking you're confused with the code I'm referring to.  You were able to run it before and upload the test file it created.  So to make things clearer, here's the code I'd like to have you try:

    Dim objIE As Internet Explorer
    Dim hDoc As MSHTML.HTMLDocument
    Dim objIEDoc As MSHTML.HTMLDocument
    Dim objShell As SHDocVw.ShellWindows
    Dim strURL As String
    Dim strHTML As String
    Dim blnFoundWebSite As Boolean
    
    Set objShell = New SHDocVw.ShellWindows
    
    strURL = "https://servman/sm/index.do"
    
   
    ' 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 = strURL Then
                    blnFoundWebSite = True
                    Exit For
                End If
            End If
        End If
    Next

    If blnFoundWebSite Then

        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
     
        Set hDoc = New MSHTML.HTMLDocument
                hDoc.body.innerHTML = objIEDoc.getElementById("iframed").contentWindow.Document.getElementById("detail-ext-gen12-683714").contentWindow.Document.body.innerHTML
    
        'extract data (on error added in case id is not found
        On Error Resume Next
        Debug.Print hDoc.getElementById("X183").Value
        Debug.Print hDoc.getElementById(“X191”).Value
        Debug.Print hDoc.getElementById(“X195”).Value
        Debug.Print hDoc.Document.getElementById(“X94”).Value
        Debug.Print hDoc.Document.getElementById(“X70Hidden”).Value
        On Error GoTo 0
    
        'save HTML to temp file
        strHTML = hDoc.body.innerHTML
        Open "C:\temp\test.txt" For Append As #1
        Write #1, strHTML
        Close #1

        MsgBox "Done"
            
    End If
    

Open in new window


Then if there are no errors, upload the test file that it creates.  Then try it again but change line# 34 to the following instead and upload that file as well.
0
 

Author Comment

by:kbay808
ID: 40615873
I tried it with both of the below lines.  The line that contained "ext-gen69" in it created the attached test file.  The line that contained  "iframed" still has an error.
hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.body.innerHTML

Open in new window


hDoc.body.innerHTML = objIEDoc.getElementById("iframed").contentWindow.Document.getElementById("detail-ext-gen12-259966").contentWindow.Document.body.innerHTML

Open in new window

test.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40615915
Woah, I gave you the wrong frame ID's.  Sorry about that.  Change line 34 to the following and upload the results of each.

        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.getElementById("detail-ext-gen12-683714").contentWindow.Document.body.innerHTML

Open in new window

        hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.getElementById("detail-ext-gen12-259966").contentWindow.Document.body.innerHTML

Open in new window

0
 

Author Comment

by:kbay808
ID: 40617949
No joy for both.  I don't know if it will help, but when I debug it and place the cursor over the line that you provided I get the popup message.  I attached a screen shot of it.
Screen-Shot.jpg
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40617960
Looks like you're stepping through to the code but you need to let it pass that line so that hdoc.body.innerhtml gets assigned the value.  Your screenshot right now is prior to being assigned the value.  Let it pass that point and then take a screenshot again.  Or better yet, add this line right after line 34:

Debug.Print hDoc.body.innerHTML
0
 

Author Comment

by:kbay808
ID: 40618203
When I put the above line right after line 34 it still stops at line 34 and the debug does not have a chance to run.  Just in case I'm doing it wrong please see below for how I did it.  I attached the test file that it created after I skipped line 34.
hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.getElementById("detail-ext-gen12-683714").contentWindow.Document.body.innerHTML Debug.Print hDoc.body.innerHTML
        Debug.Print hDoc.body.innerHTML

Open in new window


Pass line 34
'hDoc.body.innerHTML = objIEDoc.getElementById("ext-gen69").contentWindow.Document.getElementById("detail-ext-gen12-683714").contentWindow.Document.body.innerHTML Debug.Print hDoc.body.innerHTML
        Debug.Print hDoc.body.innerHTML

Open in new window

test.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40618224
You said it stops at line 34.  Do you mean it gives you an error message?  If so, that message would be helpful.
0
 

Author Comment

by:kbay808
ID: 40618320
As requested
Debug.JPG
Error.JPG
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40618854
Please run the attached file and post the debug file it creates.
IE-Pattern-Search-Example.Debug1.xlsm
0
 

Author Comment

by:kbay808
ID: 40620242
Here is the file
EE-DebugFile.txt
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40620333
Thanks.  Since we don't have access to the website, I am going to have to try to find out information another way.  We may end up running a series of items to find what we need.  @IrogSinta - since you understand frames better than me, if you see something in the data we get back, please jump in.  Now try running this file and post the debug again.
IE-Pattern-Search-Example.Debug2.xlsm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40620363
@ltlbearand3, I believe we're on the same page here.  I was about to revise my code in a similar fashion.  Hopefully this one gets us some good data. :-)

Ron
0
 

Author Comment

by:kbay808
ID: 40620593
IT WORKS!!!  It pulled all of the data!!!  That was awesome.  Now can you modify the code so it does not create the EE_DebugFile?

Thank you both for all of your effort.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40620628
Well that is awesome.  I had several tests running in that code and I think I have the right piece that worked.  Give this a try.  If it does not work, then please post the file from Debug2.  Thanks.
IE-Pattern-Search-Example.11.xlsm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40621167
@ltlbearand3, it's curious how using the Document of a frame and the ContentWindow of a frame bring up two different sets of data.  Good job!
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40621275
@IrogSinta - Yeah.  I don't know how many times in my career I have said, "well this should not make a difference, but lets try it anyway" and it does make a difference.  Thank you for jumping in and helping and for teaching me about the ContentWindow.  Nice object to know.  Now I just hope that I cleaned it up properly.
0
 

Author Comment

by:kbay808
ID: 40622608
The Example.11 file did not work.  It had an error on the below line.  Just to double check that the issue was not with the trouble ticket that I was testing, I ran the Example.Debug2 file.  It work, but when I got out of the ticket and went to another ticket it stopped working.  I got "Not Found" for all of the results.  I then tried going back into the first ticket where it worked before and it still did not work.  I closed IE and then tried again and nothing again.  After a few more tries opening and closing IE it got it to work again, but only on the first try.  I can run it multiple times on the same ticket with no problem, but once I change to another ticket it stops working.  I attached 2 versions of the debug file.  One is for when it worked and the other is for when it didn’t.
The system will be down for maintenance for the weekend, so I will not be able to test anything until Sunday night at the soonest.
Set objIEDoc = objHTML.getElementById("ext-gen69").contentWindow.Document

Open in new window

EE-DebugFile--When-it-works-Sanitized--.
EE-DebugFile--When-it-does-not-work-.txt
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40623349
Unfortunately the Debug creation did not log everything I wanted to see.  Please run this one in both scenarios and post the files.  I see what I did wrong on version 11, but since the debug only worked one one ID, I want to go back to the Debug version.  Thanks.
IE-Pattern-Search-Example.Debug3.xlsm
0
 

Author Comment

by:kbay808
ID: 40625113
I tried testing both the Debug3 and the Debug2 files for over an hour, but could not get either file to pull the target data.  I will keep trying tomorrow.  In the mean time I attached the updated copy of the debug file.  Hopefully there is something of value in it.  I am also posting a macro that I’m currently using to extract the ticket number.  It’s only pulling from part of the webpage and using a different method, but it might be able to point you in the right direction or trigger another idea.

Sub GetIncTkt()

    Dim objHTML As MSHTML.HTMLDocument
    Dim objIE As InternetExplorer
    Dim objShell As SHDocVw.ShellWindows
    Dim blnFoundWebSite As Boolean
    Dim objRegEx As VBScript_RegExp_55.RegExp
    Dim objMatch As VBScript_RegExp_55.MatchCollection
    Dim strURL As String
    
    Set objShell = New SHDocVw.ShellWindows
    Set objRegEx = New VBScript_RegExp_55.RegExp
    
    ' **** Set the Web Address Here ****
    strURL = "https://servman/sm/index.do"
    
    ' 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 = strURL 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
        
        ' Set up Reg Ex to Parse out the text we need
        objRegEx.Pattern = "IMW[0-9]{0,8}"
        
        Set objMatch = objRegEx.Execute(objHTML.body.innerHTML)
        
        If objMatch.Count > 0 Then
            ' Put the first found value in Cell A1
            Range("A1").Value = objMatch.Item(0)
        
        End If
    
    End If
    
End Sub

Open in new window

EE-DebugFile--Not-working-.txt
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40627483
Okay.  We are going to have to try and work through some things.  First.  Run the Debug 2 file and change which frame (or part of the web page) that has the active cursor.  Next.  Try running this new debug (4).  It is going to create a file with a decent amount of data to help us get a feel for what is happening.
IE-Pattern-Search-Example.Debug4.xlsm
0
 

Author Comment

by:kbay808
ID: 40627637
I ran the debug 2 file 4 times with the cursor on 4 different spots on the web page starting at the pane to the box with target data at the lowest part of the webpage.  I also posted the debug4 file.
EE-Debug4File--Sanitized-.txt
EE-DebugFile-1of4.txt
EE-DebugFile-2of4.txt
EE-DebugFile-3of4.txt
EE-DebugFile-4of4.txt
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40629852
Ok.  We still need to drill down deeper -- trying to get the data to help us see exactly what your website it doing.  Please run this one and post the file.  

@IrogSinta - Ron if you see anything in these logs, please jump in.
IE-Pattern-Search-Example.Debug5.xlsm
0
 

Author Comment

by:kbay808
ID: 40629873
I ran it a few time placing my cursor in different parts of the webpage, but they were all the same.
EE-DebugFile.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40629932
So this large file had none of the elements we were hoping to find.  What I don't understand is now you were able to get all the data one time from Debug2 and have not been able to since.   Can you recall a certain step that you did then that you're doing differently this time?

Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40629989
OK.  Still trying to dig through all this.  Please make sure that all pieces are expanded on the site and then run this code once (does not matter where on the page the cursor is located this time) and post the results.  Thanks.

I am with IrogSinta, though, as I don't understand how that worked once and not now.
IE-Pattern-Search-Example.Debug6.xlsm
0
 

Author Comment

by:kbay808
ID: 40630000
For the life of me, I tried every which way possible to get it to work again.  As for the debug6 file, there is a compile error.  I attached a screen shot of the error.
Error.JPG
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40630602
Sorry about that. Try this one.
IE-Pattern-Search-Example.Debug6.xlsm
0
 

Author Comment

by:kbay808
ID: 40631761
I got a new error.
Error.JPG
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40631979
One more time . . . . . (I think I need to just give up  . . . . )   :-(
IE-Pattern-Search-Example.Debug6.xlsm
0
 

Author Comment

by:kbay808
ID: 40632099
I truly appreciate all of your effort and I don’t blame you if you want to quit.  I know how you feel.  Well here is the latest result.
EE-DebugFile.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40632181
Too bad we don't have access to this site.  This would be so much easier.
0
 

Author Comment

by:kbay808
ID: 40632186
I wish I could give you guys more points to make it worth it.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40632283
@ltlbearand3, that large file did reveal an IFrame ID that we haven't seen yet.  See if you can incorporate it into your debug file.  I'm not sure if it can be accessed right off the bat or if it would be in the contentwindow or HTML Document of "ext-gen69".  Here's the id:

detail-ext-gen12-357690

Ron
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40632337
OK.  The last one did not pull any data - I found one more mistake.  I also added the element that you want Ron.  Please give this a try and post the debug file.
IE-Pattern-Search-Example.Debug7.xlsm
0
 

Author Comment

by:kbay808
ID: 40632447
I got another error, but it did create a debug file.
Error.JPG
EE-DebugFile.txt
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40632472
It didn't' really get very far through the code.  kbay808, whenever you get an error, it would really be more helpful if you post the actual error message along with the line that it errors on.
0
 

Author Comment

by:kbay808
ID: 40633523
Sorry about that
Error-Message.JPG
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40633942
Here's a revision to that corrects that error.
IE-Pattern-Search-Example.Debug8.xlsm
0
 

Author Comment

by:kbay808
ID: 40634898
There was another error.  Along with the error screen shots, I also reposted the source code.  I’m thinking that after everything that has been tested and learned, maybe the code may shed a new light on things since it shows all of the target data.
Error-Message.JPG
Error.JPG
Source.docx
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40635450
My head is going in circles for what is working and not working.  OK try this one:
IE-Pattern-Search-Example.Debug9.xlsm
0
 

Author Comment

by:kbay808
ID: 40636209
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40636651
Ok.  I just proved we are going in circles.  One part of this file had the same code as the error from Debug 8.  It worked in Debug 9.  I will keep going, but we may come to a road block without being able to see the actual site.  I also see the frame names are changing.  I am going back to the idea of looping through the frames - found a problem when I tried to do that earlier.

Here we go again . . .
IE-Pattern-Search-Example.Debug10.xlsm
0
 

Author Comment

by:kbay808
ID: 40640802
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40640992
Indeed, the resulting data is disappointing.  I'm at a loss on what else can be done.  Without first hand access to the site, all we can rely on is ltlbearand3's debug algorithm to help give us an idea of what the site incorporates in its code and this has limitations.  Why the code worked that one time is a mystery.  Perhaps you can try playing with that particular code using different sets of clicks and focus events and maybe you'll find the right combination that worked before.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40641035
I am not sure if we are making progress or not, but I keep finding other things to try.  So, try one more time . . .

If you can please look through the debug file and see if any of the data you want shows up in it or not, that would help as it might look a little different than the view source data.
IE-Pattern-Search-Example.Debug11.xlsm
0
 

Author Comment

by:kbay808
ID: 40641199
I will id any target data moving forward.  For Debug11 I got a new error.
Error.JPG
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40643701
0
 

Author Comment

by:kbay808
ID: 40643886
Well, your efforts are paying off.  This file contains everything that I was asking for and more.  Below is a list of all of the target data that I could hope for.  I put the debug file in a word document to make it easier to find the target data.  I replaced all of the target data with the below names and put them in brackets and highlighted them green with red font to make it easy to find.  I also ran it 5 more times on different tickets just to make sure it’s pulling all of the data every time.  Some of the target data is listed in multiple places, but each one of them is highlighted.

Target Data
Related Ticket Number
Ticket number            
Title                  
Machine name
Alt ticket number
Description
Journal Updates
POC Name
POC Phone Number
Location Code
Related Ticket Number
Debug-File--Sanitized-.docx
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40643946
Good idea to loop through those frames, ltlbearand3
:-)
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40644038
Some progress finally.  Give this code a shot and see what it does for you.  I still create a debug file and you can post that if it does not work.
IE-Pull-Data.1.xlsm
0
 

Author Comment

by:kbay808
ID: 40645848
Its working perfect!!!  Could you please modify the code so that it does not create the debug file?  I will test it out for a couple of days just in case there are any surprises.  I truly appreciate all of your time and effort that you put into this.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40645941
@kbay808,
ltlbearand3 put a lot of effort into this.  Be sure he gets all the points.

Ron
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 400 total points
ID: 40645964
Wonderful (I think this is the most posts I have ever made on a single EE question).  Hopefully I have this all cleaned up right.  Give this a try:
IE-Pull-Data.NoDebug.xlsm
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40645970
Ron,  Thanks for the kind words, but I would not have found an answer if you had not joined the conversation.  You spotted the Frames and sub content that I had missed.  You deserve some points for that, in my opinion.  Thanks for jumping in and helping out.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 100 total points
ID: 40646160
I think this is the most posts I have ever made on a single EE question
This was indeed pretty long.
:-)

It's always fun trying to figure out a puzzle, especially one you get to solve.  As for splitting the points, give me something like 50 and I'm fine.  I'm happy to have joined you in this thread.  Maybe we'll solve another one together sometime.  

Ron
0
 

Author Closing Comment

by:kbay808
ID: 40650697
After testing it out for a couple of days, I only found only one issue and it’s not with the code.  When there is only 1 ticket left in the queue, the top pane disappears when I open the ticket throwing everything off, but that’s a separate question.  I really would like to thank both of you for all of your help, but especially ltlbearand3.  Only through your dedication was this solution possible.   Thank You! Thank You! Thank You!

I posted a new question about creating a code for when there is only 1 ticket in the queue.  The title is “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? Part 2”.  I ran your last file that created a debug file and posted it with the question.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 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

9 Experts available now in Live!

Get 1:1 Help Now