Part 2c: Using VBA with JSON: OneNote

Posted byErica L. Ingram

Today, we’ll cover OneNote in our series on using VBA and JSON. You can download the database sample I’ve provided and skip to “Explanation of Code” or you can create the modules manually.

Today’s Lesson on GitHub
Link to get token
OneNote API documentation
database sample
Final Code

Or skip down to explanations of how to do various things in OneNote:

Some of this content will be repeated from the previous post on how to interact with JSON. If you know how JSON works with VBA and you want to skip directly to the OneNote part, click here to go to “Explanation of Code“.

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 ‘Wunderlist‘. 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 OneNote 15.0 Object Library *
  • Microsoft Access 16.0 Object Library
  • Microsoft XML, v6.0 *
  • Microsoft Office 16.0 Object Library *
  • Microsoft Scripting Runtime
  • Microsoft Word 16.0 Object Library
  • Microsoft Internet Controls
  • Microsoft Excel 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 ‘Tools’, then ‘References’.
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.

Explanation of Code

This exercise has an extra step or two, but it largely similar to the previous exercises we’ve gone through. The main extra step is an authentication step. You need a Microsoft account. Go here to create one if you don’t have one already.

Sign in with your Microsoft account to the Microsoft Dev Center.

Enter in a name, any name, for your app. Mine is named something like ‘accessdb’.

The Microsoft website will ask you or maybe has already if you want to view the app in Azure. Say yes and it will open a new window. For now, go back to the old window you were just at.

On the next page, in “Microsoft Graph Permissions”, click the box “Add” next to “Application Permissions”. Ensure the following four options are checked:

  • User.Export.All (Admin Only)
  • User.Invite.All (Admin Only)
  • User.Read.All (Admin Only)
  • User.ReadWrite.All (Admin Only)

Under “Application Secrets”, select “Generate New Password”, and copy+paste the key into its own text file with nothing else in the text file. Save the text file on your local hard drive. What you just saved is called your ‘secret’ in the Microsoft API.

Next, you should see on the same window where you got your ‘secret’ something towards the top called ‘Application ID’ under the ‘Properties’ heading. Copy and paste that into its own text file as well on your local hard drive.

Now, go to the Azure window you opened earlier from the prompt asking you to view the app on the Azure platform. You should see your app there with three pieces of information; the ‘object ID’, ‘application (client) ID’, and the ‘directory (tenant) ID’. You already have saved one of these. Save the remaining two in their own text files to your local drive (two numbers, two text files).

In the same Azure window, you should see the phrase “Managed application in local directory” with your app name. Click on the app name. Click on your ‘total users’, which should be ‘1’. Click on the user there, which should be you, and you will see a heading labeled ‘Identity’. See the ‘object ID’ there? Copy and paste that into its own text file on your local hard drive as well.

The last thing you will need to do is select a phrase which is called a ‘state’. It is simply a text value which can be anything you want and is used to “encode info about the user’s state in the app before the authentication request occurred, such as the page or view they were on”. If you want more info, go here.

For this exercise, I have selected “aqc” as my ‘state’, although that is not my real one. You may save this in a text file, too, if you wish, similar to the other text strings you’ve saved.

Next, you must request permissions by going to a URL constructed from the information we just got: 

https://login.microsoftonline.com/{tenant}/adminconsent?client_id={app_id}&state={state}&redirect_uri=http://localhost/myapp/permissions

A dialog to accept/approve will come up and you should accept/approve.

Get A List of OneNote Notebooks

Now we’re ready to go over the actual VBA code for this exercise. First exercise we’re going to go through is how to get a list of your notebooks.

Here’s the info box at the beginning of the function, variable declarations:

Function fONGetNotebooks()

'============================================================================
' Name        : fONGetNotebooks
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetNotebooks()
' Description : get ON notebooks
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sJSONResponse As String, sCBID As String
Dim sResponseSplit() As String, sResponseSplit1() As String, sJSONResponse1 As String
Dim rep
Dim sCBLastModifiedID As String, sCBLClientURL As String

Next, there’s a note here about accepting permissions, but you can disregard that because we’ve already done it. It’s just in there for a reminder that it must be done.

'before auth if you make privilege changes you must visit this website and accept permissions.
'https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"'

Now we’ll pull your various text strings you acquired into our function:

sFile1 = "C:\other\6.txt" 'object id
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret
sState = "aqc"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

We need to construct the URL we’ll be calling to get a token. Use your ‘directory tenant ID’ here.

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

And we get our token data. The second line calls on the URL. The third and fourth lines set a couple of request headers you need. The fifth line submits the request with the required text strings you saved. The sixth line tells Microsoft you’re waiting for a response. The seventh line stores the response in a variagble and the eighth line aborts the connection. The last several lines clears out the sensitive data since we don’t need it anymore.

