Excel VBA Example Code

From iMacros
Jump to: navigation, search

This sample script written in Excel VBA demonstrates how to read data from an Excel sheet and submit this information to a website. It calls the macro Wsh-Submit-2-Web.iim. It is included with iMacros trial version.

Web query with iMacros and VBA. All VB Script sample code can be used.
Web query with iMacros for Firefox. The VBA script is exactly the same as for the iMacros Browser, except the different iimInit parameter.

See also: How to enable VBA macros

   Option Explicit
   
   Private Sub CommandButton1_Click()
   
   MsgBox "This macro demonstrates how to read data from an Excel sheet and submit this information to a website."
   
   Dim iim1, iret, row, totalrows
   
   Set iim1 = CreateObject("imacros")
   iret = iim1.iimInit
   'Firefox iret = iim1.iimInit ("-fx")
   iret = iim1.iimDisplay("Submitting Data from Excel")
   
   totalrows = ActiveSheet.UsedRange.Rows.Count
   For row = 2 To totalrows
      'Set the variables
      iret = iim1.iimSet("FNAME", Cells(row, 1).Value)
      iret = iim1.iimSet("LNAME", Cells(row, 2).Value)
      iret = iim1.iimSet("ADDRESS", Cells(row, 3).Value)
      iret = iim1.iimSet("CITY", Cells(row, 4).Value)
      iret = iim1.iimSet("ZIP", Cells(row, 5).Value)
      iret = iim1.iimSet("STATE-ID", Cells(row, 6).Value)
      iret = iim1.iimSet("COUNTRY-ID", Cells(row, 7).Value)
      iret = iim1.iimSet("EMAIL", Cells(row, 8).Value)
      'Set the display
      iret = iim1.iimDisplay("Row# " + CStr(row))
      'Run the macro
      'Same macro as in database-2-web.vbs example!
      iret = iim1.iimPlay("wsh-submit-2-web")
      If iret < 0 Then
         MsgBox iim1.iimGetLastError()
      End If
   Next row
   
   iret = iim1.iimDisplay("Submission complete")
   iret = iim1.iimExit
   
   End Sub

Macro code for Wsh-Submit-2-Web:

 VERSION BUILD=700705  
 TAB T=1     
 TAB CLOSEALLOTHERS     
 URL GOTO=http://www.iopus.com/imacros/demo/v6/f2/automatic-data-entry.asp     
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:FNAME CONTENT={{FNAME}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:LNAME CONTENT={{LNAME}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:ADDRESS CONTENT={{ADDRESS}}
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:CITY CONTENT={{CITY}} 
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:ZIP CONTENT={{ZIP}}
 'Tip: Use the string ($) instead of the index to select the state and country 
 'Index would be the position of an entry in the combo box list, e. g. 161 for United States
 'The $ option is more robust against changes of the combo box content
 'Note * is used to ignore leading and trailing blanks that could be in the input data
 TAG POS=1 TYPE=SELECT FORM=NAME:WebDataEntry ATTR=NAME:STATEID CONTENT=$*{{STATE-ID}}*
 TAG POS=1 TYPE=SELECT FORM=NAME:WebDataEntry ATTR=NAME:COUNTRYID CONTENT=$*{{COUNTRY-ID}}*
 TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:WebDataEntry ATTR=NAME:EMAIL CONTENT={{EMAIL}}
 TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:WebDataEntry ATTR=NAME:SendButton&&VALUE:Click<SP>to<SP>submit<SP>data  
 TAG POS=1 TYPE=A ATTR=TXT:Back