Importing Web XML data

[MrLookout9]

MrExcel has never let me down before...
The vision: I keep a list of all the movies I've seen. I'd like to import some data about each movie.

I have found several sources that provide data in a web XML format. For instance, http://www.omdbapi.com/?s=American History X&r=XML.

What I’d like to do is grab the imdbID for each movie in my list. All movies are in column A. I’d like to have the imdbID in column B.

I am open to other approaches/ data sources.

Thank you!

 

[kocir]

sergioMabres

Of course you are great and many many many thanks from me.

 

[sergioMabres]

Here is the final answer for kocir problem
Hoja1

*	C	D	E	F	G	H	I	J	K	L
1	productUrl	name	minprice	minpriceshop	tutty.cz	Alza.cz	MALL.cz	T.S.Bohemia a.s.	CZC.cz	KASA.cz
2	*	*	*	*	*	*	*	*	*	*
3	http://gps-navigace.heureka.cz/mio-combo-5107u-lifetime/	Mio Combo 5107u Lifetime	4340	Nakupka.cz	4990	neprodává	neprodává	neprodává	neprodává	neprodává
4	http://gps-navigace.heureka.cz/mio-combo-5107u-lifetime/	Mio Combo 5107u Lifetime	4340	Nakupka.cz	4990	4790	4599	neprodává	4790	4790

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =HYPERLINK(IFERROR(FILTERXML($B2,"//div[@id='productUrl']"),"")) D2 =IFERROR(FILTERXML($B2,"//div[@id='name']"),"") E2 =IFERROR(FILTERXML($B2,"//div[@id='minprice']"),"") F2 =IFERROR(FILTERXML($B2,"//div[@id='minpriceshop']"),"") G2 =IF($F2<>"",IFERROR(FILTERXML($B2,"//div[@id='offers']//div[@id='"&G$1&"']"),"neprodává"),"") C3 =(MID($B3,FIND("id="""&C$1,$B3,1)+LEN(C$1)+6,FIND("",$B3,FIND("id="""&C$1,$B3,1))-FIND("id="""&C$1,$B3,1)-(LEN(C$1)+6))) D3 =(MID($B3,FIND("id="""&D$1,$B3,1)+LEN(D$1)+6,FIND("",$B3,FIND("id="""&D$1,$B3,1))-FIND("id="""&D$1,$B3,1)-(LEN(D$1)+6))) E3 =VALUE(MID($B3,FIND("id="""&E$1,$B3,1)+LEN(E$1)+6,FIND("",$B3,FIND("id="""&E$1,$B3,1))-FIND("id="""&E$1,$B3,1)-(LEN(E$1)+6))) F3 =(MID($B3,FIND("id="""&F$1,$B3,1)+LEN(F$1)+6,FIND("",$B3,FIND("id="""&F$1,$B3,1))-FIND("id="""&F$1,$B3,1)-(LEN(F$1)+6))) G3 =IF($F3<>"",IFERROR(VALUE(MID($B3,FIND("id="""&G$1,$B3,FIND("id=""offers",$B3,1))+14,FIND("",$B3,FIND("id="""&G$1,$B3,FIND("id=""offers",$B3,1)))-FIND("id="""&G$1,$B3,FIND("id=""offers",$B3,1))-14)),"neprodává"),"") H3 =IF($F3<>"",IFERROR(FILTERXML($B3,"//div[@id='offers']//div[@id='"&H$1&"']"),"neprodává"),"") I3 =IF($F3<>"",IFERROR(FILTERXML($B3,"//div[@id='offers']//div[@id='"&I$1&"']"),"neprodává"),"") J3 =IF($F3<>"",IFERROR(FILTERXML($B3,"//div[@id='offers']//div[@id='"&J$1&"']"),"neprodává"),"") K3 =IF($F3<>"",IFERROR(FILTERXML($B3,"//div[@id='offers']//div[@id='"&K$1&"']"),"neprodává"),"") L3 =IF($F3<>"",IFERROR(FILTERXML($B3,"//div[@id='offers']//div[@id='"&L$1&"']"),"neprodává"),"") C4 =(MID($B4,FIND("id="""&C$1,$B4,1)+LEN(C$1)+6,FIND("",$B4,FIND("id="""&C$1,$B4,1))-FIND("id="""&C$1,$B4,1)-(LEN(C$1)+6))) D4 =(MID($B4,FIND("id="""&D$1,$B4,1)+LEN(D$1)+6,FIND("",$B4,FIND("id="""&D$1,$B4,1))-FIND("id="""&D$1,$B4,1)-(LEN(D$1)+6))) E4 =VALUE(MID($B4,FIND("id="""&E$1,$B4,1)+LEN(E$1)+6,FIND("",$B4,FIND("id="""&E$1,$B4,1))-FIND("id="""&E$1,$B4,1)-(LEN(E$1)+6))) F4 =(MID($B4,FIND("id="""&F$1,$B4,1)+LEN(F$1)+6,FIND("",$B4,FIND("id="""&F$1,$B4,1))-FIND("id="""&F$1,$B4,1)-(LEN(F$1)+6))) G4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&G$1,$B4,FIND("id=""offers",$B4,1))+14,FIND("",$B4,FIND("id="""&G$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&G$1,$B4,FIND("id=""offers",$B4,1))-14)),"neprodává"),"") H4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&H$1,$B4,FIND("id=""offers",$B4,1))+LEN(H$1)+6,FIND("",$B4,FIND("id="""&H$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&H$1,$B4,FIND("id=""offers",$B4,1))-(LEN(H$1)+6))),"neprodává"),"") I4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&I$1,$B4,FIND("id=""offers",$B4,1))+LEN(I$1)+6,FIND("",$B4,FIND("id="""&I$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&I$1,$B4,FIND("id=""offers",$B4,1))-(LEN(I$1)+6))),"neprodává"),"") J4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&J$1,$B4,FIND("id=""offers",$B4,1))+LEN(J$1)+6,FIND("",$B4,FIND("id="""&J$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&J$1,$B4,FIND("id=""offers",$B4,1))-(LEN(J$1)+6))),"neprodává"),"") K4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&K$1,$B4,FIND("id=""offers",$B4,1))+LEN(K$1)+6,FIND("",$B4,FIND("id="""&K$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&K$1,$B4,FIND("id=""offers",$B4,1))-(LEN(K$1)+6))),"neprodává"),"") L4 =IF($F4<>"",IFERROR(VALUE(MID($B4,FIND("id="""&L$1,$B4,FIND("id=""offers",$B4,1))+LEN(L$1)+6,FIND("",$B4,FIND("id="""&L$1,$B4,FIND("id=""offers",$B4,1)))-FIND("id="""&L$1,$B4,FIND("id=""offers",$B4,1))-(LEN(L$1)+6))),"neprodává"),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

These formulas are to replace FILTERXML in Office 2007 using FIND and MID
Cheers
Sergio

 

[kocir]

It works very well. Just one small a problem.


When ActiveCell.FormulaR1C1 = getHtmlFromUrl("http://"www.mypage.com") insert result string to active cell, than the text is wraped and row become too outstretched. I tried to format cell as "text" by rightclick of mouse before launching the macro, but after running the macro the cell shows ###################### of course the text inside of cell is still ok. I have to go to another cell and copy format to force the cell to show real text.

Any help?

I do no like to use this "Wrap Text" to unwrap text in the cell or copy/paste format from another cell.
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

 

[sergioMabres]

Excel displays ### when the cell content contains just text and it exceeds 256 characters and the cell format is set to "Text". Usually, setting the cell format to "General" fixes this problem.
formatting - Excel displays ### for long text - what's wrong? - Super User
Cheers
Sergio

 

[kocir]

I know way Excel shows ###. I do not undarstand why Excel is using wrapimg for the result of the formula, because it was not applied for result of WEBSERVICE formula.

Anyway I solve it by this way:

ActiveCell.NumberFormat = "@"
ActiveCell.FormulaR1C1 = getHtmlFromUrl(www.mypages.com)
With Selection
.WrapText = False
.NumberFormat = "General"
End With

Maybe it is not "clean solution", but it works and cells are not "jumping" in size when the macro runs. Thanks this solution, the text is readable (NumberFormat = "General") and not wraped (WrapText = False).

 

[sergioMabres]

Great! glad to help

 

[kocir]

Sometimes =WEBSERVICE ("www.mypages.com") does not return html code, but #VALUE! .I d like to check the returned result but there is a problem with mitchmatch of formats and everytime I got error #NAME!

Is there any solution how to check if returned result is OK or not.?


myHTMLcheck = Mid(Selection.Value, 4, 4) ' The error message in my language is "#HODNOTA!"
If myHTMLcheck <> "html" Then MsgBox Mid(Selection.Value, 4, 4) ' string "html" is part of correct result