Background
Did you know that Google also offers a service to search books? If not, try Google Books. The Google Books project aspires to digitalize the content of all books in the world. Of course, Google also keeps metadata about these books. Stronger, Google even offers you the possibility to interact with that metadata, by means of the Google Books API. The API is public and free of charge.
In what follows, we will give a detailed example of how to use the Google Books API functionality to lookup book information based on ISBN number, in VBA. The example might be interesting for several reasons.
It shows that the big IT players use JSON.
It shows how to use VBA in PowerPoint.
It shows how to use an external API in VBA.
It shows that our JSON class can make coding easier.
To download the PowerPoint presentation, click the "DOWNLOAD" button below. As the VBA code needs to communicate with the Google Books API, an internet connection is required to make the code work correctly.
Objective
In PowerPoint, while presenting, we would like to have the functionality to look up book information based on an ISBN number. Say, for instance, that our PowerPoint slide looks as follows.
The objective is to build a routine in VBA, say GetGoogleBooksInfo()
that, after clicking the button 'Get info from Google Books', takes as input the ISBN number and fills in the information about the corresponding book in the text boxes on the slide. If something goes wrong (e.g. the ISBN number does not exist), a short message should be shown on the slide. The 'Reset' button simply resets the slide.
Solution, the principle
Thanks to Google, the solution is easy. All we need to do is to send the following URL to the Google Books API.
The Google Maps API will respond, and the response will be a JSON text.
"kind": "books#volumes",
"totalItems": 1,
"items": [{[
"kind": "books#volume",
"id": "EDbcWzOnU-kC",
"etag": "WS9cJzLm/Bw",
"selfLink": "https://www.googleapis.com/books/v1/volumes/EDbcWzOnU-kC",
"volumeInfo": {
"title": "The Pearl",
"authors": [
"John Steinbeck"
],
"publisher": "Penguin",
"publishedDate": "1992",
"description": "A poor fisherman dreams of wealth and happiness for his family when he finds a priceless pearl",
"industryIdentifiers": [{
"type": "ISBN_10",
"identifier": "014017737X"
},
{
"type": "ISBN_13",
"identifier": "9780140177374"
}
],
"readingModes": {
"text": false,
"image": false
},
"pageCount": 90,
"printType": "BOOK",
"categories": [
"Fiction"
],
"averageRating": 3.5,
"ratingsCount": 91,
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "0.10.3.0.preview.0",
"panelizationSummary": {
"containsEpubBubbles": false,
"containsImageBubbles": false
},
"imageLinks": {
"smallThumbnail": "http://books.google.com/books/content?id=EDbcWzOnU-kC&printsec=frontcover&img=1&zoom=5&source=gbs_api",
"thumbnail": "http://books.google.com/books/content?id=EDbcWzOnU-kC&printsec=frontcover&img=1&zoom=1&source=gbs_api"
},
"language": "en",
"previewLink": "http://books.google.be/books?id=EDbcWzOnU-kC&dq=isbn:9780140177374&hl=&cd=1&source=gbs_api",
"infoLink": "http://books.google.be/books?id=EDbcWzOnU-kC&dq=isbn:9780140177374&hl=&source=gbs_api",
"canonicalVolumeLink": "https://books.google.com/books/about/The_Pearl.html?hl=&id=EDbcWzOnU-kC"
},
"saleInfo": {
"country": "BE",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "BE",
"viewability": "NO_PAGES",
"embeddable": false,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://play.google.com/books/reader?id=EDbcWzOnU-kC&hl=&printsec=frontcover&source=gbs_api",
"accessViewStatus": "NONE",
"quoteSharingAllowed": false
},
"searchInfo": {
"textSnippet": "A poor fisherman dreams of wealth and happiness for his family when he finds a priceless pearl"
}
}]
}
Then, it is up to us to parse out the required information and show it on the PowerPoint slide.
Solution, the PowerPoint slide
The top three objects on the slide are ActiveX controls.
To add an ActiveX control in PowerPoint, go to the 'Developper' tab in the ribbon and then use the 'Controls' part to drag and drop an ActiveX control on the slide.
Here are the names of the ActiveX controls.
TBNumberISBN
CBGetBookInfo
CBReset
To name an ActiveX control in PowerPoint, select the Active X control and click right. A menu will pop up.
Select 'Property Sheet'. Now you can set the name in the textbox next to '(Name)'.
The first ActiveX control is a textbox. While presenting the slide, an ISBN number can be typed into it. The other two ActiveX controls are buttons. As suggested by its caption, the first ActiveX button will try to get the information from Google Books, based on the ISBN number inputted in the textbox. The second ActiveX button will simply reset the textboxes to their original state. Both buttons can be pushed while presenting.
All the other objects on the slide are shapes.
To facilitate communication between VBA and the slides, it is good habit to give each shape a name. Here are the names of the shapes (that will be used in the VBA code). Just fyi, the shape with name 'Message' is the shape with no border, next to the ActiveX controls, that shows the error message. You can't see it in the picture.
Message
Title
Authors
Publisher
Pages
Categories
Rating
Description
Picture
Here is how to name a shape in PowerPoint.
Select one of the shapes on the slide, e.g. the one for the title.
Go to the 'Format' tab in the ribbon and look for the 'Selection Pane' icon.
Click on the 'Selection Pane' icon to open up the pane, on the right of the screen.
Click on the name of the shape to change it.
Solution, the VBA code
First, let's try to send the URL with VBA and obtain the response.
Open the VBA editor of the workbook (push Alt + F11). Add the following code into 'Slide1'. This code links the ActiveX control events (i.e. pushing the button) to the VBA code.
1 2 3 4 5 6 7 8 | Private Sub CBGetBookInfo_Click() GetGoogleBooksInfo Me.TBNumberISBN.Text End Sub Private Sub CBReset_Click() Reset End Sub |
Then, add a module (in the VBA editor, choose 'Insert' and then 'Module') and copy the following VBA code into it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Option Explicit 'Tools > References... > Microsoft Script Control 1.0 must be checked 'Tools > References... > Microsoft XML, v6.0 must be checked Public Sub GetGoogleBooksInfo(ByVal NumberISBN As String) On Error GoTo GetGoogleBooksInfoError Dim oHTTPRequest As MSXML2.XMLHTTP60 Set oHTTPRequest = New MSXML2.XMLHTTP60 oHTTPRequest.Open "GET", "https://www.googleapis.com/books/v1/volumes?q=isbn:" & NumberISBN oHTTPRequest.send Do While (oHTTPRequest.readyState <> 4) DoEvents Loop MsgBox oHTTPRequest.responseText 'just for now... Set oHTTPRequest = Nothing Exit Sub GetGoogleBooksInfoError: MsgBox "Unexpected error in GetGoogleBooksInfo()", vbCritical, "Error" End Sub |
The MSXML2.XMLHTTP60
object is part of the Microsoft XML
reference (see here for details). To make the code work, the reference must be checked (in the VBA editor, choose 'Tools', choose 'References...', search in the list and check it). The JSON text that the Google Books API returns will be in oHTTPRequest.responseText
and is in fact a simple String
.
Try the code by going back to the slides (leave the VBA editor), push F5 (to start presenting), move the mouse until you see the pointer and click the button 'Get info from Google Books'. The JSON text should be shown (partly) in a messagebox. If you see this, it means that your communication with Google works!
Now, let's extract what we need from the JSON text.
Let's do this in separate routine, called ExtractBookInfoFromResponseText
. Change the VBA code as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | Option Explicit 'Tools > References... > Microsoft Script Control 1.0 must be checked 'Tools > References... > Microsoft XML, v6.0 must be checked Public Sub GetGoogleBooksInfo(ByVal NumberISBN As String) On Error GoTo GetGoogleBooksInfoError Dim oHTTPRequest As MSXML2.XMLHTTP60 Set oHTTPRequest = New MSXML2.XMLHTTP60 oHTTPRequest.Open "GET", "https://www.googleapis.com/books/v1/volumes?q=isbn:" & NumberISBN oHTTPRequest.send Do While (oHTTPRequest.readyState <> 4) DoEvents Loop <span class="cVBAToNote">ExtractBookInfoFromResponseText oHTTPRequest.responseText</span> Set oHTTPRequest = Nothing Exit Sub GetGoogleBooksInfoError: MsgBox "Unexpected error in GetGoogleBooksInfo()", vbCritical, "Error" End Sub <span class="cVBAToNote">Private Sub ExtractBookInfoFromResponseText(ByRef JSONText As String)</span> <span class="cVBAToNote">End Sub</span> End Sub |
There are several possible ways to extract the distance from the JSON text. One option is to use basic string search functions, like Instr()
. After a bit of trying, we will get there for sure. There is nothing wrong with that approach, however, there may be an easier solution. Let's try the JSON class 🙂
First, add the code of our JSON class to the VBA project. Either download the class (cJSON.cls) from the website and insert the file in the VBA project. Or, add an empty class to the VBA project, name it 'cJSON', copy the VBA code of the class from the website and paste it into the empty class.
Now replace the empty routine ExtractBookInfoFromResponseText
by this one.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | Private Sub ExtractBookInfoFromResponseText(ByRef JSONText As String) On Error GoTo ExtractBookInfoFromResponseTextError Dim oSlide As Slide Set oSlide = ActivePresentation.Slides.Item(1) Dim oShape As PowerPoint.Shape oSlide.Shapes("Message").TextFrame.TextRange.Text = "" Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONText) If (JSON.IsOk()) Then If (D.Item("totalItems") > 0) Then If (D.Item("totalItems") = 1) Then oSlide.Shapes("Title").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("title") oSlide.Shapes("Authors").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("authors").Item("0") oSlide.Shapes("Publisher").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("publisher") oSlide.Shapes("Pages").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("pageCount") oSlide.Shapes("Categories").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("categories").Item("0") oSlide.Shapes("Description").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("description") oSlide.Shapes("Rating").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("averageRating") & " (n=" & D.Item("items").Item("0").Item("volumeInfo").Item("ratingsCount") & ")" oSlide.Shapes("Picture").Visible = msoFalse If (DoesShapeExist(oSlide, "PictureCover")) Then oSlide.Shapes("PictureCover").Delete End If Set oShape = oSlide.Shapes.AddPicture( _ FileName:=CStr(D.Item("items").Item("0").Item("volumeInfo").Item("imageLinks").Item("smallThumbnail")), _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=oSlide.Shapes("Picture").Left, _ Top:=oSlide.Shapes("Picture").Top) oShape.Name = "PictureCover" oSlide.Shapes("Title").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Authors").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Publisher").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Pages").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Categories").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Rating").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Description").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) oSlide.Shapes("Picture").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "Multiple books found" End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "No book found" End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = JSON.ShowWhyNotOk() End If Set oShape = Nothing Set oSlide = Nothing Set JSON = Nothing Set D = Nothing Exit Sub ExtractBookInfoFromResponseTextError: MsgBox "Unexpected error in ExtractBookInfoFromResponseText()", vbCritical, "Error" End Sub |
All the code is standard VBA coding in PowerPoint. First, we are putting the data that is in the JSON text into a VBA Dictionary. Second, the code extracts (aggresively) data from the VBA Dictionary and puts it inside the shapes on the slide. To keep the example small, we have omitted most of the safety coding. Of course, before extracting an item from the dictionary, it is better to test first if it exists. For those interested, we have added the more safe version of the routine ExtractBookInfoFromResponseText
further.
To end the VBA coding, also add these extra routines to the VBA project.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | Public Sub Reset() On Error GoTo ResetError Dim oSlide As Slide Set oSlide = ActivePresentation.Slides.Item(1) oSlide.Shapes("Message").TextFrame.TextRange.Text = "" oSlide.Shapes("Title").TextFrame.TextRange.Text = "<title>" oSlide.Shapes("Authors").TextFrame.TextRange.Text = "<authors>" oSlide.Shapes("Publisher").TextFrame.TextRange.Text = "<publisher>" oSlide.Shapes("Pages").TextFrame.TextRange.Text = "<number of pages>" oSlide.Shapes("Categories").TextFrame.TextRange.Text = "<categories>" oSlide.Shapes("Rating").TextFrame.TextRange.Text = "<rating>" oSlide.Shapes("Description").TextFrame.TextRange.Text = "<description>" oSlide.Shapes("Picture").TextFrame.TextRange.Text = "<picture>" oSlide.Shapes("Picture").Visible = msoTrue If (DoesShapeExist(oSlide, "PictureCover")) Then oSlide.Shapes("PictureCover").Delete End If oSlide.Shapes("Title").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Authors").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Publisher").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Pages").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Categories").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Rating").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Description").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) oSlide.Shapes("Picture").TextFrame.TextRange.Font.Color.RGB = RGB(127, 127, 127) Set oSlide = Nothing Exit Sub ResetError: MsgBox "Unexpected error in Reset()", vbCritical, "Error" End Sub Private Function DoesShapeExist(ByRef oSlide As PowerPoint.Slide, ByVal NameOfShape As String) As Boolean On Error GoTo DoesShapeExisterror Dim S As String S = oSlide.Shapes(NameOfShape).Name DoesShapeExist = True Exit Function DoesShapeExisterror: DoesShapeExist = False End Function |
The Reset
routine handles resetting the slide. The function DoesShapeExist
simply checks whether a shape with a given name exists on a sheet. This function is needed to handle the shape that will hold the picture on the cover. Admitted, there may be other and better ways to handle pictures (like by using a picture placeholder on the master slide). To focus on the JSON, we wanted to keep the pure PowerPointing in the example as simple as possible.
Let's now try the VBA code again (start the presentation, click the button 'Get info from Google Books'). You should obtain this. Objective achieved!
Solution, the VBA code revisited
Case 1: non existing ISBN number
Let's see what happens when the ISBN number does not exist. Try to look for '9780140177375' (changing the 4 at the end into a 5).
The VBA code generates and error ('No books found') and shows it on the slide. Here is the JSON text that the Google Books API has sent back in this case.
"kind": "books#volumes",
"totalItems": 0
}
The Google Maps API has sent a valid JSON text, i.e. it matches the JSON grammar rules. However, the content is different. In this case, there are no items in the JSON text, which makes sense. By giving an error message, the VBA code handles this case fine.
Let's take another case.
Case 2: empty ISBN number
Delete the content of textbox containing the ISBN number. Push the button 'Get info from Google Books'.
The error message on the slide ('Multiple books found') suggests that multiple books have been found. Multiple books? For an empty IBSN number? Yes. Let's have a closer look at the JSON text that the Google Maps API has sent back. Here is a part of it.
"kind": "books#volumes",
"totalItems": 1062,
"items": [{
"kind": "books#volume",
"id": "t5rgAAAAMAAJ",
"etag": "/24Fn4ErM/M",
"selfLink": "https://www.googleapis.com/books/v1/volumes/t5rgAAAAMAAJ",
"volumeInfo": {
"title": "ISBN Review",
"authors": [
"International ISBN Agency"
],
"publishedDate": "1998",
"industryIdentifiers": [{
"type": "OTHER",
"identifier": "UOM:39015081566500"
}],
"readingModes": {
"text": false,
"image": false
},
"printType": "BOOK",
"categories": [
"International Standard Book Numbers"
],
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "preview-1.0.0",
"imageLinks": {
"smallThumbnail": "http://books.google.com/books/content?id=t5rgAAAAMAAJ&printsec=frontcover&img=1&zoom=5&source=gbs_api",
"thumbnail": "http://books.google.com/books/content?id=t5rgAAAAMAAJ&printsec=frontcover&img=1&zoom=1&source=gbs_api"
},
"language": "en",
"previewLink": "http://books.google.be/books?id=t5rgAAAAMAAJ&q=isbn:&dq=isbn:&hl=&cd=1&source=gbs_api",
"infoLink": "http://books.google.be/books?id=t5rgAAAAMAAJ&dq=isbn:&hl=&source=gbs_api",
"canonicalVolumeLink": "https://books.google.com/books/about/ISBN_Review.html?hl=&id=t5rgAAAAMAAJ"
},
"saleInfo": {
"country": "BE",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "BE",
"viewability": "NO_PAGES",
"embeddable": false,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://play.google.com/books/reader?id=t5rgAAAAMAAJ&hl=&printsec=frontcover&source=gbs_api",
"accessViewStatus": "NONE",
"quoteSharingAllowed": false
}
},
{
"kind": "books#volume",
"id": "EnM8PwAACAAJ",
"etag": "kvroM67GRuo",
"selfLink": "https://www.googleapis.com/books/v1/volumes/EnM8PwAACAAJ",
"volumeInfo": {
"title": "ISBN",
"subtitle": "handleiding voor het Internationaal Standaard Boeknummer",
"authors": [
"Bureau ISBN Nederland"
],
"publishedDate": "1995",
"industryIdentifiers": [{
"type": "ISBN_10",
"identifier": "9070075954"
},
{
"type": "ISBN_13",
"identifier": "9789070075958"
}
],
"readingModes": {
"text": false,
"image": false
},
"pageCount": 40,
"printType": "BOOK",
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "preview-1.0.0",
"language": "nl",
"previewLink": "http://books.google.be/books?id=EnM8PwAACAAJ&dq=isbn:&hl=&cd=2&source=gbs_api",
"infoLink": "http://books.google.be/books?id=EnM8PwAACAAJ&dq=isbn:&hl=&source=gbs_api",
"canonicalVolumeLink": "https://books.google.com/books/about/ISBN.html?hl=&id=EnM8PwAACAAJ"
},
"saleInfo": {
"country": "BE",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "BE",
"viewability": "NO_PAGES",
"embeddable": false,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://play.google.com/books/reader?id=EnM8PwAACAAJ&hl=&printsec=frontcover&source=gbs_api",
"accessViewStatus": "NONE",
"quoteSharingAllowed": false
}
},
[...]
}
]
}
In this case, the Google Books has sent back a JSON text that contains 10 items (although the 'totalItems' is set to 1062). The JSON text is valid. As the slide assumes output for exactly one book, the VBA code returns an error message ('Multiple books') and shows it on the slide. This is ok, however, in this case, it would probably make more sense to catch the error before sending the request to the Google Books API and return an error like 'Missing ISBN number'.
The two cases presented so far can both be avoided by validating the ISBN number before sending it to Google Books. For help on how to do that, look here.
Here is another case.
Case 3
Let's see what happens when you try the ISBN number '9780131103627', which is the ISBN number of the book 'C Programming Language, 2nd Edition' by Brian W. Kernighan and Dennis M. Ritchie.
Note that there is only one author on the slide. Here is the JSON text that the Google Books API has sent back.
"kind": "books#volumes",
"totalItems": 1,
"items": [{
"kind": "books#volume",
"id": "161QAAAAMAAJ",
"etag": "mevoHHo/odM",
"selfLink": "https://www.googleapis.com/books/v1/volumes/161QAAAAMAAJ",
"volumeInfo": {
"title": "The C Programming Language",
"authors": [
"Brian W. Kernighan",
"Dennis M. Ritchie"
],
"publishedDate": "1988",
"description": "Introduces the features of the C programming language, discusses data types, variables, operators, control flow, functions, pointers, arrays, and structures, and looks at the UNIX system interface",
"industryIdentifiers": [{
"type": "ISBN_10",
"identifier": "0131103628"
},
{
"type": "ISBN_13",
"identifier": "9780131103627"
}
],
"readingModes": {
"text": false,
"image": false
},
"pageCount": 272,
"printType": "BOOK",
"categories": [
"Computers"
],
"averageRating": 4.5,
"ratingsCount": 106,
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "0.8.4.0.preview.0",
"panelizationSummary": {
"containsEpubBubbles": false,
"containsImageBubbles": false
},
"imageLinks": {
"smallThumbnail": "http://books.google.com/books/content?id=161QAAAAMAAJ&printsec=frontcover&img=1&zoom=5&source=gbs_api",
"thumbnail": "http://books.google.com/books/content?id=161QAAAAMAAJ&printsec=frontcover&img=1&zoom=1&source=gbs_api"
},
"language": "en",
"previewLink": "http://books.google.be/books?id=161QAAAAMAAJ&dq=isbn:9780131103627&hl=&cd=1&source=gbs_api",
"infoLink": "http://books.google.be/books?id=161QAAAAMAAJ&dq=isbn:9780131103627&hl=&source=gbs_api",
"canonicalVolumeLink": "https://books.google.com/books/about/The_C_Programming_Language.html?hl=&id=161QAAAAMAAJ"
},
"saleInfo": {
"country": "BE",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "BE",
"viewability": "NO_PAGES",
"embeddable": false,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://play.google.com/books/reader?id=161QAAAAMAAJ&hl=&printsec=frontcover&source=gbs_api",
"accessViewStatus": "NONE",
"quoteSharingAllowed": false
},
"searchInfo": {
"textSnippet": "As we said in the first preface to the first edition, C "wears well as one's experience with it grows." With a decade more experience, we still feel that way. We hope that this book will help you to learn C and use it well."
}
}]
}
In the JSON text that Google Books has sent back, both authors are mentionned. Hence, the error must be in the VBA code (can you find it?). The JSON value associated to the name 'authors' is a JSON array. The error is not in the data exchange with VBA: the item in the VBA dictionary that is associated to the key 'authors' is a VBA Dictionary that contains both authors. The first one has key "0", the second one has key "1". The expression TypeOf D.Item("items").Item("0").Item("volumeInfo").Item("authors") Is Dictionary
evaluates to True
. The error is in the way that the author data is extracted from the VBA dictionary. By doing oSlide.Shapes("Authors").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("authors").Item("0")
only the first item (i.e. the first author) is extracted. To have all the authors, you need to run through all the keys in the VBA Dictionary that are associated to the key 'authors'. Actually, we have made the error on purpose. We wanted to keep the VBA code as easy as possible, even at the expense of a small error 🙂
Let's now improve the code.
Better code
To write better code, you need to spend some moments studying the structure of the JSON text that is returned. As a book can have multiple authors, Google Books creates a JSON array to contain the authors. Similarly, as a book can be in multiple categories, the categories are also in a JSON array. To improve the VBA code, we should take this JSON text design into account. Another improvement consists of testing whether or not an item exists in the VBA Dictionary before extracting it. It makes the VBA code longer, but safer.
Replace the content of the function ExtractBookInfoFromResponseText
by this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | Private Sub ExtractBookInfoFromResponseText(ByRef JSONText As String) On Error GoTo ExtractBookInfoFromResponseTextError Dim oSlide As Slide Set oSlide = ActivePresentation.Slides.Item(1) Dim oShape As PowerPoint.Shape oSlide.Shapes("Message").TextFrame.TextRange.Text = "" Dim V As Variant Dim Index As Long Dim S As String Dim JSON As cJSON Set JSON = New cJSON Dim D As Dictionary Set D = JSON.Deserialize(JSONText) If (JSON.IsOk()) Then If (D.Item("totalItems") > 0) Then If (D.Item("totalItems") = 1) Then If (D.Exists("items")) Then If (D.Item("items").Exists("0")) Then If (D.Item("items").Item("0").Exists("volumeInfo")) Then If (D.Item("items").Item("0").Item("volumeInfo").Exists("title")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("title") oSlide.Shapes("Title").TextFrame.TextRange.Text = MapVariantToString(V) oSlide.Shapes("Title").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("authors")) Then S = "" Index = 0 Do While (D.Item("items").Item("0").Item("volumeInfo").Item("authors").Exists(CStr(Index))) V = D.Item("items").Item("0").Item("volumeInfo").Item("authors").Item(CStr(Index)) S = S & MapVariantToString(V) & "," Index = Index + 1 Loop oSlide.Shapes("Authors").TextFrame.TextRange.Text = Mid(S, 1, Len(S) - 1) oSlide.Shapes("Authors").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("publisher")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("publisher") oSlide.Shapes("Publisher").TextFrame.TextRange.Text = MapVariantToString(V) oSlide.Shapes("Publisher").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("pageCount")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("pageCount") oSlide.Shapes("Pages").TextFrame.TextRange.Text = MapVariantToString(V) oSlide.Shapes("Pages").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("categories")) Then S = "" Index = 0 Do While (D.Item("items").Item("0").Item("volumeInfo").Item("categories").Exists(CStr(Index))) V = D.Item("items").Item("0").Item("volumeInfo").Item("categories").Item(CStr(Index)) S = S & MapVariantToString(V) & "," Index = Index + 1 Loop oSlide.Shapes("Categories").TextFrame.TextRange.Text = Mid(S, 1, Len(S) - 1) oSlide.Shapes("Categories").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("description")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("description") oSlide.Shapes("Description").TextFrame.TextRange.Text = MapVariantToString(V) oSlide.Shapes("Description").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("averageRating")) Then If (D.Item("items").Item("0").Item("volumeInfo").Exists("ratingsCount")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("averageRating") S = MapVariantToString(V) V = D.Item("items").Item("0").Item("volumeInfo").Item("ratingsCount") S = S & " (n=" & MapVariantToString(V) & ")" oSlide.Shapes("Rating").TextFrame.TextRange.Text = S oSlide.Shapes("Rating").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If End If If (D.Item("items").Item("0").Item("volumeInfo").Exists("imageLinks")) Then If (D.Item("items").Item("0").Item("volumeInfo").Item("imageLinks").Exists("smallThumbnail")) Then V = D.Item("items").Item("0").Item("volumeInfo").Item("imageLinks").Item("smallThumbnail") oSlide.Shapes("Picture").Visible = msoFalse If (DoesShapeExist(oSlide, "PictureCover")) Then oSlide.Shapes("PictureCover").Delete End If Set oShape = oSlide.Shapes.AddPicture( _ FileName:=MapVariantToString(V), _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=oSlide.Shapes("Picture").Left, _ Top:=oSlide.Shapes("Picture").Top) oShape.Name = "PictureCover" oSlide.Shapes("Picture").TextFrame.TextRange.Font.Color.RGB = RGB(0, 0, 0) End If End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "'volumeInfo' is missing." End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "'items' is empty." End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "'items' are is missing." End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "Multiple books found" End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = "No book found" End If Else oSlide.Shapes("Message").TextFrame.TextRange.Text = JSON.ShowWhyNotOk() End If Set oShape = Nothing Set oSlide = Nothing Set JSON = Nothing Set D = Nothing Exit Sub ExtractBookInfoFromResponseText2Error: MsgBox "Unexpected error in ExtractBookInfoFromResponseText()", vbCritical, "Error" End Sub |
The extra lines of VBA code add more safety and mirror the design of the JSON text in a better way. The resulting code is considerably longer, but, still remains quite readable, no?
Now rerun case 3. The result looks better now.
Closing remark
Would we obtain the same result in the browser? Go to https://books.google.com and search for the ISBN number '9780140177374'.
At the moment of writing, Google Books returns us this.
Note that ISBN number in the url https://books.google.be/books?isbn=014017737X
is sligtly different from the one that you have inputted. ThThe ISBN number 0140177737X is the 10-digit version (with X being the check digit) while 9780140177374 is the 13-digit version (with the 4 at the end being the check digit). Now click 'The Pearl' to see more details. The page you get looks a bit like the powerpoint slide. You will certainly recognize some elements. Of course, there is more info on the webpage.