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!

 

[sergioMabres]

You could open the XML in some empty sheet using

    ActiveWorkbook.XmlImport URL:= _
        "http://www.omdbapi.com/?s=American History X&r=XML", ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$A$1")

Then use VLOOKUP to fetch the imdbID using the movie title as the search argument

[TABLE="width: 68"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 68"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MrLookout9]

Do you have any suggestions for replicating this 400+ times

 

[sergioMabres]

Yes I do. You could create a function that uses movie title as an argument and use "http://www.omdbapi.com/?t=" & movieTitle
and use xpath to retrieve the imdbID node so you will only have to copy the formula with the custom function to your 400 rows and voila

 

[MrLookout9]

I have not used xpath before. Would you mind elaborating? I really appreciate your expertise.

 

[sergioMabres]

I can do more than that here is a Excel 2013 workbook with what you need
https://dl.dropboxusercontent.com/u/23094164/movies.imdbID.xlsx
I hope it helps

Col A has movies titles
Col B has WEBSERVICE(" http://www.omdbapi.com/?t="&A2)
Col C has FILTERXML but is not working with JSON so I used FIND
Col D has =MID(B2;FIND("imdbID";B2)+9;9)

To do the same for Excel 2010 and below can be done to with some work
Sergio

 

[MrLookout9]

Hi Sergio, this looks great. Unfortunately I am working with Excel 2010. Did you define these functions in VBA or are the standard with 2013? I opened up Visual Basic and didn't see any code. Again, thank you for your help.

 

[sergioMabres]

It is standard Excel 2013
I found out how to get an XLM from omimdb it is with &r=XML
Here is the file using FILTERXML(XML;XPATH)
https://dl.dropboxusercontent.com/u/23094164/movies.imdbIDusingXpath%20.xlsx
Excel 2013 is sweet
I could write the function for Excel 2010 if you really need it

 

[sergioMabres]

Here it is the file for Excel 2010
https://dl.dropboxusercontent.com/u/23094164/movies.imdbIDforexcel2010.xlsm
And here it is the function that runs instead of WEBSERVICE()

Function getHtmlFromUrl(pURL As String) As String
    Dim resText As String
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET", pURL, False
    objHttp.Send ""
    getHtmlFromUrl = objHttp.ResponseText
End Function

It my pleasure to help
Sergio

 

[MrLookout9]

Bingo! You sir, are a genius. Thank you for doing what you do.