Skip to content

Instantly share code, notes, and snippets.

@eshrinivasan
Last active February 22, 2022 05:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save eshrinivasan/0393a2acb768165574dfa5c3833fb25b to your computer and use it in GitHub Desktop.
Save eshrinivasan/0393a2acb768165574dfa5c3833fb25b to your computer and use it in GitHub Desktop.
Sub FetchOMDBData(row)
On Error GoTo SearchFailed
Dim xmlhttp, JSON, k, omdbURL, params
Dim col As Integer
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
params = "&t=" & WorksheetFunction.EncodeURL(Cells(row, MOVIE_NAME_COL).value)
If Not IsEmpty(Cells(row, MOVIE_YEAR_COL).value) Then
params = params & "&y=" & Cells(row, MOVIE_YEAR_COL).value
End If
omdbURL = "http://www.omdbapi.com/?apikey=" & Sheets("Info").Range("A9").value & params
xmlhttp.Open "GET", omdbURL, False
xmlhttp.Send 'Debug.Print xmlhttp.responseText
'Debug.Print "-----------------------------------"
Set JSON = ParseJson(xmlhttp.responseText)
col = FLAG_COL
If JSON("Response") = "False"
Then
' '
Recheck with search option
' params = Replace(params, "&t=", "&s=", 1, 1)
' omdbURL = "http://www.omdbapi.com/?apikey=" & Sheet1.Range("I2").value & params
' xmlhttp.Open "GET", omdbURL, False
' xmlhttp.Send
'
' Set JSON = ParseJson(xmlhttp.responseText)
'
' If JSON("Response") = "False" Then
Cells(row, col).value = "NF"
Exit Sub
' End If
End If
Cells(row, col).value = "OK"
col = col + 1
Cells(row, col).value = JSON("Title")
col = col + 1
Cells(row, col).value = JSON("Year")
col = col + 1
Call Sheet2.Hyperlinks.Add(Cells(row, col), "http://www.imdb.com/title/" & JSON("imdbID"), TextToDisplay: = JSON("imdbID"))
col = col + 1
Cells(row, col).value = JSON("imdbRating")
col = col + 1
Cells(row, col).value = JSON("imdbVotes")
col = col + 1
Cells(row, col).value = JSON("Metascore")
col = col + 1
Cells(row, col).value = JSON("Rated")
col = col + 1
Cells(row, col).value = JSON("Released")
col = col + 1
Cells(row, col).value = JSON("Runtime")
col = col + 1
Cells(row, col).value = JSON("Genre")
col = col + 1
Cells(row, col).value = JSON("Actors")
col = col + 1
Cells(row, col).value = JSON("Plot")
col = col + 1
Cells(row, col).value = JSON("Language")
col = col + 1
Cells(row, col).value = JSON("Country")
col = col + 1
Cells(row, col).value = JSON("Poster")
col = col + 1
Exit Sub
SearchFailed:
Debug.Print Err.Number & ": " & Err.Description
Cells(row, col).value = "FAIL"
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment