Part 2d: Using VBA with JSON: OneNote

Posted byErica L. Ingram

This is the second part of our two-part series on using VBA with JSON in OneNote. Today, we’ll learn how to delete pages, update them, copy to a section, and create sections and section groups.

Using VBA with JSON: OneNote, Part 1 on GitHub
Using VBA with JSON: OneNote, Part 1
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.

Delete Pages From OneNote Notebook

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 delete a page from one of your notebooks.

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

Function fONDeletePage()
'====================================================
' Name        : fONDeletePage
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONDeletePage()
' Description : delete ON page by page ID
'====================================================
Dim sURL As String, sToken As String, sLine4 As String
Dim Parsed As Dictionary
Dim sFile1 As String, sFile2 As String, sFile3 As String
Dim sFile4 As String
Dim sLine1 As String, sLine2 As String, sLine3 As String
Dim apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String

Next, we’ll get our token as we’ve done before:

'preparing to get token
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"

'getting 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")

Now we’re to the new part. You should have gotten a page ID from a function covered in the previous blog post, and it goes in the variable in this chunk of code:

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

'fill this in with your page ID
sPageID = ""

Next, we construct the URL that we’ll call on to delete the page. The URL can be constructed in a couple different ways and i’ve suggested some here, but there is one uncommented by default.

'several URL options to choose from for deleting pages:
'personal account:
'DELETE https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID

'DELETE https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID

'DELETE https://graph.microsoft.com/v1.0/sites/{id}/onenote/pages/{id}
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sSiteID & "/onenote/pages/" & sPageID

Now, we’re calling on the URL to tell them to delete the page, assigning the response to a variable, and then clearing out information from the sensitive variables since we don’t need it anymore.

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "DELETE", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "text/html"
    .send
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With

Lastly, we’ll print some info to the debug window (Ctrl+G) so we know it deleted successfully. If it isn’t successful, you’ll see the response so you can troubleshoot it.

Debug.Print "Page Deleted, ID No.:  " & sPageID
Debug.Print "Status: " & apiWaxLRS & "   |   (204 means deleted successfully)"
Debug.Print "--------------------------------------------"

Update Pages From OneNote Notebook

Now, we want to update a page already in the OneNote notebook.

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

Function fONUpdatePage()
'============================================================================
' Name        : fONUpdatePage
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONUpdatePage()
' Description : update ON page by page ID
'============================================================================
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 apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String

Next, we’ll get our token as we’ve done before:

'preparing to get token
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"

'getting 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")

Now, we put our page ID that we want to update into a variable and construct the URL we’ll be calling on to update the page. There is one default uncommented, but i have constructed and commented out a couple other versions if you have a need for them.

'fill this in with your page ID
sPageID = ""
        
'several URL options to choose from for updating pages:
'personal account:
'PATCH https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}/content
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID & "/content"

'PATCH https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}/content
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID & "/content"

'PATCH https://graph.microsoft.com/v1.0/sites/{id}/onenote/pages/{id}/content
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sSiteID & "/onenote/pages/" & sPageID & "/content"

Next, we’ll need to construct the JSON string we’ll be sending to tell Microsoft what updates you are making. I have provided an example modified from an example on Microsoft’s site, but you may edit this as necessary.

'update request follows json format:
sUpdateJSON = "[{" & _
    "'target':'#para-id'," & _
    "'action':'insert'," & _
    "'position':'before'," & _
    "'content':'<img src=" & Chr(34) & "image-url-or-part-name" & Chr(34) & "alt=" & Chr(34) & "image-alt-text" & Chr(34) & " />'" & _
  "},{" & _
    "'target':'#list-id'," & _
    "'action':'append'," & _
    "'content':'<li>new-page-content</li>'" & _
  "}]"

Next, we’ll call on the URL, send the JSON string we constructed with the required request headers, wait for a response, and clear out the sensitive information from their variables.

