0

My coding knowledge is very basic so please do bear that in mind. Basically there is a encoding service called vid.ly. We have hundreds of videos on there and would like to create an excel spreadsheet with all the information of them.

Vidly accepts API queries in XML and JSON. Below is a basic example of the query I want to make:

http://api.vid.ly/#GetMediaList

Is there a way that I can get Excel to send that query to the Vidly website, receive an XML/JSON response and make a table from it? I have gotten it to work with an XML generated manually but I really want Excel to pull that information automatically.

pnuts
  • 58,317
  • 11
  • 87
  • 139
James
  • 1
  • 1

1 Answers1

0

Sure, you need to write VBA code in excel sheet. Refer to following urls

https://msdn.microsoft.com/en-us/library/dd819156%28v=office.12%29.aspx

http://www.dotnetspider.com/resources/19-STEP-BY-STEP-Consuming-Web-Services-through-VBA-Excel-or-Word-Part-I.aspx

http://www.automateexcel.com/2004/11/14/excel_vba_consume_web_services/

Sheetal Mohan Sharma
  • 2,908
  • 1
  • 23
  • 24
  • Thanks for the information, unfortunately it's a little beyond my knoweldge of VBA. Could you recommend a better way to do what I want, even if it was outside of Excel? All I want is a list of all the videos and the ability to add some data to the records? Cheers – James Jun 05 '15 at 13:53
  • If you have XML from webservice call then you can convert that to CSV. Look at this thread http://stackoverflow.com/questions/21413978/convert-an-xml-file-to-csv-file-using-java – Sheetal Mohan Sharma Jun 05 '15 at 13:57
  • @James once you get the data from the api and process them in excel (or basically any tool that converts them into json), you can use some ready-solution to store the data and share them in cloud. For example https://www.ipushpull.com - I will be happy to help you with some simple script that converts the json into good structure for that – Tomas Jun 25 '15 at 09:29
  • H Tom, thanks for your offer of help, the part I am really struggling with is getting the data from the API, I just cannot manage it! The database I have created works excellently, I just need to feed it the data or even have a self updating XML file on our server if that is possible. Would you know of any way to do this? Thank you! – James Jun 25 '15 at 12:33
  • @Tom Sorry, forgot to tag your name – James Jun 25 '15 at 12:51
  • @James the implementation of their api really depends on the system that you are using. What language are you implementing this? They d have sandbox to play with at http://m.vid.ly/test/test_api.php which is written in php – Tomas Jun 25 '15 at 14:12
  • @Tom Well we currently store the information manually in excel so if could create an XML I could import that as a data source. Even better, I have a filmmaker database which accepts data via XML and an xmlt translation file. The filmmaker database is all written and accepts the XML generated from the sandbox, just need a way to have the data to automatically update. Cheers – James Jun 26 '15 at 15:52
  • @James XML is complication, are you sure they dont accept JSON? In any way, you could have a script (either front or backend) that takes the data from excel, puts them in valid format for that API and pushes it to api. Its really matter of wiriting that script – Tomas Jun 26 '15 at 15:58
  • @Tom They do accept JSON, it's just I have absolutely no knowledge of JSON and since neither Excel or Filemaker use it, I didn't really look into it. How could I go about making a simple backend/frontend in JSON? Thanks :D – James Jun 29 '15 at 08:43