Excel

Open web page & search page for keyword(s) from Excel

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

On opening the web page (the page opens as a workbook) you type in a keyword or phrase and the page is searched for all occurrences of the keyword 

Discussion:

You may want to access something such as the use of a VBA function or command that you know are located somewhere on a web page but you dont want to have to search the whole page to find it. This gives you the location(s) on the page so you can go directly to it. The example given here only references one web page but there is nothing to stop you adding more pages to a list. 

Code:

instructions for use 

			
Option Compare Text 
Option Explicit 
 
Sub OpenHTMLpage_SearchIt() 
    Dim Cell As Range, Keyword$, N%, SearchAgain As VbMsgBoxResult 
     'put your own URL below, an example is given
    Application.Workbooks.Open ("http://www.geocities.com/johnske100/TheFINDfunction.html") 
     'the page opens in column A so we need column A fairly wide to read the page
    Columns(1).ColumnWidth = 85 
    MsgBox "When finished - click X on the web window to close it " 
StartSearch: 
    N = 0 
    Keyword = InputBox("Enter the word or phrase you are looking for", "Keyword Is ?") 
    If Keyword = Empty Then GoTo StartSearch 
    For Each Cell In Range("A1:A500") 
        If Cell Like "*" & Keyword & "*" Then 
            MsgBox "An " & Keyword & " was found at " & Cell.Address & " (" & Cell & ")" 
            N = N + 1 
        End If 
    Next Cell 
    If N = 0 Then MsgBox "Sorry, no " & Keyword & "'s found", , "not Found" 
    SearchAgain = MsgBox("Another search?", vbYesNo, "More?") 
    If SearchAgain = vbYes Then GoTo StartSearch 
End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select File/Close and Return To Microsoft Excel
  6. Save the workbook
  7. Select Tools/Macro/Macros.../OpenHTMLpage_SearchIt/Run
 

Test the code:

  1. You must be on-line to do this...
  2. Select Tools/Macro/Macros.../OpenHTMLpage_SearchIt/Run
  3. When the web-page opens, just follow the directions..
  4. Note that this works for html web-pages but not for php (& maybe others)
 

Sample File:

SearchWebPageAsWB.zip 10.76KB 

Approved by mdmackillop


This entry has been viewed 365 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express