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 616 times.

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