You will need to use your secret and your app ID here with the URL that we just constructed.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
.abort
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
End With

Now we’ll parse the response to actually store the token in a variable:

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

Next we need to construct the URL which we’ll call on to get a list of our notebooks. Use your ‘object ID’ here.

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks"

Next, we’ll call on it to get our list of notebooks. The second line calls on the URL. The third and fourth lines set a couple of request headers you need. The fifth line submits the request with the required text strings you saved. The sixth line stores the response in a variable and the seventh line prints it to the debug window. The ninth line aborts the connection.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

So now we got a response and we need to parse it:

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

Next, we’re going to loop through the various available fields, assign them to variables, and print them in the debug window.

For Each rep In vDetails
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sDisplayName = rep("displayName")
sLastModifiedDateTime = rep("lastModifiedDateTime")
sIsDefault = rep("isDefault")
sUserRole = rep("userRole")
sIsShared = rep("isShared")
sSectionsUrl = rep("sectionsUrl")
sSectionGroupsUrl = rep("sectionGroupsUrl")
Set rep1 = rep("createdBy")
Set rep2 = rep1("user")
sCBID = rep2("id")
sCBDisplayName = rep2("displayName")
Set rep3 = rep("lastModifiedBy")
Set rep4 = rep1("user")
sCBLastModifiedID = rep4("id")
sCBLastModifiedName = rep4("displayName")
Set rep5 = rep("links")
Set rep6 = rep5("oneNoteClientUrl")
sCBLClientURL = rep6("href")
Set rep7 = rep("links")
Set rep8 = rep5("oneNoteWebUrl")
sCBLWebURL = rep8("href")
<pre><code>    Debug.Print "sONID = " & sONID
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sDisplayName = " & sDisplayName
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sIsDefault = " & sIsDefault
    Debug.Print "sUserRole = " & sUserRole
    Debug.Print "sIsShared = " & sIsShared
    Debug.Print "sSectionsUrl = " & sSectionsUrl
    Debug.Print "sSectionGroupsUrl = " & sSectionGroupsUrl
    Debug.Print "sCBID = " & sCBID
    Debug.Print "sCBDisplayName = " & sCBDisplayName
    Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
    Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "--------------------------------------------"
Next
End Function

Get A List of Sections from a OneNote Notebook

Now that we’ve got our notebooks, we need to get sections from a notebook. This one will look a lot like the previous one with some pretty minor modifications. So in the name of brevity, we’re going to section off the part that’s the same and then explain beginning at the part that’s different. Here’s the part of the code that’s the same:

Function fONGetSections()
'============================================================================
' Name        : fONGetSections
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fOneNote()
' Description : get ON sections
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim rep, vDetails, rep1, rep2, rep3, rep4, rep5, rep6, rep7, rep8
Dim sJSONResponse As String, sCBID As String
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sPNID As String, sPNDisplayName As String
Dim sPNSelf As String, sPSGODContext As String, sPSG As String

'before auth if you make privilege changes you must visit this website and accept permissions.
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"
'
sFile1 = "C:\other\6.txt" 'object id / user ID 'display name "Erica Ingram"
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "RESPONSETEXT-------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

Now to the different part. We’ve got our token and we want to construct the URL we’re going to call on. You’ll need the ID of the notebook you want to use, so if you don’t know it, run the previous function to get the ID of the notebook you want to use. 

sNotebookID = ""
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sections" '

Much like the last function, the second line calls on the URL. The third and fourth lines set request headers, including our token. The fifth line sends the request. The sixth line stores the response in a variable. Lines 7 through 11 clear out the sensitive information from their corresponding variables. Lines 12 and 13 print it to the debug window. Line 14 aborts the connection.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Now that we have a response, we can parse it:

    Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

Lastly, we can store this information in variables and loop through each result: 

For Each rep In vDetails
sONID = rep("id")
sCreatedDateTime = rep("createdDateTime")
sSelfLink = rep("self")
sDisplayName = rep("displayName")
sLastModifiedDateTime = rep("lastModifiedDateTime")
sIsDefault = rep("isDefault")
sUserRole = rep("pagesUrl")
sPSGODContext = rep("parentSectionGroup@odata.context")
sIsShared = rep("parentNotebook@odata.context")
Set rep1 = rep("createdBy")
    Set rep2 = rep1("user")
    sCBID = rep2("id")
    sCBDisplayName = rep2("displayName")
    Set rep3 = rep("lastModifiedBy")
    Set rep4 = rep1("user")
    sCBLastModifiedID = rep4("id")
    sCBLastModifiedName = rep4("displayName")
    Set rep5 = rep("links")
    Set rep6 = rep5("oneNoteClientUrl")
    sCBLClientURL = rep6("href")
    Set rep7 = rep("links")
    Set rep8 = rep5("oneNoteWebUrl")
    sCBLWebURL = rep8("href")
    Set rep9 = rep("parentNotebook")
    sPNID = rep9("id")
    sPNDisplayName = rep9("displayName")
    sPNSelf = rep9("self")

    Debug.Print "sONID = " & sONID
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sDisplayName = " & sDisplayName
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sIsDefault = " & sIsDefault
    Debug.Print "sUserRole = " & sUserRole
    Debug.Print "sCBID = " & sCBID
    Debug.Print "sCBDisplayName = " & sCBDisplayName
    Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
    Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "sPNID = " & sPNID
    Debug.Print "sPNDisplayName = " & sPNDisplayName
    Debug.Print "sPNSelf = " & sPNSelf
    Debug.Print "sPSGODContext = " & sPSGODContext
    Debug.Print "sIsShared = " & sIsShared
    Debug.Print "--------------------------------------------"
