In my Workbook Code, Sub Get_MovieData(), its using A:A to discover last row, Using the Number From Each in in column D, it then Pulls Data from a Web XML doc and filles in the rest of the Row with Data. it should then goto the Next row and use the Data from column D, however if column D is Empty, it scips to the Next Row, Makes the querry, but doesn't skip to the next row to add the Data, leaving the Data incorrect
heres The Code:
Sub Get_MovieData()
Dim rngCode As Range
Dim rng As Range
Dim xml As Object
Dim nodes As Object
Dim strURL As String
Dim arrCat As Variant
Dim I As Long
Dim j As Long
Dim LastRow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set rngCode = ActiveSheet.Range("D3")
Set xml = CreateObject("MSXML2.DOMDocument")
With xml
Set rng = ActiveSheet.Range("A3")
For j = 3 To LastRow
Set rngCode = ActiveSheet.Range("D" & j)
If rngCode <> "" Then
strURL = "http://api.themoviedb.org/2.1/Movie.getInfo/en/xml/644c8c7523000ff3de7cf4b0af9d4f8c/" & rngCode.Value
.Load strURL
Do: DoEvents: Loop Until .readyState = 4
Set nodes = .getElementsByTagName("movie")
For I = 0 To nodes.Length - 1
rng.Value = nodes(I).ChildNodes(5).nodeTypedValue 'Title
rng.Offset(0, 4).Value = nodes(I).ChildNodes(15).nodeTypedValue 'Rating
rng.Offset(0, 5).Value = Left(nodes(I).ChildNodes(16).nodeTypedValue, 4) 'Release Date
rng.Offset(0, 6).Value = nodes(I).ChildNodes(17).nodeTypedValue 'RunTime
rng.Offset(0, 7).Value = nodes(I).ChildNodes(11).nodeTypedValue 'Plot
'rng.Offset(0, 5).Value = nodes(I).ChildNodes(9).nodetypedvalue 'IMDB ID
'rng.Offset(0, 6).Value = nodes(I).ChildNodes(8).nodetypedvalue 'TMDB ID
rng.Offset(0, 8).Value = nodes(I).ChildNodes(14).nodeTypedValue 'Tag Line
rng.Offset(0, 9).Value = nodes(I).ChildNodes(21).nodeTypedValue 'Trailer
rng.Offset(0, 10).Value = nodes(I).ChildNodes(20).nodeTypedValue 'Official WebSite"
Next I
Set nodes = .getElementsByTagName("category")
If nodes.Length > 0 Then
ReDim arrCat(0 To nodes.Length - 1)
For I = 0 To nodes.Length - 1
arrCat(I) = nodes(I).Attributes(1).Value
Next I
If UBound(arrCat) > 0 Then
rng.Offset(0, 11).Value = Join(arrCat, ",") 'Genre
End If
End If
Set nodes = .getElementsByTagName("image")
For Each nd In nodes
For Each Attr In nd.Attributes
Debug.Print Attr.Name & " - " & Attr.Value
Next Attr
Next nd
rng.Offset(0, 12).Value = nodes(nd + 1).Attributes(1).Value
Set rng = rng.Offset(1)
End If
Next j
End With
With ActiveSheet
.Range("C3, D3, F3:G3").EntireColumn.AutoFit
.Range("A:A").ColumnWidth = 40
.Range("L:L").ColumnWidth = 30
.Range("C:C").ColumnWidth = 10
.Range("E:E").ColumnWidth = 7
.Range("F:F").ColumnWidth = 5
End With
End Sub
WorkBookMovies.xlsm... Working With Sheets("Movies")
Bookmarks