Part 2a: Using VBA with JSON: Courtlistener

This Lesson’s Code on Github

Today, we’re going to go over how to use VBA to interact with JSON around the web, so you’ll need one piece of software for now, Microsoft Office. When we get to PDFs later, you will need Acrobat (not Reader, but Acrobat). We’ll learn how to get information off the internet via JSON parsing in VBA into variables.

So if you’re in a legal profession, you already know what CourtListener is. It’s a nonprofit’s website where you can look up case law. But instead of going to their website in a browser, you can also perform these searches automatically with VBA and JSON or even pull them out of your Word document automatically to search for them and write VBA code to hyperlink phrases/words in your brief.

Once you get this info stored in variables, you can place it basically wherever you want in an Office file. You could put it on a CD label in Publisher, in a Word document, Excel sheet, or on a slide in PowerPoint, for example, all automated.

JSON

JSON stands for JavaScript Object Notation. It is a text string loaded with information that lots of places use to communicate over the internet. The important part is, it’s structured in a way that you can extract pieces of information out of it to use in coding. Many companies have an API that uses JSON that you probably work with every day such as Clio, CourtListener, PayPal, Wunderlist, OneNote, and many others. You can use it to communicate with these companies over the internet and automate the things you do in their software. We will be going over some in the coming weeks. For a more in-depth explanation, I recommend this site. I receive no remuneration for linking that (or anything here); I just like the explanation and think it is sufficient for this exercise.

References

We’ll be working in an empty database. I have provided an almost empty database for this article, which you can find here. It contains three modules and one empty table. Save the database so that you can hit Ctrl + S frequently and save all the time. Get into the habit of it if you can. If you use this database, you may skip down to the section “Security“. If you do not wish to download the sample, continue and we’ll go through setting up our database manually.

Open a new empty database in Access. Just open up Access and select “blank database”. Save it as any name. It’s just a sandbox or testing area, so don’t worry about what it’s called. Once you’ve saved your new empty database, go to Database Tools on the top row above the ribbon and hit ‘Visual Basic’. Now, you should see the following screen:

A module is the white screen you’re looking at in the VBA window. You can name your modules. You may have multiple modules within one file. This is where all your macros and code are stored. But yours will all be empty and we do not want that, so import the two modules from the following two links, VBA Dictionary & JSON Converter. Right click on the “Modules” folder, then import JSON Converter . Right click on “Class Modules”, then import VBA Dictionary. Next, you’ll want to create a third regular module, the one you’ll be editing code in. Mine is named ‘CourtListener’. Save again.

You will need to add the following references. References are like libraries that let you use the code we’re learning. These references are what allow ‘early binding’ to occur like we discuss in a little bit. But the more references you add, the bigger your application is and the longer it takes to load, so you only want to turn on the ones you are actually using. Here are the ones we will be using throughout the course of your journey here. The starred ones are the only ones we are actually using today and they are the only ones turned on in the sample database. So if you want to just turn those on, that’s sufficient for this post. But these unstarred ones, you’ll need in later weeks as we progress in the blog.

Visual Basic for Applications *
Microsoft Office 16.0 Access database engine Object Library *
OLE Automation *
Microsoft Scripting Runtime *
Microsoft Access 16.0 Object Library
Microsoft Word 16.0 Object Library
Microsoft Internet Controls
Microsoft Excel 16.0 Object Library
Microsoft XML, v6.0
Microsoft Office 16.0 Object Library
Microsoft Outlook 16.0 Object Library
Microsoft Publisher 16.0 Object Library
Microsoft Shell Controls and Automation
Microsoft HTML Object Library
Microsoft WinHTTP Services, version 5.1
Microsoft OneNote 15.0 Object Library

If you have Adobe Acrobat, you want to make sure these are checked on as well. We are not using any of these for this exercise, so you may leave them unchecked.

AFormAut 1.0 Type Library
Adobe Acrobat 9.0 Type Library
Acrobat Distiller

Select references in the list that look similar to the one shown here.

Security

You should never hard-code a token, password, username, client secret, secret I.D., or anything else you wish to keep safe right into your software or database, meaning you do NOT EVER write in your code something like this:

sToken = "2389407sejklfasdjl23908"

If you do this and someone breaks into your database, they will also have your various secret codes/passwords/etc. At the very least, you should store the various secret phrases, tokens, etc we’ll be discussing in a local text file on a local hard drive, NOT shared, and only give access to it from the current user. Then in theory, after you’ve done that, you could call on it as a variable when you need it and clear it out of memory as soon as you don’t.

Restrict the text file’s access to only your current user allowed or at the very least do not ‘share’ it or put it on a network drive, OneDrive, or a public repository on GitHub (yes, that really happens) or any place like that.

Always use HTTPS for URLs. Do NOT send data over http unless you do not care if the entire world sees it, actors good AND bad, so to speak.