Next
End Function

Get A List of Section Groups from a OneNote Notebook

Same as before; in the interests of time here, I’m going to totally skip the identical part.

Now to the different part. We’ve got our token and we want to construct the URL we’re going to call on. You’ll need the ID of the notebook you want to use, so if you don’t know it, run the previous function to get the ID of the notebook you want to use. 

sNotebookID = ""
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sectionGroups" '

Much like the last function, the second line calls on the URL. The third and fourth lines set request headers, including our token. The fifth line sends the request. The sixth line stores the response in a variable. Lines 7 through 11 clear out the sensitive information from their corresponding variables. Lines 12 and 13 print it to the debug window. Line 14 aborts the connection.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Now that we have a response, we can parse it:

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

And like last time, we need a loop with a lot of variables to loop through the result and parse out the info from the fields we need:

For Each rep In vDetails
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sDisplayName = rep("displayName")
sLastModifiedDateTime = rep("lastModifiedDateTime")
sIsDefault = rep("isDefault")
sUserRole = rep("userRole")
sIsShared = rep("isShared")
sSectionsUrl = rep("sectionsUrl")
sSectionGroupsUrl = rep("sectionGroupsUrl")
sPSGODContext = rep("parentSectionGroup@odata.context")
sPSG = Parsed("parentSectionGroup")

Set rep1 = rep("createdBy")
Set rep2 = rep1("user")
sCBID = rep2("id")
sCBLClientURL = rep1("links")
sCBDisplayName = rep2("displayName")
Set rep3 = rep("lastModifiedBy")
Set rep4 = rep1("user")
sCBLastModifiedID = rep4("id")
sCBLastModifiedName = rep4("displayName")
Set rep9 = rep("parentNotebook")
sPNID = rep9("id")
sPNDisplayName = rep9("displayName")
sPNSelf = rep9("self")


Debug.Print "sONID = " & sONID
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sDisplayName = " & sDisplayName
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sSectionsUrl = " & sSectionsUrl
Debug.Print "sSectionGroupsUrl = " & sSectionGroupsUrl
Debug.Print "sCBID = " & sCBID
Debug.Print "sCBDisplayName = " & sCBDisplayName
Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
Debug.Print "sPNID = " & sPNID
Debug.Print "sPNDisplayName = " & sPNDisplayName
Debug.Print "sPNSelf = " & sPNSelf
Debug.Print "sPSGODContext = " & sPSGODContext
Debug.Print "--------------------------------------------"
Next

End Function

Get A List of Pages from a OneNote Notebook/Section

Now we’ll get pages. In the interests of time here, I’m going to totally skip the identical part.

Now to the different part. We’ve got our token and we want to construct the URL we’re going to call on. You’ll need the ID of the notebook you want to use, so if you don’t know it, run the previous function to get the ID of the notebook you want to use. 

sNotebookID = ""
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages"

Much like the last function, the second line calls on the URL. The third and fourth lines set request headers, including our token. The fifth line sends the request. The sixth line stores the response in a variable. Lines 7 through 11 clear out the sensitive information from their corresponding variables. Lines 12 and 13 print it to the debug window. Line 14 aborts the connection.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Now that we have a response, we can parse it:

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

And lastly, we’ll store this info in variables and print it to the debug window.

For Each rep In vDetails
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sTitle = rep("title")
scontentUrl = rep("contentUrl")
sIsShared = rep("parentNotebook@odata.context")

sLastModifiedDateTime = rep("lastModifiedDateTime")

Set rep1 = rep("links")
Set rep2 = rep1("oneNoteClientUrl")
sCBLClientURL = rep2("href")
Set rep3 = rep1("oneNoteWebUrl")
sCBLWebURL = rep3("href")

Set rep9 = rep("parentSection")
sPNID = rep9("id")
sPNDisplayName = rep9("displayName")
sPNSelf = rep9("self")

