firefox 41.0.2
WIN: 8.1 Pro
------------------------------------------------
Hey Guys,
I am totally new to iMacros and VBS and need your help concerning my project:
In general, I need to download data from an online database (Capital IQ) for about 50k firmyears and save each firm-file as xls.
I have one URL for every of the about 5000 firms, saved in an csv. file.
Each firm has about 10 years of data, which should be saved in a 'binder', which can be done by selecting the years via drop down and adding them to the binder by a click. The process for every firm is as follows:
1) I clear the old binder (done by 2 clicks)
2) I select one year in a dropdown menu, confirm and then add it to the binder (3 clicks)
3) I repeat this step for each year in the list (--> not every firm has the same no. of available years!)
4) I save the binder as xls. (3 clicks)
I could already produce these steps with the help of the macro recording.
In order to loop through all firms, I implemented a short script at the beginning looping through one column of the csv. It is also my aim that the downloaded binder is named after a certain ticker, which can also be found in a particular column in the csv file.
So far, executing the script file only results in an unknown error code -1001, maybe you could help me?
Code: Select all
'open page
Option Explicit
Dim iim, status
Set iim= CreateObject ("imacros")
status = iim.iimOpen("-fx", true)'open in Firefox
'TODO: import URLs from csv
Dim tm
tm= "SET !DATASOURCE C:\Users\Sebastian\OneDrive\HIWI\URL_Input.csv" + vbNewLine
tm = tm + "SET !LOOP 2" + vbNewLine
tm = tm + "SET !DATASOURCE_COLUMNS 4" + vbNewLine
tm = tm + "SET !DATASOURCE_LINE {{!LOOP}}"
'TODO: loop for all URLs
status = iim.iimPlayCode(tm)
'initially empty old binder
Dim macro
macro = "VERSION BUILD=8940826 RECORDER=FX" + vbNewLine
macro = macro + "TAB T=1" + vbNewLine
macro = macro + "URL GOTO={{!COL4}}" + vbNewLine 'fourth column from csv as URL
macro = macro + "TAG POS=259 TYPE=IMG ATTR=SRC:https://w3.ciqimg.com/CIQDOTNET/images/shim.gif?urwvid=3221519312" + vbNewLine
macro = macro + "ONDIALOG POS=1 BUTTON=OK CONTENT=" + vbNewLine
macro = macro + "TAG POS=1 TYPE=A ATTR=ID:_clearContents" + vbNewLine
macro = macro + "WAIT SECONDS = 1" + vbNewLine
status = iim.iimPlayCode(macro)
'loop through all firmyears by selecting each year from dropdown + adding them to binder
Dim schleife
schleife= "TAG POS=1 TYPE=SELECT FORM=ID:ctl00 ATTR=ID:_pageHeader_fin_dropdown_source CONTENT={{loopNumber}} " + vbNewLine
schleife = schleife + "TAG POS=1 TYPE=INPUT:SUBMIT FORM=ID:ctl00 ATTR=ID:_pageHeader_TopGoButton" + vbNewLine
schleife = schleife + "WAIT SECONDS = 4" + vbNewLine
schleife = schleife + "TAG POS=1 TYPE=AREA ATTR=ONMOUSEOUT:if(!document.getElementById('_pageHeader_ctl22').tempdisable){document.getElementById('_pageHeader_bToolbar_addToBinder').className<SP>=<SP>""binderIcoSprite_AtBRoll_excel""}else{document.getElementById('_pageHeader_bToolbar_addToBinder').className<SP>=<SP>""binderIcoSprite_AtBRollG_excel""}&&ONMOUSEOVER:if(!document.getElementById('_pageHeader_ctl22').tempdisable){document.getElementById('_pageHeader_bToolbar_addToBinder').className<SP>=<SP>""binderIcoSprite_AtBRollS_excel""}else{document.getElementById('_pageHeader_bToolbar_addToBinder').className<SP>=<SP>""binderIcoSprite_AtBRollH_excel""}&&ONCLICK:document.getElementById('_pageHeader_addToBinderMenuFP').FadePanel.show();&&TITLE:Select<SP>Report<SP>to<SP>Add<SP>to<SP>Binder&&COORDS:51,0,61,19&&SHAPE:rect&&TXT:" + vbNewLine
schleife = schleife + "TAG POS=1 TYPE=SPAN ATTR=TXT:Add<SP>Capital<SP>Structure<SP>Details<SP>Report" + vbNewLine
schleife = schleife + "WAIT SECONDS = 4"
Dim recentyear
recentyear = 1
do while not status < 0' ToDo: loop until last entry of dropdown reached, something with #EANF#?
recentyear = recentyear +1
status = iim.iimSet("loopNumber", Cstr(recentyear))
status = iim.iimPlayCode(schleife)
loop
'download binder as excel file
Dim speichern
speichern = speichern + "TAG POS=259 TYPE=IMG ATTR=SRC:https://w3.ciqimg.com/CIQDOTNET/images/shim.gif?urwvid=3221519312" + vbNewLine
speichern = speichern + "ONDOWNLOAD FOLDER=C:\Users\snuhhh\OneDrive\HIWI\Binders FILE={{!COL2}}.xls WAIT=YES" + vbNewLine 'Filename: Ticker Variable.xls from csv input
speichern = speichern + "TAG POS=1 TYPE=IMG ATTR=SRC:https://w0.ciqimg.com/CIQDOTNET/images/BinderToolbar/ico_docType_excel.gif?urwvid=3221519312" + vbNewLine
speichern = speichern + "WAIT SECONDS=6" + vbNewLine
speichern = speichern + "TAB T=2" + vbNewLine
speichern = speichern + "TAG POS=1 TYPE=INPUT:BUTTON FORM=ID:ctl00 ATTR=ID:_cancel" + vbNewLine
speichern = speichern + "TAB T=1" + vbNewLine
'Quit Script
status = iim.iimClose()
Wscript.Quit(status)