Early Versus Late Binding

Early binding is where you declare a “reference”. As we talked about earlier, you can look at these references by going to Tools –> References in the menu up at the top. With early binding, because the function knows what each object will be doing at start, Intellisense can help you pick out the correct piece of code. It makes auto-fill suggestions for your code.

In late binding, you just declare an object and tell it at the last minute what it should do. But because the object doesn’t know what to do until after the function’s started, Intellisense doesn’t work, among other things. As opposed to the picture below, simply nothing would pop up as you type.

Most of the time, we will be using early binding with references because it is easier to do as a new person, but this exercise will be one example of late binding. Importantly, you do not need to turn on references with late binding. For our purposes, early binding is usually preferred to late.

Intellisense in action with early binding.
In late binding, this will *never* pop up.

VBA and JSON, CourtListener:

For now, we’re just going to learn about how to get the information from the internet, CourtListener in our example, to your Visual Basic for Applications screen. Later, we will learn how to pull information out of AND into Word automatically.

Now, if you are using my sample database, you may go to the next headline, “Explanation of Code“. If you are using your own empty database, next you should get the entire piece of code from below the headline “Final Code“, found towards the bottom of this post, paste it into your “third” module, save again, and then continue from here. The “third” module in my sample is named ‘CourtListener’. Save again. If you click the link “Final Code“, there will be another link to come back up here once you’ve copied the code.

Explanation of Code

Pro Tip: Put your insertion point on a line and click the tan area to the left of the line in your VBA window. See the red circle that comes up? Now, when you run your function, it will run the code to that specific line and stop. If you hit ‘run’ subsequently once, it will continue the code, but if you want it to stop entirely, you must hit the ‘stop’ button once more. You will find this extremely useful for debugging your code. Left click on the red circle to remove it.

For this exercise, place a break on the line “Debug.Print “——————————————–“.

Here’s the opener of this code. You’ll see the info box I wrote and then some variable declarations. When you do not specify a variable type, it defaults to the variant type.

'====================================================================
' Name        : fCourtListener
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fCourtListener()
' Description : gets URLs & search listings from CourtListener (case law)
'========================================================================
'declare variables
Dim sAbsoluteURL As String, sURL As String, apiWaxLRS As String
Dim sInputState As String, sToken As String
Dim sInput1 As String, sInput2 As String, sInputCourt As String
Dim sCourt As String
'variant variables
Dim rep, resp, sCitation, oEntry

Dim sID As Object, oCitations As Object
Dim oRequest As Object, vDetails As Object

Dim x As Integer, y As Integer
Dim Parsed As Dictionary

Now, the next three variables are ones you input/select. An input prompt will pop up for each of these variables and you will input the value for it. sInput1 and sInput2 are search words or phrases that you want to search for in the case law. sInputState is either the name of a state, the word ‘federal’, or the word ‘special’. You may also leave blank either sInput2 or sInputState. I have used “Westview”, “”, “Washington” as the search inputs for this post. We’ll come back to this in a little bit when we run the whole code. For now, I’m just explaining what’s going on here.

'search term 1
sInput1 = _
    InputBox("Enter a word or phrase to search for on CourtListener.")
'search term 2
sInput2 = InputBox _
    ("Enter another word or phrase to search for on CourtListener.")

'enter name of state here or 'federal' or 'special'
sInputState = InputBox _
     ("Enter state name, 'federal', or 'special' here.")

Depending on which sInputState you select, those are the courts CourtListener will search for your phrase. I have made a convenient If-elseif statement that handles it by one of either the state name, ‘federal’, or ‘special’, without the quotes. Each value represents a list of certain courts (Washington for all Washington courts, special for all special, federal for all federal) that you will likely be familiar with:

If sInputState = "Alabama" Then
   sInputCourt = "almd+alnd+alsd+almb+alnb+alsb+ala+alactapp" & _
       "+alacrimapp+alacivapp"
ElseIf sInputState = "Alaska" Then
     sInputCourt = "akd+akb+alaska+alaskactapp"
ElseIf sInputState = "Arizona" Then
     sInputCourt = "azd+arb+ariz+arizctapp+ariztaxct"
ElseIf sInputState = "Arkansas" Then
    sInputCourt = "ared+arwd+areb+arwb+ark+arkctapp+" & _
       "arkworkcompcom+arkag"
ElseIf sInputState = "California" Then
   sInputCourt = "cacd+caed+cand+casd+californiad+caca+cacb+caeb+" & _
      "canb+casb+cal+calctapp+calappdeptsuper+calag"
ElseIf sInputState = "Colorado" Then
     sInputCourt = "cod+cob+colo+coloctapp+coloworkcompcom+coloag"
ElseIf sInputState = "Connecticut" Then
    sInputCourt = "ctd+ctb+conn+connappct+connsuperct+connworkcompcom"
