Importing Web XML data

MrLookout9

New Member
Joined
Jun 25, 2011
Messages
27
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!
 
Well if you like what I do you can go to my blog and click (sever times) on the ads as a sign of appreciation, see my signature for the URL of the blog
Thanks
Sergio
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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


This thread is EXACTLY what I am trying to do here but with the exception of the find function. In this work up he is finding the keyword and scouting 9 spaces from there and collecting the 9 spaces afterwards. This theory works well for that section but what about the movie rating? For example If a movie was Rated R we could use
=MID(B2;FIND("Rated";B2)+8;1) to grab the "R"

{"Title":"Saw","Year":"2004","Rated":"R",......}

But what if the movie was Rated PG-13???

=MID(B2;FIND("Rated";B2)+8;1) would only grab the "P"
{"Title":"Saw","Year":"2004","Rated":"PG-13",......}


How can I (in excel 2010) FIND the data from say "Rated" and get everything within the parentheses after the ":"? That way I can get "Rated":"R" and "Rated":"PG-13" to display neatly?
 
Upvote 0
Hi Joe
You could look for the comma after "Rated" and extract up to the comma with a formula like
=MID(C2,FIND("Rated",C2)+8,FIND(",",C2,FIND("Rated",C2))-FIND("Rated",C2)-9)
I hope it helps
Sergio
 
Upvote 0
Sergio,
I referred to the Excel 2010 file you created. It does not retrieve the HTML data when I run it. I get the following error 'A value used in the formula is of the wrong data type'. Is it that some VBAproject references need to be checked? TIA
Regards,
George
 
Upvote 0
sifunciona.png

Hi George, I re checked the file and still works (I added a row and got the answers)
The problem could be some thing else
PC enviroment
Excel enviroment
Communication problem
Something else

Cheers
Sergio
 
Upvote 0
Sergio, thanks for the prompt response. Let me play around and try and figure out the issue.
Regards,
George
 
Upvote 0
You are a Genius, Sir. This method also worked with Excel 2007. You saved my day.
Hence I also paid my respect (Laszlo/Izzy).


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()
Code:
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
 
Upvote 0
The macro for Office 2007 is in this post, just read page 1
add the macro/function to your book
use the function =getHtmlFromUrl("http://www.somesite.com/samepage.htm") in some sheet in the book
and it should work fine
Change http://www.somesite.com/samepage.htm for your desired URL
Cheers
Sergio
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top