1) Use Firefox Browser ( or Download from http://www.mozilla.com/en-US/firefox/ )
2) Install IMacros from https://addons.mozilla.org/en-US/firefox/addon/3863
3) Use the following code, paste in a notepad file and save as “macro1.iim”.
VERSION BUILD=6111213 RECORDER=FX
Note the ' prefix denotes commented out code
'AUTOMATED ENTRY INTO WEBSITE IN CORRECT POSITION
TAB T=1
'URL GOTO=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmLogin ATTR=NAME:txtUser CONTENT=USERNAME
'SET !ENCRYPTION NO
'TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:frmLogin ATTR=NAME:txtPassword CONTENT=USERPASSWORD
'TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:frmLogin ATTR=NAME:btnSubmit&&VALUE:Login
'TAG POS=1 TYPE=TD ATTR=ID:el34
'ENTER FORM INPUTS
'TAG POS=1 TYPE=SELECT FORM=NAME:frmSearch ATTR=NAME:txtState CONTENT=%CA
'TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmSearch ATTR=NAME:txtName CONTENT=b
'TAG POS=1 TYPE=INPUT:SUBMIT FORM=NAME:frmSearch ATTR=NAME:btnSubmit&&VALUE:Submit
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !LOOP 1
SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
TAG POS=1 TYPE=STRONG ATTR=TXT:Name
TAG POS=R{{!LOOP}} TYPE=A ATTR=HREF:* EXTRACT=HREF
SET !VAR1 {{!EXTRACT}}
'PROMPT {{!EXTRACT}}
URL GOTO={{!VAR1}}
TAG POS=1 TYPE=STRONG ATTR=TXT:Name
TAG POS=R1 TYPE=TD ATTR=TXT:* EXTRACT=TXT
TAG POS=1 TYPE=STRONG ATTR=TXT:Email
TAG POS=R1 TYPE=TD ATTR=TXT:* EXTRACT=TXT
'PROMPT {{!EXTRACT}}
BACK
SAVEAS TYPE=EXTRACT FOLDER=* FILE=*
4) The code should be run after logging in and after giving inputs for name (use wild card of a single alphabet say a) and state from drop down
5) Click submit to get number of records
6)Click on the IOpus Macro button next to address bar in Firefox and load the macro file above
7) Run macro ( Click on run loop button from 1 to X where X is number of records returned in step5.
Repeat Steps 4 to 7 till a single State ( which is the group by variable here ) is complete.
8) Go to C:\Documents and Settings\admin\My Documents\iMacros\Downloads (Check this from IMacros settings and options in your installation)
9) Rename the file index as “state.csv”
10) Open CSV file
11) Use the following Office 2003 Macro to clean the file
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/22/2008 by ajay
'
' Keyboard Shortcut: Ctrl+q
'
Cells.Select
Selection.Replace What:="#NEWLINE#", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
Columns("B:B").ColumnWidth = 23.71
Columns("A:A").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=9
ActiveWorkbook.Save
End Sub
12) In case you have Office 2007 Use The Record Macro feature to create your unique Macro in your personal Macro Workbook, basically replacing all #NEWFILE# with space (using Ctrl+H) and using Text to columns for column 2 and column 3, with type delimited,next, treat successive delimiters as one (check box),next,do not import first column (BY selecting that column”)
13) To append lots of files into 1 file use the following R Commands
Download R from www.r-project.org
>setwd("C:\\Documents and Settings\\admin\\My Documents\\iMacros\\Downloads")
Note this is the same folder as in Step 8 above
>list.files(path = ".", pattern = NULL, all.files = FALSE, full.names = FALSE,
+ recursive = FALSE, ignore.case = FALSE)
The R output is something like below
ACTUAL EXECUTION TIME REVISED MACRO
This uses multiple tabs ( using TAB T=1 and TAB T=2) to switch between Tabs. Thus you can search for a big name in Tab 1 , while Tab 2 consists of the details of the table components ( here Name and Email positioned relatively)
Execution of Loop is by the Loop Button on IMacros