ElseIf sInputState = "Delaware" Then
    sInputCourt = "ded+circtdel+deb+del+delch+delsuperct+" & _
       "delctcompl+delfamct+deljudct"
ElseIf sInputState = "Florida" Then
   sInputCourt = "flmd+flnd+flsd+flmb+flnb+flsb+fla+fladistctapp" & _
       "+flaag"
ElseIf sInputState = "Georgia" Then
     sInputCourt = "gamd+gand+gasd+gamb+ganb+gasb+ga+gactapp"
ElseIf sInputState = "Hawaii" Then
     sInputCourt = "hid+hib+haw+hawapp"
ElseIf sInputState = "Idaho" Then
     sInputCourt = "idd+idb+idaho+idahoctapp"
ElseIf sInputState = "Illinois" Then
   sInputCourt = "ilcd+ilnd+ilsd+illinoised+illinoisd+ilcb+ilnb" & _
      "+ilsb+ill+illappct"
ElseIf sInputState = "Indiana" Then
     sInputCourt = "innd+insd+indianad+innb+insb+ind+indctapp+indtc"
ElseIf sInputState = "Iowa" Then
     sInputCourt = "iand+iasd+ianb+iasb+iowa+iowactapp"
ElseIf sInputState = "Kansas" Then
     sInputCourt = "ksd+ksb+kan+kanctapp+kanag"
ElseIf sInputState = "Kentucky" Then
     sInputCourt = "kyed+kywd+kyeb+kywb+ky+kyctapp+kyctapphigh"
ElseIf sInputState = "Louisiana" Then
     sInputCourt = "laed+lamd+lawd+laeb+lamb+lawb+la+lactapp+laag"
ElseIf sInputState = "Maine" Then
     sInputCourt = "med+bapme+meb+me"
ElseIf sInputState = "Maryland" Then
     sInputCourt = "mdd+mdb+md+mdctspecapp+mdag"
ElseIf sInputState = "Massachusetts" Then
     sInputCourt = "mad+bapma+mab+mass+massappct+masssuperct" & _
        "+massdistct+maworkcompcom"
ElseIf sInputState = "Michigan" Then
     sInputCourt = "mied+miwd+mieb+miwb+mich+michctapp"
ElseIf sInputState = "Minnesota" Then
     sInputCourt = "mnd+mnb+minn+minnctapp+minnag"
ElseIf sInputState = "Mississippi" Then
     sInputCourt = "msnd+mssd+msnb+mssb+miss+missctapp"
ElseIf sInputState = "Missouri" Then
     sInputCourt = "moed+mowd+moeb+mowb+mo+moctapp+moag"
ElseIf sInputState = "Montana" Then
     sInputCourt = "mtd+mtb+mont+monttc+montag"
ElseIf sInputState = "Nebraska" Then
     sInputCourt = "ned+nebraskab+neb+nebctapp+nebag"
ElseIf sInputState = "Nevada" Then
     sInputCourt = "nvd+nvb+nev"
ElseIf sInputState = "New Hampshire" Then
     sInputCourt = "nhd+nhb+nh"
ElseIf sInputState = "New Jersey" Then
     sInputCourt = "njd+njb+nj+njsuperctappdiv+njtaxct+njch"
ElseIf sInputState = "New Mexico" Then
     sInputCourt = "nmd+nmb+nm+nmctapp"
ElseIf sInputState = "New York" Then
   sInputCourt = "nyed+nynd+nysd+nywd+nyeb+nynb+nysb+nywb+ny" & _
      "+nyappdiv+nyappterm+nysupct+nyfamct+nysurct+nycivct" & _
      "+nycrimct+nyag"
ElseIf sInputState = "North Carolina" Then
   sInputCourt = "nced+ncmd+ncwd+circtnc+nceb+ncmb+ncwb+nc" & _
        "+ncctapp+ncsuperct+ncworkcompcom"
ElseIf sInputState = "North Dakota" Then
     sInputCourt = "ndd+ndb+nd+ndctapp"
ElseIf sInputState = "Ohio" Then
  sInputCourt = "ohnd+ohsd+ohiod+ohnb+ohsb+ohio+ohioctapp+ohioctcl"
ElseIf sInputState = "Oklahoma" Then
     sInputCourt = "oked+oknd+okwd+okeb+oknb+okwb+okla" & _
        "+oklacivapp+oklacrimapp+oklajeap+oklacoj+oklaag"
ElseIf sInputState = "Oregon" Then
     sInputCourt = "ord+orb+or+orctapp+ortc"
ElseIf sInputState = "Pennsylvania" Then
     sInputCourt = "paed+pamd+pawd+pennsylvaniad+paeb+pamb+" & _
        "pawb+pa+pasuperct+pacommwct+cjdpa+stp"