Debug.Print "sONID = " & sONID
Debug.Print "sTitle = " & sTitle
Debug.Print "scontentUrl = " & scontentUrl
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sCBLClientURL = " & sCBLClientURL
Debug.Print "sCBLWebURL = " & sCBLWebURL
Debug.Print "sPNID = " & sPNID
Debug.Print "sPNDisplayName = " & sPNDisplayName
Debug.Print "sPNSelf = " & sPNSelf
Debug.Print "--------------------------------------------"
Next

End Function

Searching OneNote Page Titles for Phrase & Returning Page ID

First, we need an extremely short function to call the function and pass the search phrase or word we want to search for to the function:

Function test()
Call fONGetPageID("a") ''a' is your search phrase.

End Function

The phrase we’re searching for is just the letter a, and we’ll be searching for it in page titles. In the interests of time here, I’m going to totally skip the portion of the code identical to the other functions listed here and skip to the relevant section.

Let’s start by parsing out the token from the response. 

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

Then we need to construct the URL:

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages"

Following that, we set up and send the request to get all pages from OneNote. Much like the last function, the second line calls on the URL. The third and fourth lines set request headers, including our token. The fifth line sends the request. The sixth line stores the response in a variable. Lines 7 through 11 clear out the sensitive information from their corresponding variables. Lines 12 and 13 print it to the debug window. Line 14 aborts the connection.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Next, we’ll begin to parse the response: 

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

Now, when we parse our response, we’re going to start by getting the page title of the ‘first’ page in the search results: 

For Each rep In vDetails
sTitle = rep("title")

Next, we’ll put in an if statement that says, “Starting at character 1, if the title contains string, do the following.” Then it completes everything indented underneath the if statement. At the end, it loops to the next search result. If there is no “a” in the title, it will move on to the next one without telling you. You will only see results in the debug window that have a match for your search result.

If InStr(1, sTitle, sSearchInTitle) Then
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sTitle = rep("title")
scontentUrl = rep("contentUrl")
sIsShared = rep("parentNotebook@odata.context")
sLastModifiedDateTime = rep("lastModifiedDateTime")
Set rep1 = rep("links")
Set rep2 = rep1("oneNoteClientUrl")
sCBLClientURL = rep2("href")
Set rep3 = rep1("oneNoteWebUrl")
sCBLWebURL = rep3("href")
Set rep9 = rep("parentSection")
sPNID = rep9("id")
sPNDisplayName = rep9("displayName")
sPNSelf = rep9("self")

Debug.Print "sONID = " & sONID
    Debug.Print "sTitle = " & sTitle
    Debug.Print "scontentUrl = " & scontentUrl
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "sPNID = " & sPNID
    Debug.Print "sPNDisplayName = " & sPNDisplayName
    Debug.Print "sPNSelf = " & sPNSelf
    Debug.Print "--------------------------------------------"
    'return id of title
    Debug.Print "Page with Title " & Chr(34) & sTitle & Chr(34) & " has page ID number " & sONID & "."

    Debug.Print "--------------------------------------------"

Else

End If
Next

End Function

Creating OneNote Pages in OneNote Notebooks

This is the last function I’ll be covering today. Next post, we’ll finish going over interacting with OneNote, including how to delete pages, update them, copy to sections, and create sections and section groups. If you’re feeling adventurous, you are able to do this on your own if you consult the Microsoft Graph Rest API for the various fill-ins you need (like the URL you call on, the data you send to Microsoft with your request, the headers).

So like all the others, the first half of the code is the same, followed by parsing the response and acquiring the token out of the response. You need a section ID to create a page, so you may need to go back and run previously provided functions before you can move forward. 

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")
sSectionID = ""

Next is the body of the OneNote page you want to create. You’re going to want to make an text string of HTML code; one has been provided here as a test. Alternatively, you could use the file-read method we’ve been using to get tokens from the local text files and have VBA read the file contents of an HTML file into a text string instead of the token file. That’s not in the final code down below; you’ll have to edit it to do so, but you have everything you need provided for you here if you want to try that. 

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Moving on, here’s the HTML string. i suggest waiting to really look at this closely until you’ve had a chance to get the code working and then come back and edit the string to see how it works with OneNote.

sHTMLPage = "<!DOCTYPE html><html><head>" & _
"<title>A page with <i>rendered</i> <b>images</b></title>" & _
"<meta name=" & Chr(34) & "created" & Chr(34) & " content=" & Chr(34) & "2015-07-22T09:00:00-08:00" & Chr(34) & " />" & _
"</head><body>

Here's an image from an online source:

" & "<img src=" & Chr(34) & _
    "https://www.aquoco.co/SQUARELOGO.jpg" & Chr(34) & " alt=" & Chr(34) & "an image on the page" & Chr(34) & _
    " width=" & Chr(34) & "500" & Chr(34) & " />" & "</body></html>"

