I’m currently packing books into boxes, in preparation for January, when I expect to be moving out of this apartment and in to a house a little way up the road. It occurred to me that it’d be nice to have an index of my books – at least the titles. I’ve borrowed a barcode scanner from work, and hooked it up to Excel to record the ISBNs of all the books with barcodes (yes, some books don’t have one, that’s life).
The problem is, ISBNs aren’t very useful to me – I can’t memorize that 9780521809269 is The Art of Electronics (never mind that I still know that 205.214.199.1 was the DNS server for SunBeach in Barbados in the 1990s and early 2000s). So, some VBA is needed, and a good source of ISBN -> metadata.
OpenLibrary looked promising initially, and this code is designed to fetch data from them. They return JSON (or Javascript-wrapped JSON), and the jscmd=data option ensures you get all the data like title and authors. Unfortunately, their database seems a bit lacking compared to others. So, back to searching for a way to get all the titles (and authors perhaps) without forking over money or doing it by hand with copy and paste.
Sub ISBN()
Dim urlBase As String
Dim olURL As String
Dim myKey As String
urlBase = "https://openlibrary.org/api/books?format=json&jscmd=data&bibkeys=ISBN:"
Do
r = CStr(ActiveCell.Value)
Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", urlBase + r, False
.send
End With
myKey = "ISBN:" + r
If hReq.responseText <> "{}" Then
Set JSON = JsonConverter.ParseJson(hReq.responseText)
Set Data = JSON(myKey)
On Error Resume Next
ActiveCell.Offset(0, 1).Value = Data("title")
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
End Sub