Connect Excel with IE

[De_chopin]

Hi to all,

I have an excel sheet where i have 3 columns: artist name, name of a song, and empty column to paste a url. Now what i`d like my macro to do is to copy first and second colums and paste it into search engine at http://www.youtube.com with some predefined string - "addon". Then it press search and get the first result from search (some video) and gets this video`s url (like e.x http://www.youtube.com/watch?v=rVOVdB-u9I4) and paste it into my excel sheet in 3rd column. Then repeat it like 8000 times

Worst thing is i have to do it in 2 versions. One automatic, and one semiautomatic. The automatic should work as one i discribed. Semi automatic is similar but needs a user reaction so user can choose from first four search results to be pasted in my sheet/url column.

Do you guys have any idea hot to do it? It`d save me a lot of time

Best,

[khemu]

No Problemo...Can you provide an excel with the first two columns populated...it will save me some time developing this...

[De_chopin]

here is part of the list in excel.

I forgot about one thing. If search returns 0 results, instead of video url i`d like macro to paste "not found" or sth like this.

[De_chopin]

file didn`t go thru for the first time.

[khemu]

I did not get much time today...created a crude one for you..will refine it tomorrow (if u want)

Changes you have to make :
1. Change maxRows to the number of searches in excel
2. list.xls is hardcoded as filename for now and location has to be on desktop

Code:

Copy      Help

run "$Desktop$\list.xls" "" "Open" "" 3|0x100; err

int maxRows = 1 ;;181
str url_pre = "http://www.youtube.com/results?search_query="
str title artist url
str temp

str intresp result_url

Excel.Workbook x._getfile("$desktop$\list.xls")
Excel.Application a=x.Application
a.Visible=TRUE; err

ExcelSheet es.ws = x.Worksheets.Item(1)
int i
for(i 1 maxRows+1)
,es.SetCell("Connecting..." 3 i)
,es.GetCell(title 1 i)
,es.GetCell(artist 2 i)
,url.format("%s%s %s" url_pre title artist)
,IntGetFile url _s
,intresp = _s
,
,_i = find(intresp "a href=''/watch" 0 1)
,temp = _s.get(_s _i-1)
,
,;GET URL
,_i = find(temp ">" 0 1)
,result_url = temp.get(temp 9 _i-10)
,result_url - "http://www.youtube.com"
,
,es.SetCell(result_url 3 i)

es.Save
mes-("All links have been downloaded !" "Done" "ia")

[De_chopin]

Wow! This works great! Thank you very much khemu, that will save me a lot of time.
Is it possible to provide additional str to define search in different ways? Like "how to play" or "music video" words plus things that are in excel?
And is semi automated version possible? Sometimes first result is rubbish so i would like to click on one of the results like from first 4 for example.

Thanks again for your help.

[khemu]

Another quickie one...
Make sure you download the new excel attached !

Changes to make:
1. maxRows - Same as before
2. Suffix1 & Suffix2
3. semi_auto - 1=4 results, 0=1 result

function E_Main

Code:

Copy      Help

int semi_auto = 0
str suffix1 = "how to play"
str suffix2 = "music video"

run "$Desktop$\list.xls" "" "Open" "" 3|0x100; err
mes("Do not click/type anywhere on the Excel while macro is running !" "Warning" "!a")

int maxRows = 2 ;;181
str title artist url
str temp

str intresp result_url result_url2 result_url3 result_url4

Excel.Workbook x._getfile("$desktop$\list.xls")
Excel.Application a=x.Application
a.Visible=TRUE; err

ExcelSheet es.ws = x.Worksheets.Item(1)
int i
for(i 2 maxRows+1)
,es.GetCell(title 1 i)
,es.GetCell(artist 2 i)

,if (suffix1)
,,es.SetCell("Connecting..." 3 i)
,,_s = E_GetUrls(semi_auto title artist suffix1)
,,es.SetCell(_s 3 i)

,if (suffix2)
,,es.SetCell("Connecting..." 4 i)
,,_s = E_GetUrls(semi_auto title artist suffix2)
,,es.SetCell(_s 4 i)

es.Save
mes-("All links have been downloaded !" "Done" "ia")

function E_GetUrls

Code:

Copy      Help

function~ semi_auto $title $artist $suf1 [$suf2]

if(!suf2)
,suf2 = ""
,
str url_pre = "http://www.youtube.com/results?search_query="
str    url.format("%s%s %s %s %s" url_pre title artist suf1 suf2)
IntGetFile url _s
str intresp = _s
,
_i = find(intresp "a href=''/watch" 0 1)
str    temp = _s.get(_s _i-1)
,
;GET URL
if (!semi_auto)
,_i = find(temp ">" 0 1)
,str result_url.get(temp 9 _i-10)
,result_url - "http://www.youtube.com"
,_s.format("%s" result_url)
else
,_i = find(temp ">" 0 1)
,result_url.get(temp 9 _i-10)
,result_url - "http://www.youtube.com"
,
,_i = find(temp "a href=''/watch" _i 1); _i = find(temp "a href=''/watch" _i+10 1); _i = find(temp "a href=''/watch" _i+10 1)
,temp.get(temp _i+8); _i = find(temp ">" 0 1)
,str result_url2.get(temp 0 _i-1)
,result_url2 - "http://www.youtube.com"

,_i = find(temp "a href=''/watch" _i 1); _i = find(temp "a href=''/watch" _i+10 1); _i = find(temp "a href=''/watch" _i+10 1)
,temp.get(temp _i+8); _i = find(temp ">" 0 1)
,str result_url3.get(temp 0 _i-1)
,result_url3 - "http://www.youtube.com"

,_i = find(temp "a href=''/watch" _i 1); _i = find(temp "a href=''/watch" _i+10 1); _i = find(temp "a href=''/watch" _i+10 1)
,temp.get(temp _i+8); _i = find(temp ">" 0 1)
,str result_url4.get(temp 0 _i-1)
,result_url4 - "http://www.youtube.com"

,_s.format("%s %s %s %s" result_url result_url2 result_url3 result_url4)

ret _s