'call on URL to delete page
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "PATCH", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "application/json"
    .send sUpdateJSON
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With

Lastly, we’ll print some info to the debug window (Ctrl+G) so we know it updated successfully. If it isn’t successful, you’ll see the response so you can troubleshoot it.

'print status on successful update
Debug.Print "Page Updated, ID No.:  " & sPageID
Debug.Print "Status: " & apiWaxLRS & "   |   (204 means updated successfully)"
Debug.Print "--------------------------------------------"
End Function

Copy Pages to Section of OneNote Notebook

Now, we want to copy a page already in the OneNote notebook to a certain section.

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

Function fONCopyPageToSection()
'============================================================================
' Name        : fONCopyPageToSection
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONCopyPageToSection()
' Description : copy ON page by page ID to section
'============================================================================
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 apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String

Next, we’ll get our token as we’ve done before:

'preparing to get token
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"

'getting 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")

Next, we’ll fill in our page ID/section IDs and construct our URL:

'fill this in with your page ID & desired section ID to copy to
sPageID = ""
sSectionID = ""

'several URL options to choose from for copying pages:

'personal account:
'POST https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}/copyToSection
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID & "/copyToSection"

'POST https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}/copyToSection
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID & "/copyToSection"

'POST https://graph.microsoft.com/v1.0/me/onenote/pages/{id}/copyToSection
'sURL = "https://graph.microsoft.com/v1.0/me/onenote/pages/" & sPageID & "/copyToSection"

Next we’ll construct a JSON request string to send to Microsoft:

'Here, we're constructing the JSON string we need to send so Microsoft knows which page to copy and to which section to copy.
sCopyJSON = "{" & _
  Chr(34) & "id" & Chr(34) & ": " & Chr(34) & sPageID & Chr(34) & "," & _
  Chr(34) & "groupId" & Chr(34) & ": " & Chr(34) & sSectionID & Chr(34) & _
    "}"

So now we’re calling on the URL with some required headers and sending the JSON string we just made. Then we put the response in a variable and clear out the sensitive information from their respective variables.

'call on URL to copy page to desired section
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "POST", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Content-Length", "52"
    .send sCopyJSON
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With

Lastly, we’ll print some info to the debug window so we know it was successful or, if unsuccessful, an error message that will help you troubleshoot.

'print status on successful update
Debug.Print "Page ID No.:  " & sPageID & "   |   Section ID No.:  " & sSectionID
Debug.Print "Status: " & apiWaxLRS & "   |   (202 means updated successfully)"
Debug.Print "--------------------------------------------"

End Function

Create Section in OneNote Notebook

Last function we’ll go over is how to create a section in OneNote.

This part is probably familiar to you; we have our info box and variable declaration, followed by getting some sensitive necessary info into variables and getting our token. So i won’t go over this since this part is the same in every function we’ve gone over in OneNote.

Function fONCreateSection()
'============================================================================
' Name        : fONCreateSection
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONCreateSection()
' Description : create ON section
'============================================================================
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 sJSONRequest As String, sNotebookID 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")

Next, we’ll put the notebook ID where we want the new section in a variable and name our new section.

'notebook id where you want your new section to go
sNotebookID = ""
'set new section name here
sSectionName = "Testing VBA New Section 1"
'sGroupID = ""

Now we need a URL to call on. I have commented several examples, but the default one is first and uncommented. 

'POST https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/notebooks/{id}/sections
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/groups/{id}/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/sites/{id}/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sGroupID & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/me/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/me/onenote/notebooks/" & sNotebookID & "/sections"

Now, we have a very small JSON string we need to construct to send with our request when we call on the URL. Notice your section name goes in here.

sJSONRequest = "{" & Chr(34) & "displayName" & Chr(34) & ": " & Chr(34) & sSectionName & Chr(34) & "}"