ElseIf sInputState = "Rhode Island" Then
     sInputCourt = "rid+rib+ri+risuperct"
ElseIf sInputState = "South Carolina" Then
  sInputCourt = "scd+southcarolinaed+southcarolinawd+scb+sc+scctapp"
ElseIf sInputState = "South Dakota" Then
     sInputCourt = "sdd+sdb+sd"
ElseIf sInputState = "Tennessee" Then
  sInputCourt = "tned+tnmd+tnwd+tennessed+circttenn+tneb+tnmb" & _
      "+tnwb+tennesseeb+tenn+tennctapp+tenncrimapp+tennsuperct"
ElseIf sInputState = "Texas" Then
  sInputCourt = "txed+txnd+txsd+txwd+txeb+txnb+txsb+txwb+tex+" & _
      "texapp+texcrimapp+texreview+texjpml+texag+sttex"
ElseIf sInputState = "Utah" Then
     sInputCourt = "utd+utb+utah+utahctapp"
ElseIf sInputState = "Vermont" Then
     sInputCourt = "vtd+vtb+vt+vtsuperct"
ElseIf sInputState = "Virginia" Then
     sInputCourt = "vaed+vawd+vaeb+vawb+va+vactapp"
ElseIf sInputState = "Washington" Then
     sInputCourt = "waed+wawd+waeb+wawb+wash+washctapp+washag"
ElseIf sInputState = "West Virginia" Then
     sInputCourt = "wvnd+wvsd+wvnb+wvsb+wva"
ElseIf sInputState = "Wisconsin" Then
     sInputCourt = "wied+wiwd+wieb+wiwb+wis+wisctapp+wisag"
ElseIf sInputState = "Wyoming" Then
     sInputCourt = "wyd+wyb+wyo"
ElseIf sInputState = "special" Then
  sInputCourt = "ag+afcca+asbca+armfor+acca+uscfc+tax+mc+mspb+" & _
    "nmcca+cavc+bva+fiscr+fisc+cit+usjc+jpml+sttex+stp+cc+com+" & _
    "ccpa+cusc+eca+tecoa+reglrailreorgct+kingsbench"
ElseIf sInputState = "federal" Then
  sInputCourt = "scotus+ca1+ca2+ca3+ca4+ca5+ca6+ca7+" & _
     "ca8+ca9+ca10+ca11+cadc+cafc"
End If

Are you as excited as I am yet? I’m so excited.

ANYWAYS, the next part is an if-else statement that handles whether or not you entered anything into the second search field, sInput2. It essentially says, “If the variable sInput2 is empty, then do X; else do Y.”

Within each section of ‘if’ and ‘else’, the next portion assigns a certain URL string to the variable sURL. Each option of ‘if’ or ‘else’ accounts for whether you entered something in sInput2. Notice how the sURL string in the ‘else’ portion is constructed WITH the variable sInput2 but in the ‘if’ portion it is NOT.

If sInput2 = "" Then
  'only input1
  sURL = "https://www.courtlistener.com/api/rest/v3/search/" _
     & "?q=" & sInput1 & "&court=" & sInputCourt & _
  "&order_by=score+desc&stat_Precedential=on" & "&fields=caseName" '
Else
  'with input2
  'example url in the following comment: 'https://www.courtlistener.com/
  '?type=o&q=westview&type=o&order_by=score+desc&stat_Precedential=on
  '&court=waed+wawd+waeb+wawb+wash+washctapp+washag
     
  'variable for URL construction
  sURL = "https://www.courtlistener.com/api/rest/v3/search/" & _
  "?q=" & sInput1 & _
  "&q=" & sInput2 &  _
  "&court=" & sInputCourt & _
  "&order_by=score+desc&stat_Precedential=on" & "&fields=caseName"
End If

CourtListener has other URLs you may use to construct different URL strings if you need other information from CourtListener. For now, I recommend you just follow along with my example until we get done and you understand how to modify all this stuff for your needs.

Now that we have our website string sorted out that we want to call on for data, we’re going to call on it and save its response in a variable. We will need a token here. Go to this link at CourtListener and get one. Then come back here.

'Go get a token and then come back.
'sign up at https://www.courtlistener.com/api/rest-info/

Now, here’s a method of getting your token into your software. Again, my earlier warnings about security and regular disclaimer at the end of each article apply. Copy your token (highlight it and hit Ctrl+C) and put it in a text file on your LOCAL hard drive. Save the text file and close it. Restrict its access as necessary or secure it as necessary. Note the path to the text file or the directory you saved it in.

Now, we are going to edit the code you pasted. Locate the portion shown below in your pasted code. On the first line of this portion, you specify the string of the path to your text file that you just made with the variable sFile1. You should edit the part between the quotes to reflect where your text file is and its name. The third line of code reads the file contents. The fourth line saves the file contents into the variable sToken. The final line closes it.

