Amazon Search with an Excel UserForm

Last week I bumped into the Coding4Fun article Using the Amazon Search Web Service and made a mental note to accomplish the same result in Excel.

I didn’t actually download the example, but set about to create something based on the screenshot given.

And here’s a screenshot of my version (Download is at the bottom):

One major difference between the Coding4Fun Example and my example is the Codinging4fun uses the Amazon SOAP web service, and my example uses the REST web service.

The choice to use the REST over SOAP came down to two reasons

1. I prefer REST over SOAP 🙂

2. Using SOAP (even with Excel 2003) requires an extra Toolbox from Microsoft. Using REST requires only setting a reference to the XML parser* (in this example that is Microsoft XML, v5.0), which should be installed on any Office 2003 Machine)

Compatibility

The Amazon Search UserForm was tested in Excel 2003 on a machine with Internet Explorer installed (for navigating to the Item URL), and an Internet connection.

Notes

The search query response from Amazon is a “Large” response to return the image URL’s also. Depending on your connection speed a search make take a few seconds.

I’m still an XML parsing rookie; The code isn’t password protected so feel free to try your hand at reducing the lines of code. (or creating your own modified version (a large response provides much more information than I actually used))

Credits

I loaded the Item pictures in the form to a picture control, from a URL, using the amazing Juan Pablo Gonzalaz code.

The paths to the XML elements were discovered using the 2003 XML Toolkit

Click this link to download the Amazon Search in an Excel Userform

Try to break it, modify it, let me know what you think.

*You don’t need a reference to the XML parser if you don’t use VBA to parse the XML. Instead simply map the data to your spreadsheet cells.