Excel-VBA-StockSearch

From iMacros

This sample script written in Excel VBA demonstrates how to read data from an Excel sheet and use this information to search a website and write extracted values back to the Excel sheet. It calls the macros VBA-StockSearch1.iim and VBA-StockSearch2.iim.

Option Explicit
  '(c) 2007 Internet Macros Tech Support
  ' 2007-5-16, Created
  
Private Sub CommandButton1_Click()
 
  MsgBox "This script demonstrates how to read data from an Excel sheet and using this, capture information from a website."
  
  Dim iim1, iret, row, totalrows
     
  Set iim1 = CreateObject("InternetMacros.iim")
  iret = iim1.iimInit
  iret = iim1.iimDisplay("Submitting Data from Excel")
  
  'Insert todays date
  Cells(1, 2).Value = Date
     
  'Navigate to the site and extract the first price
  row = 2
  'Set the variable
  iret = iim1.iimSet("-var_companyname", Cells(row, 1).Value)
  'Set the display
  iret = iim1.iimDisplay("Row# " + CStr(row))
  'Run the macro
  iret = iim1.iimPlay("VBA-StockSearch1")
  If iret < 0 Then
      MsgBox iim1.iimGetLastError()
  End If
  Cells(row, 2).Value = Replace(iim1.iimGetLastExtract(), "[EXTRACT]", "")
    
  'Loop through portfolio and extract remaining prices
  totalrows = ActiveSheet.UsedRange.Rows.Count
  For row = 3 To totalrows
     'Set the variable
     iret = iim1.iimSet("-var_companyname", Cells(row, 1).Value)
     'Set the display
     iret = iim1.iimDisplay("Row# " + CStr(row))
     'Run the macro
     iret = iim1.iimPlay("VBA-StockSearch2")
     If iret < 0 Then
        MsgBox iim1.iimGetLastError()
     End If
     'Insert the extracted price
     Cells(row, 2).Value = Replace(iim1.iimGetLastExtract(), "[EXTRACT]", "")
  Next row
  
  iret = iim1.iimDisplay("Share price extraction complete")
  iret = iim1.iimExit
  
End Sub

Macro code for VBA-StockSearch1.iim:

VERSION BUILD=6000410     
TAB T=1     
TAB CLOSEALLOTHERS     
SET !ERRORIGNORE YES
URL GOTO=http://www.londonstockexchange.com/en-gb/     
SIZE X=1092 Y=913     
TAG POS=1 TYPE=INPUT:RADIO FORM=NAME:Home ATTR=ID:AdTop_advUTSel_rblUserTypes_0&&VALUE:CTXA1 CONTENT=CTXA1 
TAG POS=1 TYPE=INPUT:IMAGE FORM=NAME:Home ATTR=ID:AdTop_advUTSel_btnSubmit  
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:Home ATTR=ID:mygrid__ctl0__ctl0__ctl0_txtNameCode CONTENT={{companyname}}
TAG POS=1 TYPE=INPUT:IMAGE FORM=NAME:Home ATTR=ID:mygrid__ctl0__ctl0_btnSearch  
TAG POS=1 TYPE=TD ATTR=TXT:Code   
TAG POS=R4 TYPE=TD ATTR=CLASS:resultstablecell&&TXT:* EXTRACT=TXT  

Macro code for VBA-StockSearch2.iim:

VERSION BUILD=6000410     
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:T10 
ATTR=ID:WebControl1_PresentationModeControlsContainer__ctl0__ctl0__ctl0__ctl0_txtNameCode CONTENT={{companyname}}
TAG POS=1 TYPE=INPUT:IMAGE FORM=NAME:T10 ATTR=ID:WebControl1_PresentationModeControlsContainer__ctl0__ctl0__ctl0_btnSearch  
TAG POS=1 TYPE=TD ATTR=TXT:Code   
TAG POS=R4 TYPE=TD ATTR=CLASS:resultstablecell&&TXT:* EXTRACT=TXT

The Excel Spreadsheet contains the following entries:

Company Name
InterContinental Hotels Group
JJB Sports
Cookson Group
Babcock International