sFile1 = "C:\other\1.txt"
'get token into variable
Open sFile1 For Input As #1
Line Input #1, sToken
Close #1

Next, we create the object which will call on the URL and establishes that, with the following lines until ‘End With’, do all the actions listed with the object we just made.

'begin use of object:
With CreateObject("WinHttp.WinHttpRequest.5.1")
        'do things here
End With 'end use of object

The third line is the actual call on the URL string. There are several other options you can put here in place of “GET”, which you will see when you go try this on other JSON APIs, such as “HEAD”, “POST”, or “OPTIONS”. Use whatever the API tells you to use in the way it tells you to use it. In this case, we’re using “GET”. You do not edit this part now, but when you modify this for your own use, you might.

.Open "GET", sURL, False 'Alternatives to GET:  HEAD or OPTIONS

The next three lines are what are called headers. They tell the URL how to handle the request. Each API requires different values for these headers and, just like you did with GET, you should follow the API. The API will tell you how to handle this part. In this case, notice there are three headers. One of them is our token.

        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .setRequestHeader "Authorization", "Bearer " & sToken

Here’s the send request.

.send

Next, we store the response from the URL we called into a variable, clear our token variable, and abort the connection.

        apiWaxLRS = .responseText
        sToken = ""
        .abort

Next, we’re going to print the response text in the debug/immediate window so you can see what you got back from C ourtListener. Hit Ctrl + G to pull up the debug/immediate window if it’s not open already.

        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------------"
End With

Next, we’re going to set our counter integers to 1 so the loops start at the beginning and run to their proper end.

x = 1
y = 1

You may run this code. As stated previously, it will ask you for input three times; two words or phrases to search on CourtListener and the name of a state or the word “federal” or “state”. I have used “Westview”, “”, “Washington” as the search inputs for this post if you would like to match it to what I’m doing (without the quotation marks).

After running this code, it will stop or break at the red break point you made and you will see a response from CourtListener:

Picture of response text received from CourtListener.

Now, we’re going to ‘parse’ that mess we got back from the CourtListener URL, because it’s actually not a mess at all. It’s a text string with a ton of information organized very tightly in a structured way. We can break down that string into usable chunks. This is where VBA Dictionary & JSON Converter comes in. These are already in the sample database and/or you downloaded and added them earlier. VBA Dictionary & JSON Converter are additional modules that will break up the JSON string at appropriate points and allow us to stash pieces of that string or pieces of information in a variable. Each piece of information is called a ‘field’. Later in this post, we will discuss the fields CourtListener has in their JSON strings so that you can get your desired pieces of information. You’ve already installed these two modules, so we can just move right along.

For now, note the important part of the string comes back in a field called ‘results’ and then it breaks out into each search result, with each search result having the same fields, but different information in each of the fields. The following picture is to make the big unformatted string easier to read and explain what’s going on here, for illustrative purposes only. You will not find it or be able to see it in the database or your code.

In the following code which parses the JSON response or extracts the needed information from the string and assigns it to variables, the first line parses the actual response.
In the second line, I assign the ‘results’ field to a variable.
This is followed by a couple of nested loops which will loop properly through the ‘citations’ field.
Each search result may have more than one citation, so you must loop through the ‘citations’ field to ensure you can access all citations for that search result; for each result in sID, do the following.
Then it commands the function to do things based on the contents of the field ‘citations’.

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set sID = Parsed("results")
For Each rep In sID
        If Not IsNull(rep("citation")) Then
            Set oCitations = rep("citation")
            For Each oEntry In oCitations
               'if current citation string is null/empty
               If oEntry = "null" Or oEntry = "" Or oEntry = Null  _
               Or oEntry = "Null" Then 
                   sCitation = "null"
                   Debug.Print sCitation
               'if current citation string is not empty
               ElseIf oEntry &lt;&gt; "" Then 
                   sCitation = oEntry
                   Debug.Print sCitation
               Else 'anything else
                   Set sCitation = oEntry
                   For Each resp In sCitation
                       sCitation = oEntry
                       y = x + 1
                       Debug.Print sCitation
                   Next
                   y = 1 
               End If
            Next 'go to the next citation
        Else 'if citation field is null do this 
            sCitation = "Null"
        End If

If you are confused, that’s okay. Let it run and visually match up what ‘prints’ in the debug window with the fields. A visual is sometimes easier. That link is to a nicely formatted but identical JSON string, a sample response from CourtListener, and you can see other ‘field’ names you may use in place of ‘citation’ or ‘court’ or ‘absolute_url’ for information you may want. You can see all 19 sets of results formatted.

On the left is the unformatted response text and on the right is the formatted version of the same string from CourtListener. See identical field names in both?