Now, we’ll call on the URL and send with it the JSON string we just constructed, put the response into a variable, and clear out the sensitive information.

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "POST", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-type", "application/json"
    .setRequestHeader "Content-length", "27"
    .send sJSONRequest
    apiWaxLRS = .responseText
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    Debug.Print apiWaxLRS
    Debug.Print "--------------------------------------------"
    .abort
End With

Now, similar to our other functions, we’re going to parse the response into variables and print it out in the debug window.

Set Parsed = JsonConverter.ParseJson(apiWaxLRS)

sIsShared = Parsed("@odata.context")
sONID = Parsed("id")
sSelfLink = Parsed("self")

sCreatedDateTime = Parsed("createdDateTime")
sTitle = Parsed("displayName")
scontentUrl = Parsed("pagesUrl")
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 "--------------------------------------------"


'print status on successful section create
Debug.Print "Section ID No.:  " & sONID & "   |   Section Name:  " & sSectionName
Debug.Print "Status: " & apiWaxLRS
Debug.Print "201 updated successfully"
Debug.Print "--------------------------------------------"

End Function

Final Code

Update Pages From OneNote Notebook

Function fONUpdatePage()
'============================================================================
' Name        : fONUpdatePage
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONUpdatePage()
' Description : update ON page by page ID
'============================================================================
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 apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String
Dim sUpdateJSON As String
'preparing to get token
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"

'getting 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")

'fill this in with your page ID
sPageID = ""
        
'several URL options to choose from for updating pages:
'personal account:
'PATCH https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}/content
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID & "/content"

'PATCH https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}/content
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID & "/content"

'PATCH https://graph.microsoft.com/v1.0/sites/{id}/onenote/pages/{id}/content
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sSiteID & "/onenote/pages/" & sPageID & "/content"



'update request follows json format:
sUpdateJSON = "[{" & _
    "'target':'#para-id'," & _
    "'action':'insert'," & _
    "'position':'before'," & _
    "'content':'<img src=" & Chr(34) & "image-url-or-part-name" & Chr(34) & "alt=" & Chr(34) & "image-alt-text" & Chr(34) & " />'" & _
  "},{" & _
    "'target':'#list-id'," & _
    "'action':'append'," & _
    "'content':'<li>new-page-content</li>'" & _
  "}]"
  

'call on URL to delete page
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "PATCH", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "application/json"
    .send sUpdateJSON
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With



'print status on successful update
Debug.Print "Page Updated, ID No.:  " & sPageID
Debug.Print "Status: " & apiWaxLRS & "   |   (204 means updated successfully)"
Debug.Print "--------------------------------------------"



End Function

Delete Pages From OneNote Notebook

Function fONDeletePage()
'============================================================================
' Name        : fONDeletePage
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONDeletePage()
' Description : delete ON page by page ID
'============================================================================
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 apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String

'preparing to get token
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"

'getting 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")

'fill this in with your page ID
sPageID = ""
'sGroupID = ""
'sSiteID = ""
        
'several URL options to choose from for deleting pages:
'personal account:
'DELETE https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID

'DELETE https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID

'DELETE https://graph.microsoft.com/v1.0/sites/{id}/onenote/pages/{id}
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sSiteID & "/onenote/pages/" & sPageID

'call on URL to delete page
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "DELETE", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "text/html"
    .send
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With

'print status on successful delete
Debug.Print "Page Deleted, ID No.:  " & sPageID
Debug.Print "Status: " & apiWaxLRS & "   |   (204 means deleted successfully)"
Debug.Print "--------------------------------------------"

End Function

Copy Pages to Section of OneNote Notebook

Function fONCopyPageToSection()
'============================================================================
' Name        : fONCopyPageToSection
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONCopyPageToSection()
' Description : copy ON page by page ID to section
'============================================================================
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 apiWaxLRS As String, sGroupID As String
Dim sCBID As String, sPageID As String, sSiteID As String
Dim sCopyJSON As String, sSectionID As String
'preparing to get token
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"

'getting 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")


'fill this in with your page ID & desired section ID to copy to
sPageID = ""
sSectionID = ""

