+ Reply to Thread
Results 1 to 12 of 12

Search for range of strings on a webpage (url)

  1. #1
    Registered User
    Join Date
    08-13-2009
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    17

    Search for range of strings on a webpage (url)

    Hi,

    I found some code to search for a string on a webpage (url) and it works.

    I need to adjust the VBA code a little bit, but I'm not that good at VBA.

    What I need is:

    Instead of entering a single string (query) then the program should loop through all queries in column B.

    If the specific query is found within the text on the webpage (url) it should return 1 in column C and the number of times the query appears (occurrences) in column D.

    I have attached the Excel spreadsheet with an example.

    Hope you can help me.

    Best regards
    Morten
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,357

    Question Re: Search for range of strings on a webpage (url)


    Hi !

    Case sensitive or not ? Or what is your expected result in cell D2 ?

    Column C is not necessary as if not found column D is blank or set to zero
    Last edited by Marc L; 06-29-2018 at 01:16 PM.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,357

    Cool Try this ‼


    With a comment in code if case not sensitive …

    Paste this occurences demo to the worksheet module :

    PHP Code: 
    Sub Demo1()
             
    Dim oDoc As ObjectR&
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET", [A2].ValueFalse
            
    .setRequestHeader "DNT""1"
                
    On Error Resume Next
            
    .send
                On Error 
    GoTo 0
         
    If .Status <> 200 Then BeepDebug.Print .Status" " & .StatusText: Exit Sub
             Set oDoc 
    CreateObject("htmlfile")
                 
    oDoc.write .responseText
        End With
        
    For 2 To Me.UsedRange.Rows.Count
            Cells
    (R4).Value UBound(Split(oDoc.body.innerTextCells(R2).Value))     ' or .Value, , vbTextCompare))
        Next
             Set oDoc = Nothing
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Try this ‼

    Hello Morsoe,

    I tested this macro on the URL you posted and the search terms. The results matched my manual search of the terms in the page source. This macro has been added to the attached workbook and the button now calls this macro. This macro can be easily changed to handle multiple URLs in column "A" if needed.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,357

    Re: Search for range of strings on a webpage (url)


    Hi Leith !

    You did the same error as my first try : your cell D2 result is wrong as there are only 2 in the webpage text as OP required
    instead of 12 in the webpage code !

    Same occurrences result as yours in my first try looking in whole webpage code instead of just webpage text,
    demo located in the worksheet module :

    PHP Code: 
    Sub Demo0()
             
    Dim R&
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET", [A2].ValueFalse
            
    .setRequestHeader "DNT""1"
                
    On Error Resume Next
            
    .send
                On Error 
    GoTo 0
                
    If .Status <> 200 Then BeepDebug.Print .Status" " & .StatusText: Exit Sub
            
    For 2 To Me.UsedRange.Rows.Count
                Cells
    (R4).Value UBound(Split(.responseTextCells(R2).Value, , vbTextCompare))
            
    Next
        End With
    End Sub 
    Last edited by Marc L; 06-30-2018 at 09:16 AM.

  6. #6
    Registered User
    Join Date
    04-03-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    74

    Re: Search for range of strings on a webpage (url)

    the macro from 1 always as a value.
    You should improve it to analyze all the pages of a site.
    It would be very useful.

  7. #7
    Registered User
    Join Date
    08-13-2009
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Search for range of strings on a webpage (url)

    Thank you for your efforts Marc L and Leith.

    As Marc L says the correct result of the term "EU-landene" would be 2 and not 12.

    Best regards
    Morten

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,357

    Re: Search for range of strings on a webpage (url)


    It may depend sometimes on case sensitive or not,
    my post #3 demo is sensitive (where you can click on bottom left star icon ★ Add Reputation thanks !) …
    Last edited by Marc L; 07-02-2018 at 05:42 AM. Reason: typo

  9. #9
    Registered User
    Join Date
    04-03-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    74

    Re: Search for range of strings on a webpage (url)

    Could you add a complete file with the updated macro?
    Thank you

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,357

    Re: Search for range of strings on a webpage (url)

    stefano,
    if you dare to ask a question in a private message it would be smarter your inbox has some place to receive any answer ‼
    Anyway, I never answer to a private message for any technical question which must be in the thread
    as you already know from another forum, ...
    Last edited by AliGW; 07-03-2018 at 02:33 AM. Reason: Personal attack edited out.

  11. #11
    Registered User
    Join Date
    04-03-2017
    Location
    italy
    MS-Off Ver
    2010
    Posts
    74

    Re: Search for range of strings on a webpage (url)

    I'm just interested in the macro.

    ...
    Last edited by AliGW; 07-03-2018 at 02:34 AM. Reason: Response to personal attack removed.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,869

    Re: Search for range of strings on a webpage (url)

    Stefano: Marc is right about private messages. You should ask any and all of your follow-up questions here within the thread (if relevant to this thread) or start your own..

    Marc: Please do not launch personal attacks on other members of this forum.

    I shall edit both posts - this 'argument' will cease now. Thanks.

    In both cases, the fact that English is not your first language has caused some misunderstanding here.
    Last edited by AliGW; 07-03-2018 at 02:39 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Search range for multiple strings
    By cboud in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2016, 04:24 PM
  2. Search range for a list of strings, show userform and replace selected cells.
    By banaanas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2015, 01:04 AM
  3. Replies: 1
    Last Post: 07-04-2014, 11:52 AM
  4. [SOLVED] search for parts of strings in another list of strings
    By marioroter in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-17-2013, 12:28 PM
  5. excel formula to search Multiple strings in several columns and return strings
    By krratna123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 11:20 AM
  6. Replies: 1
    Last Post: 08-13-2013, 08:32 AM
  7. Search range of strings with a range of keywords and highlight matches
    By efillner in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 04:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1