There are 19 search results in the Westview sample, so this code would iterate through the largest loop 19 times. Note some of these 19 have 1 citation and some have multiple, hence the loop I constructed and went through earlier. As I said, expand each field there with the little arrows, put the code side by side with the sample results, and you’ll see how they interact together or how you can apply doing this to other fields. You do not need to edit anything. I just want you to understand where the information is in this string and how it is structured.

So moving on to other pieces of information, we don’t just want citation information, right? The rest of the fields I’m using as examples are fairly straightforward and require no loops. You can see other fields available in the provided sample as well as in the above pictures. CourtListener also has a full rundown of the different fields available. The code you have provides some fields as examples.

Afterwards, this code then adds 1 to x because we are going to be parsing the next search result with the next iteration of the loop, i.e. x = 2, the second search result.

    Debug.Print sCitation
    sCaseName = rep("caseName")
    Debug.Print sCaseName
    sAbsoluteURL = rep("absolute_url")
    Debug.Print sAbsoluteURL
    sCourt = rep("court")
    Debug.Print sCourt
    x = x + 1
    Debug.Print "--------------------------------------------"

Lastly, we’re going to reset the y counter integer and go to the ‘next’ item in the JSON string or the ‘next’ search result.

Now, if you hit Run –> Run, or F5, or the green play button in the toolbar up top, it will finish running through the first loop, index number 1, and go to the red break point you made in the second loop, index number 2, so we do not reset the x. Remember, we are not resetting x because we have just parsed the first case or search result, the index number of which is represented by the count integer x. However, we DO want to reset the y counter integer because that represents the current index number for each citation of the current case/search result and we want to make sure we get all the citations for each search result. So we reset y after each iteration of x and go to the next case in the search results.

y = 1
Next

So if you run this code yet again, it will finish parsing the second loop and then run to the red break point again for a third time, and it would repeat the process 16 more times after that (for a total of 19 search results).

Resources

Github
JSON Placeholder
JSON Parser
Database File
CourtListener API Model
Formatted JSON CourtListener string
VBA Dictionary & JSON Converter

Epilogue

So, now you can get JSON information from the internet into your debug/immediate window. Soon, we’ll learn how to put it in other places. For now, if you read all the building blocks posts so far, you could, say, use a DAO recordset to put the variables’ values into a database table. We’ll learn later how you could use the ‘absolute_url’ field to construct a URL that also links words in a Word document automatically. I’ll later teach you how to put that information into a Word document such that it will mark it as a citation and put it in a table of authorities automatically.

If you made it this far, congratulations and thanks for reading. I hope your mind is spinning with all the things you could do with this newfound information! For the next few posts, we’ll be covering how to use JSON with other popular APIs geared towards the legal profession. I have received no remuneration of any kind for this post or any other on this website.

Final Code

Double-click anywhere in the following code area and press Ctrl + C to copy the entire code.

Click here after you’ve pasted your final code to pick up where you left off with “Explanation of Code“.

Function fCourtListener()
'============================================================================
' Name        : fCourtListener
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fCourtListener()
' Description : gets URLs & search listings from CourtListener (case law)
'============================================================================
Dim sAbsoluteURL As String, sURL As String, apiWaxLRS As String
Dim sCaseName As String, sInputState As String, sToken As String
Dim sInput1 As String, sInput2 As String, sInputCourt As String
Dim sCourt As String
Dim rep, resp, sCitation, oEntry
Dim sID As Object, oCitations As Object
Dim oRequest As Object, vDetails As Object
Dim x As Integer, y As Integer
Dim Parsed As Dictionary

'Go get a token and then come back.
'sign up at https://www.courtlistener.com/api/

sFile1 = "C:\other\10.txt"
'get token into variable
Open sFile1 For Input As #1
Line Input #1, sToken
Close #1

'search term 1
sInput1 = _
    InputBox("Enter a word or phrase to search for on CourtListener.")
'search term 2
sInput2 = InputBox _
    ("Enter another word or phrase to search for on CourtListener.")
 
'enter name of state here or 'federal' or 'special'
sInputState = InputBox("Enter state name, 'federal', or 'special' here.")

If sInputState = "Alabama" Then
    sInputCourt = "almd+alnd+alsd+almb+alnb+alsb+ala+alactapp+alacrimapp+alacivapp"
ElseIf sInputState = "Alaska" Then
     sInputCourt = "akd+akb+alaska+alaskactapp"
ElseIf sInputState = "Arizona" Then
     sInputCourt = "azd+arb+ariz+arizctapp+ariztaxct"
ElseIf sInputState = "Arkansas" Then
     sInputCourt = "ared+arwd+areb+arwb+ark+arkctapp+arkworkcompcom+arkag"
ElseIf sInputState = "California" Then
     sInputCourt = "cacd+caed+cand+casd+californiad+caca+cacb+caeb+" & _
        "canb+casb+cal+calctapp+calappdeptsuper+calag"
