Excel

Automate Internet Explorer from Excel (google search)

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

brettdj

Description:

This code runs a google search for 'vbax kb'. If a valid website is found via a regular expressions parsing search, then this web page is opened. For more info on regular expressions please see http://www.vbaexpress.com/kb/getarticle.php?kb_id=68 

Discussion:

You want to download data from an internet database. Excel can be used to automate internet explorer, loop through multiple pages for specific strings and then dump data. 

Code:

instructions for use 

			
Sub AutomateIE() 
    Dim ie As InternetExplorer 
    Dim RegEx As RegExp, RegMatch As MatchCollection 
    Dim MyStr As String 
     
    Set ie = New InternetExplorer 
    Set RegEx = New RegExp 
     
     'Search google for "vbax kb"
    ie.Navigate "http://www.google.com.au/search?hl=en&q=vbax+kb&meta=" 
     
     'Loop unitl ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE 
    Loop 
     
     'String to parse google search for a VBAX reference
    With RegEx 
        .Pattern = "www.vbaexpress.+?html" 
        .MultiLine = True 
    End With 
     
     'return text from google page
    MyStr = ie.Document.body.innertext 
    Set RegMatch = RegEx.Execute(MyStr) 
     
     'If a match to our RegExp searchstring is found then launch this page
    If RegMatch.Count > 0 Then 
        ie.Navigate RegMatch(0) 
        Do Until ie.ReadyState = READYSTATE_COMPLETE 
        Loop 
        MsgBox "Loaded VBAX link" 
         'show internet explorer
        ie.Visible = True 
    Else 
        MsgBox "No VBAX link found" 
    End If 
     
    Set RegEx = Nothing 
    Set ie = Nothing 
End Sub

How to use:

  1. Copy the code above.
  2. Open your workbook.
  3. Hit Alt+F11 to open the Visual Basic Editor (VBE).
  4. From the menu, choose Insert-Module.
  5. Paste the code into the code window at right.
  6. Close the VBE, and save the file if desired.
 

Test the code:

  1. Run the macro SetRefs by going to Tools-Macro-Macros and double-click SetRefs. This sets the references to the VBscript Regular Expressions 5.5 and Internet Controls libraries
  2. Run the macro AutomateIE by going to Tools-Macro-Macros and double-click AutomateIE.
 

Sample File:

IEauto (KB15).zip 10.86KB 

Approved by mdmackillop


This entry has been viewed 629 times.

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