'several URL options to choose from for copying pages:

'personal account:
'POST https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/pages/{id}/copyToSection
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/pages/" & sPageID & "/copyToSection"

'POST https://graph.microsoft.com/v1.0/groups/{id}/onenote/pages/{id}/copyToSection
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/pages/" & sPageID & "/copyToSection"

'POST https://graph.microsoft.com/v1.0/me/onenote/pages/{id}/copyToSection
'sURL = "https://graph.microsoft.com/v1.0/me/onenote/pages/" & sPageID & "/copyToSection"

'Here, we're constructing the JSON string we need to send so Microsoft knows which page to copy and to which section to copy.
sCopyJSON = "{" & _
  Chr(34) & "id" & Chr(34) & ": " & Chr(34) & sPageID & Chr(34) & "," & _
  Chr(34) & "groupId" & Chr(34) & ": " & Chr(34) & sSectionID & Chr(34) & _
    "}"


'call on URL to copy page to desired section
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "POST", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Content-Length", "52"
    .send sCopyJSON
    apiWaxLRS = .responseText
    If apiWaxLRS = "" Then
        apiWaxLRS = .status
    End If
    sToken = ""
    sFile2 = ""
    sFile3 = ""
    sFile4 = ""
    sURL = ""
    sLine1 = ""
    sLine2 = ""
    sLine3 = ""
    sLine4 = ""
    'Debug.Print apiWaxLRS
    'Debug.Print "--------------------------------------------"
    .abort
End With


'print status on successful update
Debug.Print "Page ID No.:  " & sPageID & "   |   Section ID No.:  " & sSectionID
Debug.Print "Status: " & apiWaxLRS & "   |   (202 means updated successfully)"
Debug.Print "--------------------------------------------"

End Function

Create Section in OneNote Notebook

Function fONCreateSection()
'============================================================================
' Name        : fONCreateSection
' Author      : Erica L Ingram
' Copyright   : 2019, A Quo Co.
' Call command: Call fONCreateSection()
' Description : create ON section
'============================================================================
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 sJSONRequest As String, sNotebookID 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")

'notebook id where you want your new section to go
sNotebookID = ""
'set new section name here
sSectionName = "Testing VBA New Section 1"
'sGroupID = ""

'POST https://graph.microsoft.com/v1.0/users/{id | userPrincipalName}/onenote/notebooks/{id}/sections
sURL = "https://graph.microsoft.com/v1.0/users/" & sLine1 & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/groups/{id}/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/groups/" & sGroupID & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/sites/{id}/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/sites/" & sGroupID & "/onenote/notebooks/" & sNotebookID & "/sections"

'POST https://graph.microsoft.com/v1.0/me/onenote/notebooks/{id}/sections
'sURL = "https://graph.microsoft.com/v1.0/me/onenote/notebooks/" & sNotebookID & "/sections"

sJSONRequest = "{" & Chr(34) & "displayName" & Chr(34) & ": " & Chr(34) & sSectionName & Chr(34) & "}"

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "POST", sURL, False
    .setRequestHeader "Authorization", "Bearer " & sToken
    .setRequestHeader "Content-type", "application/json"
    .setRequestHeader "Content-length", "27"
    .send sJSONRequest
    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")
sCreatedDateTime = Parsed("createdDateTime")
sTitle = Parsed("displayName")
scontentUrl = Parsed("pagesUrl")
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 "sCreatedDateTime = " & sCreatedDateTime
Debug.Print "sLastModifiedDateTime = " & sLastModifiedDateTime
Debug.Print "sCBLClientURL = " & sCBLClientURL
Debug.Print "sCBLWebURL = " & sCBLWebURL
Debug.Print "--------------------------------------------"


'print status on successful section create
Debug.Print "Section ID No.:  " & sONID & "   |   Section Name:  " & sSectionName
Debug.Print "Status: " & apiWaxLRS
Debug.Print "201 updated successfully"
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).