The renewal maintenance has officially ended for Progress iMacros effective November 30, 2023.
This Wiki site will also no longer be moderated from the Progress side.
Thank you again for your business and support.
Sincerely, The Progress Team
Excel-VBA-StockSearch
Jump to navigation
Jump to search
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 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("imacros") iret = iim1.iimOpen("-ng") 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