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
Indexing my books
Tagged on: