Parsing the Web: Loop and parse all occurences of text after text in a string

therber2

New Member
Joined
Nov 28, 2011
Messages
15
Hey,

First time poster here so be easy on me...

I am working on modifying this code (below) that I found here: http://www.mrexcel.com/forum/showthread.php?t=277591

Code:
Option Explicit

Public stResultstr As String

Sub SearchForString()
   Dim rngURL As Range
   Dim cll As Range
   Dim stCheck As String
   Dim xmlHttp As Object
   
   On Error Resume Next
   Set xmlHttp = CreateObject("Microsoft.XMLHTTP")
   If xmlHttp Is Nothing Then
      MsgBox "Unable to create XMLHTTP object, it's probably not installed on this machine", vbCritical
      Exit Sub
   End If
   
   Set rngURL = Application.InputBox("Select the range of URLs to check", "Select Range", Selection, Type:=8)
   On Error GoTo 0
   
   If rngURL Is Nothing Then Exit Sub
   
   stCheck = InputBox("Enter the text to search", "", "")
   If Len(stCheck) = 0 Then Exit Sub
   
   For Each cll In rngURL.Cells
      If CheckURL(xmlHttp, cll.Value, stCheck) Then
       
         cll.Offset(, 1).Value = stResultstr
         
      End If
     
   Next cll
   
End Sub

Public Function CheckURL(ByRef xmlHttp As Object, ByVal URL As String, ByVal stCheck As String) As Boolean
   Dim stResult As String
   Dim intt As Long
   
   If Not LCase$(URL) Like "http://*" Then
      URL = "http://" & URL
   End If
   
   xmlHttp.Open "GET", URL, False
   xmlHttp.Send ""
   
   If xmlHttp.readyState = 4 Then
      If xmlHttp.Status = 200 Then
         stResult = xmlHttp.responseText
         
          If InStr(1, stResult, stCheck, vbBinaryCompare) > intt Then
            intt = InStr(1, stResult, stCheck, vbBinaryCompare) + Len(stCheck)
            CheckURL = True
            stResultstr = Mid(stResult, intt, 10)
            
         End If
      End If
   End If
End Function

So right now I have the script prompt you for a URL range, then it asks you for what you want to search for, and I have it set to return the next 10 characters from that found point.

You can see here:

Code:
If InStr(1, stResult, stCheck, vbBinaryCompare) > intt Then
            intt = InStr(1, stResult, stCheck, vbBinaryCompare) + Len(stCheck)

I began to create something to identify the last position of the found item because next I want to loop it to find the next occurance of the same thing and return the next 10 characters after it in the next cell to the right in that same row:

Code:
cll.Offset(, 1).Value = stResultstr

Well, as I am new to this board..I am also new to VBA and programming in general. I'm sure this is very simple for someone who knows how to write basic loops. Thanks for the help..

Again, what I want to do here is find and parse out every occurence of a string (inputbox) found on a web page url in column A. The parse occurrences will go to the right in cells C though ? for row N.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

therber2

New Member
Joined
Nov 28, 2011
Messages
15
Hey guys,

Did anyone have any ideas on this one? I'm so close here; again, I'm new to the forum. Not sure if this is again any rules, but I'll send someone a $20 tip via paypal if they can solve it. :biggrin:

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,521
Messages
5,625,295
Members
416,087
Latest member
drth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top