Now, here we need to construct our URL that we’re going to call on to create the page, using the section ID you got.

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/sections/" & sSectionID & "/pages"

Much like the last function, the second line calls on the URL. The third and fourth lines set request headers, including our token. The sixth line sends the request. The seventh line stores the response in a variable. Lines 8 through 12 clear out the sensitive information from their corresponding variables. Lines 13 and 14 print it to the debug window. Line 15 aborts the connection. The last line parses the response we receive.

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Authorization", "Bearer " & sToken
.setRequestHeader "Content-Type", "text/html"
.setRequestHeader "Content-Disposition", "form-data", "name", "Presentation"
.send sHTMLPage
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)

Similar to the others, we’re going to separate out the info we need into different variables from the response and print them to the debug window:

sIsShared = Parsed("@odata.context")
sONID = Parsed("id")
sSelfLink = Parsed("self")
sCreatedDateTime = Parsed("createdDateTime")
sTitle = Parsed("title")
scontentUrl = Parsed("contentUrl")
sLastModifiedDateTime = Parsed("lastModifiedDateTime")
Set rep1 = Parsed("links")
Set rep2 = rep1("oneNoteClientUrl")
sCBLClientURL = rep2("href")
Set rep3 = rep1("oneNoteWebUrl")
sCBLWebURL = rep3("href")

Debug.Print "sIsShared = " & sIsShared
Debug.Print "sONID = " & sONID
Debug.Print "sTitle = " & sTitle
Debug.Print "scontentUrl = " & scontentUrl
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sCBLClientURL = " & sCBLClientURL
Debug.Print "sCBLWebURL = " & sCBLWebURL
Debug.Print "--------------------------------------------"

End Function

That’s it for now. Next week, we’ll finish up OneNote and learn how to delete pages, update them, copy to a section, and create sections and section groups.

Resources

Explanations

Final Code

Get List of OneNote Notebooks

Function fONGetNotebooks()

'============================================================================
' Name        : fONGetNotebooks
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetNotebooks()
' Description : get ON notebooks
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sJSONResponse As String, sCBID As String
Dim rep, vDetails, rep1, rep2, rep3, rep4, rep5, rep6, rep7, rep8
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sState As String

sFile1 = "C:\other\6.txt" 'object id
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
.abort
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
sLine1 = ""
sLine2 = ""
sLine3 = ""
sLine4 = ""
End With
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine6 & "/onenote/notebooks"
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", sURL, False
    .setRequestHeader "Host", "graph.microsoft.com"
    .setRequestHeader "Authorization", "Bearer " & sToken
    .send
    apiWaxLRS = .responseText
    Debug.Print apiWaxLRS
    Debug.Print "--------------------------------------------"
    .abort
End With
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")
For Each rep In vDetails
    sONID = rep("id")
    sSelfLink = rep("self")
    sCreatedDateTime = rep("createdDateTime")
    sDisplayName = rep("displayName")
    sLastModifiedDateTime = rep("lastModifiedDateTime")
    sIsDefault = rep("isDefault")
    sUserRole = rep("userRole")
    sIsShared = rep("isShared")
    sSectionsUrl = rep("sectionsUrl")
    sSectionGroupsUrl = rep("sectionGroupsUrl")
    Set rep1 = rep("createdBy")
    Set rep2 = rep1("user")
    sCBID = rep2("id")
    sCBDisplayName = rep2("displayName")
    Set rep3 = rep("lastModifiedBy")
    Set rep4 = rep1("user")
    sCBLastModifiedID = rep4("id")
    sCBLastModifiedName = rep4("displayName")
    Set rep5 = rep("links")
    Set rep6 = rep5("oneNoteClientUrl")
    sCBLClientURL = rep6("href")
    Set rep7 = rep("links")
    Set rep8 = rep5("oneNoteWebUrl")
    sCBLWebURL = rep8("href")

    Debug.Print "sONID = " & sONID
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sDisplayName = " & sDisplayName
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sIsDefault = " & sIsDefault
    Debug.Print "sUserRole = " & sUserRole
    Debug.Print "sIsShared = " & sIsShared
    Debug.Print "sSectionsUrl = " & sSectionsUrl
    Debug.Print "sSectionGroupsUrl = " & sSectionGroupsUrl
    Debug.Print "sCBID = " & sCBID
    Debug.Print "sCBDisplayName = " & sCBDisplayName
    Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
    Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "--------------------------------------------"
Next
End Function

Explanations

Get A List of Sections from a OneNote Notebook