ElseIf sInputState = "Colorado" Then
     sInputCourt = "cod+cob+colo+coloctapp+coloworkcompcom+coloag"
ElseIf sInputState = "Connecticut" Then
     sInputCourt = "ctd+ctb+conn+connappct+connsuperct+connworkcompcom"
ElseIf sInputState = "Delaware" Then
     sInputCourt = "ded+circtdel+deb+del+delch+delsuperct+delctcompl" & _
        "+delfamct+deljudct"
ElseIf sInputState = "Florida" Then
     sInputCourt = "flmd+flnd+flsd+flmb+flnb+flsb+fla+fladistctapp+flaag"
ElseIf sInputState = "Georgia" Then
     sInputCourt = "gamd+gand+gasd+gamb+ganb+gasb+ga+gactapp"
ElseIf sInputState = "Hawaii" Then
     sInputCourt = "hid+hib+haw+hawapp"
ElseIf sInputState = "Idaho" Then
     sInputCourt = "idd+idb+idaho+idahoctapp"
ElseIf sInputState = "Illinois" Then
     sInputCourt = "ilcd+ilnd+ilsd+illinoised+illinoisd+ilcb+ilnb" & _
        "+ilsb+ill+illappct"
ElseIf sInputState = "Indiana" Then
     sInputCourt = "innd+insd+indianad+innb+insb+ind+indctapp+indtc"
ElseIf sInputState = "Iowa" Then
     sInputCourt = "iand+iasd+ianb+iasb+iowa+iowactapp"
ElseIf sInputState = "Kansas" Then
     sInputCourt = "ksd+ksb+kan+kanctapp+kanag"
ElseIf sInputState = "Kentucky" Then
     sInputCourt = "kyed+kywd+kyeb+kywb+ky+kyctapp+kyctapphigh"
ElseIf sInputState = "Louisiana" Then
     sInputCourt = "laed+lamd+lawd+laeb+lamb+lawb+la+lactapp+laag"
ElseIf sInputState = "Maine" Then
     sInputCourt = "med+bapme+meb+me"
ElseIf sInputState = "Maryland" Then
     sInputCourt = "mdd+mdb+md+mdctspecapp+mdag"
ElseIf sInputState = "Massachusetts" Then
     sInputCourt = "mad+bapma+mab+mass+massappct+masssuperct" & _
        "+massdistct+maworkcompcom"
ElseIf sInputState = "Michigan" Then
     sInputCourt = "mied+miwd+mieb+miwb+mich+michctapp"
ElseIf sInputState = "Minnesota" Then
     sInputCourt = "mnd+mnb+minn+minnctapp+minnag"
ElseIf sInputState = "Mississippi" Then
     sInputCourt = "msnd+mssd+msnb+mssb+miss+missctapp"
ElseIf sInputState = "Missouri" Then
     sInputCourt = "moed+mowd+moeb+mowb+mo+moctapp+moag"
ElseIf sInputState = "Montana" Then
     sInputCourt = "mtd+mtb+mont+monttc+montag"
ElseIf sInputState = "Nebraska" Then
     sInputCourt = "ned+nebraskab+neb+nebctapp+nebag"
ElseIf sInputState = "Nevada" Then
     sInputCourt = "nvd+nvb+nev"
ElseIf sInputState = "New Hampshire" Then
     sInputCourt = "nhd+nhb+nh"
ElseIf sInputState = "New Jersey" Then
     sInputCourt = "njd+njb+nj+njsuperctappdiv+njtaxct+njch"
ElseIf sInputState = "New Mexico" Then
     sInputCourt = "nmd+nmb+nm+nmctapp"
ElseIf sInputState = "New York" Then
     sInputCourt = "nyed+nynd+nysd+nywd+nyeb+nynb+nysb+nywb+ny" & _
        "+nyappdiv+nyappterm+nysupct+nyfamct+nysurct+nycivct+nycrimct+nyag"
ElseIf sInputState = "North Carolina" Then
     sInputCourt = "nced+ncmd+ncwd+circtnc+nceb+ncmb+ncwb+nc" & _
        "+ncctapp+ncsuperct+ncworkcompcom"
ElseIf sInputState = "North Dakota" Then
     sInputCourt = "ndd+ndb+nd+ndctapp"
ElseIf sInputState = "Ohio" Then
     sInputCourt = "ohnd+ohsd+ohiod+ohnb+ohsb+ohio+ohioctapp+ohioctcl"
ElseIf sInputState = "Oklahoma" Then
     sInputCourt = "oked+oknd+okwd+okeb+oknb+okwb+okla" & _
        "+oklacivapp+oklacrimapp+oklajeap+oklacoj+oklaag"
ElseIf sInputState = "Oregon" Then
     sInputCourt = "ord+orb+or+orctapp+ortc"
