Using excel query to get a substring of the xml data from a URL

Question

I have the following table in excel where col1=movie ID, col2 = movie name. I'm trying to use excel web query to to get the production firm name of all the movies in my list.

ID          Movie Name
tt1540741   A Single Shot (2013)
tt4630158   A Space Program (2015 Documentary)

For example http://www.omdbapi.com/?i=tt2330270&tomatoes=true&plot=short&r=xml

The link will return a XML that contains the production firm name.

I'm not sure how to write the excel formula that will achieve this goal.

(I want the output to looks like this)

ID          Movie Name                         Production firm
tt1540741   A Single Shot (2013)                      ABC
tt4630158   A Space Program (2015 Documentary)        DEF

score 0 · Answer 1 · answered Jul 10 '16 at 14:38

Problem solved. Here is my solution. Note that the row numbers are fixed here, you can change it to be dynamic.

Column A here contains all the URL with a unique movie ID. For example

http://www.omdbapi.com/?i=tt0811137&tomatoes=true&plot=short&r=xml

Sub getMovieInfo()
'  Application.ScreenUpdating = False
  Dim x As Integer
  ' Set numrows = number of rows of data.
  NumRows = 1491
  ' Select cell a1.
  Range("A1").Select
  ' Establish "For" loop to loop "numrows" number of times.
  For x = 1 To 1491
     ' Insert your code here.
     pos = "A" & x
     des = "$D" & x
    ActiveWorkbook.XmlImport URL:=Cells.Item(x, "A"), _
    ImportMap:=Nothing, Overwrite:=True, Destination:=Sheets("Sheet3").Range(des)
   ' Sheets("Sheet1").Range(des).Value = Cells.Item(x, "A")
    Application.Wait (Now + TimeValue("00:00:2"))

    If (IsEmpty(Sheets("sheet3").Range(des)) = True) Then
    Application.Wait (Now + TimeValue("00:00:10"))
        If (IsEmpty(Sheets("sheet3").Range(des)) = True) Then
        Exit Sub
        End If
    End If

     ActiveCell.Offset(1, 0).Select
  Next
  End Sub

Using excel query to get a substring of the xml data from a URL

1 Answers1