Function fONGetSections()
'============================================================================
' Name        : fONGetSections
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetSections()
' Description : get ON sections
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim rep, vDetails, rep1, rep2, rep3, rep4, rep5, rep6, rep7, rep8
Dim sJSONResponse As String, sCBID As String
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sPNID As String, sPNDisplayName As String
Dim sPNSelf As String, sPSGODContext As String, sPSG As String
Dim sNotebookID As String, sState As String

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"
'

sFile1 = "C:\other\6.txt" 'object id / user ID 'display name "Erica Ingram"
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "RESPONSETEXT-------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")
sNotebookID = ""

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sections" '
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", sURL, False
    .setRequestHeader "Host", "graph.microsoft.com"
    .setRequestHeader "Authorization", "Bearer " & sToken
    .send
    apiWaxLRS = .responseText
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    apiWaxLRS = .responseText
    Debug.Print apiWaxLRS
    Debug.Print "--------------------------------------------"
    .abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")
For Each rep In vDetails
    sONID = rep("id")
    sCreatedDateTime = rep("createdDateTime")
    sSelfLink = rep("self")
    sDisplayName = rep("displayName")
    sLastModifiedDateTime = rep("lastModifiedDateTime")
    sIsDefault = rep("isDefault")
    sUserRole = rep("pagesUrl")
    sPSGODContext = rep("parentSectionGroup@odata.context")
    sIsShared = rep("parentNotebook@odata.context")

    Set rep1 = rep("createdBy")
    Set rep2 = rep1("user")
    sCBID = rep2("id")
    sCBDisplayName = rep2("displayName")
    Set rep3 = rep("lastModifiedBy")
    Set rep4 = rep1("user")
    sCBLastModifiedID = rep4("id")
    sCBLastModifiedName = rep4("displayName")
    Set rep5 = rep("links")
    Set rep6 = rep5("oneNoteClientUrl")
    sCBLClientURL = rep6("href")
    Set rep7 = rep("links")
    Set rep8 = rep5("oneNoteWebUrl")
    sCBLWebURL = rep8("href")
    Set rep9 = rep("parentNotebook")
    sPNID = rep9("id")
    sPNDisplayName = rep9("displayName")
    sPNSelf = rep9("self")

    Debug.Print "sONID = " & sONID
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sDisplayName = " & sDisplayName
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sIsDefault = " & sIsDefault
    Debug.Print "sUserRole = " & sUserRole
    Debug.Print "sCBID = " & sCBID
    Debug.Print "sCBDisplayName = " & sCBDisplayName
    Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
    Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "sPNID = " & sPNID
    Debug.Print "sPNDisplayName = " & sPNDisplayName
    Debug.Print "sPNSelf = " & sPNSelf
    Debug.Print "sPSGODContext = " & sPSGODContext
    Debug.Print "sIsShared = " & sIsShared
    Debug.Print "--------------------------------------------"
Next
End Function


Explanations



Get A List of Section Groups from a OneNote Notebook

Function fONGetSectionGroups()
'============================================================================
' Name        : fONGetSectionGroups
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetSectionGroups()
' Description : get ON section groups
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sJSONResponse As String, sCBID As String
Dim rep
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sPNID As String, sPNDisplayName As String
Dim sPNSelf As String, sPSGODContext As String, sPSG As String

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"
'
sFile1 = "C:\other\6.txt" 'object id / user ID 'display name "Erica Ingram"
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

sNotebookID = ""

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sectionGroups" '

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
sLine1 = ""
sLine2 = ""
sLine3 = ""
sLine4 = ""
apiWaxLRS = .responseText
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With
Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")
For Each rep In vDetails
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sDisplayName = rep("displayName")
sLastModifiedDateTime = rep("lastModifiedDateTime")
sIsDefault = rep("isDefault")
sUserRole = rep("userRole")
sIsShared = rep("isShared")
sSectionsUrl = rep("sectionsUrl")
sSectionGroupsUrl = rep("sectionGroupsUrl")
sPSGODContext = rep("parentSectionGroup@odata.context")
sPSG = Parsed("parentSectionGroup")

Set rep1 = rep("createdBy")
Set rep2 = rep1("user")
sCBID = rep2("id")
sCBLClientURL = rep1("links")
sCBDisplayName = rep2("displayName")
Set rep3 = rep("lastModifiedBy")
Set rep4 = rep1("user")
sCBLastModifiedID = rep4("id")
sCBLastModifiedName = rep4("displayName")
Set rep9 = rep("parentNotebook")
sPNID = rep9("id")
sPNDisplayName = rep9("displayName")
sPNSelf = rep9("self")