ElseIf sInputState = "Pennsylvania" Then
     sInputCourt = "paed+pamd+pawd+pennsylvaniad+paeb+pamb+" & _
        "pawb+pa+pasuperct+pacommwct+cjdpa+stp"
ElseIf sInputState = "Rhode Island" Then
     sInputCourt = "rid+rib+ri+risuperct"
ElseIf sInputState = "South Carolina" Then
     sInputCourt = "scd+southcarolinaed+southcarolinawd+scb+sc+scctapp"
ElseIf sInputState = "South Dakota" Then
     sInputCourt = "sdd+sdb+sd"
ElseIf sInputState = "Tennessee" Then
     sInputCourt = "tned+tnmd+tnwd+tennessed+circttenn+tneb+tnmb" & _
        "+tnwb+tennesseeb+tenn+tennctapp+tenncrimapp+tennsuperct"
ElseIf sInputState = "Texas" Then
     sInputCourt = "txed+txnd+txsd+txwd+txeb+txnb+txsb+txwb+tex+" & _
        "texapp+texcrimapp+texreview+texjpml+texag+sttex"
ElseIf sInputState = "Utah" Then
     sInputCourt = "utd+utb+utah+utahctapp"
ElseIf sInputState = "Vermont" Then
     sInputCourt = "vtd+vtb+vt+vtsuperct"
ElseIf sInputState = "Virginia" Then
     sInputCourt = "vaed+vawd+vaeb+vawb+va+vactapp"
ElseIf sInputState = "Washington" Then
     sInputCourt = "waed+wawd+waeb+wawb+wash+washctapp+washag"
ElseIf sInputState = "West Virginia" Then
     sInputCourt = "wvnd+wvsd+wvnb+wvsb+wva"
ElseIf sInputState = "Wisconsin" Then
     sInputCourt = "wied+wiwd+wieb+wiwb+wis+wisctapp+wisag"
ElseIf sInputState = "Wyoming" Then
     sInputCourt = "wyd+wyb+wyo"
ElseIf sInputState = "special" Then
     sInputCourt = "ag+afcca+asbca+armfor+acca+uscfc+tax+mc+mspb+" & _
        "nmcca+cavc+bva+fiscr+fisc+cit+usjc+jpml+sttex+stp+cc+com+" & _
        "ccpa+cusc+eca+tecoa+reglrailreorgct+kingsbench"
ElseIf sInputState = "federal" Then
     sInputCourt = "scotus+ca1+ca2+ca3+ca4+ca5+ca6+ca7+" & _
        "ca8+ca9+ca10+ca11+cadc+cafc"
End If
If sInput2 = "" Then
    'only input1
    sURL = "https://www.courtlistener.com/api/rest/v3/search/"  _
       & "?q=" & sInput1 & "&court=" & sInputCourt & _
    "&order_by=score+desc&stat_Precedential=on" & "&fields=caseName" '
Else
    'with input2
    'example url in the following comment: 'https://www.courtlistener.com/
    '?type=o&q=westview&type=o&order_by=score+desc&stat_Precedential=on
    '&court=waed+wawd+waeb+wawb+wash+washctapp+washag
    
    'variable for URL construction
    sURL = "https://www.courtlistener.com/api/rest/v3/search/" & "?q=" & sInput1 & _
    "&q=" & sInput2 & "&court=" & sInputCourt & _
    "&order_by=score+desc&stat_Precedential=on" & "&fields=caseName" '
End If

    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", sURL, False 'options
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .setRequestHeader "Authorization", "Bearer " & sToken
        .send
        apiWaxLRS = .responseText
        sToken = ""
        .abort
        Debug.Print apiWaxLRS
        Debug.Print "--------------------------------------------"
    End With

x = 1
y = 1

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set sID = Parsed("results")

For Each rep In sID

        If Not IsNull(rep("citation")) Then
            Set oCitations = rep("citation")
            For Each oEntry In oCitations

                If oEntry = "null" Or oEntry = ""  _
                Or oEntry = Null Or oEntry = "Null" Then
                    sCitation = "null"
                    Debug.Print sCitation
                ElseIf oEntry &lt;&gt; "" Then
                    sCitation = oEntry
                    Debug.Print sCitation
                Else
                    Set sCitation = oEntry

                    For Each resp In sCitation
                        sCitation = oEntry
                        y = x + 1
                        Debug.Print sCitation
                    Next

                    y = 1

                End If

            Next
        Else
            sCitation = "Null"
        End If

    Debug.Print sCitation
    sCaseName = rep("caseName")
    Debug.Print sCaseName
    sAbsoluteURL = rep("absolute_url")
    Debug.Print sAbsoluteURL
    sCourt = rep("court")
    Debug.Print sCourt
    x = x + 1
    Debug.Print "--------------------------------------------"
    y = 1
Next

End Function

Click here after you’ve copied+pasted your final code to pick up where you left off with “Explanation of Code“.

Disclaimer

**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.