Debug.Print "sONID = " & sONID
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sDisplayName = " & sDisplayName
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sSectionsUrl = " & sSectionsUrl
Debug.Print "sSectionGroupsUrl = " & sSectionGroupsUrl
Debug.Print "sCBID = " & sCBID
Debug.Print "sCBDisplayName = " & sCBDisplayName
Debug.Print "sCBLastModifiedName = " & sCBLastModifiedName
Debug.Print "sCBLastModifiedID = " & sCBLastModifiedID
Debug.Print "sPNID = " & sPNID
Debug.Print "sPNDisplayName = " & sPNDisplayName
Debug.Print "sPNSelf = " & sPNSelf
Debug.Print "sPSGODContext = " & sPSGODContext
Debug.Print "--------------------------------------------"
Next

End Function

Explanations

Get A List of Pages from a OneNote Notebook/Section

Function fONGetPages()
'============================================================================
' Name        : fONGetPages
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetPages()
' Description : get ON pages
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sJSONResponse As String, sCBID As String
Dim rep
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sPNID As String, sPNDisplayName As String
Dim sPNSelf As String, sPSGODContext As String, sPSG As String

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"
'
sFile1 = "C:\other\6.txt" 'object id / user ID 'display name "Erica Ingram"
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

sNotebookID = ""

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
sLine1 = ""
sLine2 = ""
sLine3 = ""
sLine4 = ""
Debug.Print "RESPONSETEXT-------------------------------------------"
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

For Each rep In vDetails
sONID = rep("id")
sSelfLink = rep("self")
sCreatedDateTime = rep("createdDateTime")
sTitle = rep("title")
scontentUrl = rep("contentUrl")
sIsShared = rep("parentNotebook@odata.context")

sLastModifiedDateTime = rep("lastModifiedDateTime")

Set rep1 = rep("links")
Set rep2 = rep1("oneNoteClientUrl")
sCBLClientURL = rep2("href")
Set rep3 = rep1("oneNoteWebUrl")
sCBLWebURL = rep3("href")

Set rep9 = rep("parentSection")
sPNID = rep9("id")
sPNDisplayName = rep9("displayName")
sPNSelf = rep9("self")

Debug.Print "sONID = " & sONID
Debug.Print "sTitle = " & sTitle
Debug.Print "scontentUrl = " & scontentUrl
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sCBLClientURL = " & sCBLClientURL
Debug.Print "sCBLWebURL = " & sCBLWebURL
Debug.Print "sPNID = " & sPNID
Debug.Print "sPNDisplayName = " & sPNDisplayName
Debug.Print "sPNSelf = " & sPNSelf
Debug.Print "--------------------------------------------"
Next

End Function

Explanations

Get A Page ID from a OneNote Notebook Page

Function test()
Call fONGetPageID("a") '"a" is your search phrase.
End Function
Function fONGetPageID(sSearchInTitle As String)
'============================================================================
' Name        : fONGetSections
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetPageID()
' Description : get ON page by searching title
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sJSONResponse As String, sCBID As String
Dim rep
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sPNID As String, sPNDisplayName As String
Dim sPNSelf As String, sPSGODContext As String, sPSG As String

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"

sFile1 = "C:\other\6.txt" 'object id / user ID 'display name "Erica Ingram"
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")

sNotebookID = ""

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", sURL, False
.setRequestHeader "Host", "graph.microsoft.com"
.setRequestHeader "Authorization", "Bearer " & sToken
.send
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
sLine1 = ""
sLine2 = ""
sLine3 = ""
sLine4 = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
Set vDetails = Parsed("value")

For Each rep In vDetails
sTitle = rep("title")
If InStr(1, sTitle, sSearchInTitle) Then
    sONID = rep("id")
    sSelfLink = rep("self")
    sCreatedDateTime = rep("createdDateTime")
    sTitle = rep("title")
    scontentUrl = rep("contentUrl")
    sIsShared = rep("parentNotebook@odata.context")
    sLastModifiedDateTime = rep("lastModifiedDateTime")
    Set rep1 = rep("links")
    Set rep2 = rep1("oneNoteClientUrl")
    sCBLClientURL = rep2("href")
    Set rep3 = rep1("oneNoteWebUrl")
    sCBLWebURL = rep3("href")
    Set rep9 = rep("parentSection")
    sPNID = rep9("id")
    sPNDisplayName = rep9("displayName")
    sPNSelf = rep9("self")

    Debug.Print "sONID = " & sONID
    Debug.Print "sTitle = " & sTitle
    Debug.Print "scontentUrl = " & scontentUrl
    Debug.Print "sSelfLink = " & sSelfLink
    Debug.Print "sCreatedDateTime = " & sCreatedDateTime
    Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
    Debug.Print "sCBLClientURL = " & sCBLClientURL
    Debug.Print "sCBLWebURL = " & sCBLWebURL
    Debug.Print "sPNID = " & sPNID
    Debug.Print "sPNDisplayName = " & sPNDisplayName
    Debug.Print "sPNSelf = " & sPNSelf
    Debug.Print "--------------------------------------------"
    'return id of title
    Debug.Print "Page with Title " & Chr(34) & sTitle & Chr(34) & " has page ID number " & sONID & "."

    Debug.Print "--------------------------------------------"

Else

End If
Next

End Function

Explanations

Creating OneNote Pages in OneNote Notebooks

Function fONCreatePage()
'============================================================================
' Name        : fONGetSections
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONGetPageID()
' Description : get ON page by searching title
'============================================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String, sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim sResponseText As String, apiWaxLRS As String
Dim sONID As String, sSelfLink As String, sCreatedDateTime As String
Dim sDisplayName As String, sLastModifiedDateTime As String, isDefault As String
Dim sUserRole As String, sIsShared As String, sSectionsUrl As String, sSectionGroupsUrl As String
Dim sCBDisplayName As String, sCBLastModifiedName As String
Dim sCBLWebURL As String, sIsDefault As String
Dim sCBID As String
Dim rep
Dim sCBLastModifiedID As String, sCBLClientURL As String
Dim sHTMLPage As String
'
sFile1 = "C:\other\6.txt" 'object id / user ID
sFile2 = "C:\other\7.txt" 'directory tenant id
sFile3 = "C:\other\8.txt" 'application client id
sFile4 = "C:\other\9.txt" 'secret

'before auth if you make privilege changes you must visit this website and accept permissions.
'sState = ""
'"https://login.microsoftonline.com/" & sLine2 & "adminconsent?client_id=" & sLine3 & "&state=" & sState & "&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient"

Open sFile1 For Input As #1
Line Input #1, sLine1
Close #1

Open sFile2 For Input As #2
Line Input #2, sLine2
Close #2

Open sFile3 For Input As #3
Line Input #3, sLine3
Close #3

Open sFile4 For Input As #4
Line Input #4, sLine4
Close #4

sURL = "https://login.microsoftonline.com/" & sLine2 & "/oauth2/v2.0/token"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Host", "login.microsoftonline.com"
.send ("client_id=" & sLine3 & "&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=" & sLine4 & "&grant_type=client_credentials")
.waitForResponse
apiWaxLRS = .responseText
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)
sToken = Parsed("access_token")
sSectionID = ""

sHTMLPage = "<!DOCTYPE html><html><head>" & _
"<title>A page with <i>rendered</i> <b>images</b></title>" & _
"<meta name=" & Chr(34) & "created" & Chr(34) & " content=" & Chr(34) & "2015-07-22T09:00:00-08:00" & Chr(34) & " />" & _
"</head><body>

Here's an image from an online source:

" & "<img src=" & Chr(34) & _
    "https://www.aquoco.co/SQUARELOGO.jpg" & Chr(34) & " alt=" & Chr(34) & "an image on the page" & Chr(34) & _
    " width=" & Chr(34) & "500" & Chr(34) & " />" & "</body></html>"

sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/sections/" & sSectionID & "/pages"

With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", sURL, False
.setRequestHeader "Authorization", "Bearer " & sToken
.setRequestHeader "Content-Type", "text/html"
.setRequestHeader "Content-Disposition", "form-data", "name", "Presentation"
.send sHTMLPage
apiWaxLRS = .responseText
sToken = ""
sFile2 = ""
sFile3 = ""
sFile4 = ""
sURL = ""
sLine1 = ""
sLine2 = ""
sLine3 = ""
sLine4 = ""
Debug.Print apiWaxLRS
Debug.Print "--------------------------------------------"
.abort
End With

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)

sIsShared = Parsed("@odata.context")
sONID = Parsed("id")
sSelfLink = Parsed("self")
sCreatedDateTime = Parsed("createdDateTime")
sTitle = Parsed("title")
scontentUrl = Parsed("contentUrl")
sLastModifiedDateTime = Parsed("lastModifiedDateTime")
Set rep1 = Parsed("links")
Set rep2 = rep1("oneNoteClientUrl")
sCBLClientURL = rep2("href")
Set rep3 = rep1("oneNoteWebUrl")
sCBLWebURL = rep3("href")

Debug.Print "sIsShared = " & sIsShared
Debug.Print "sONID = " & sONID
Debug.Print "sTitle = " & sTitle
Debug.Print "scontentUrl = " & scontentUrl
Debug.Print "sSelfLink = " & sSelfLink
Debug.Print "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sCBLClientURL = " & sCBLClientURL
Debug.Print "sCBLWebURL = " & sCBLWebURL
Debug.Print "--------------------------------------------"

End Function

Published by Erica L. Ingram

Having produced court transcripts for a decade, I understand how complex practitioners' needs are when it comes to crafting legal documents. If you're a solo practitioner or professional services provider, your time is sliced thin and I can help you alleviate that. To accomplish that, I'll be posting helpful hints and how-tos here on how to use VBA in Office to really stand out at work and do more with less (effort).