MrExcel.com - Your One Stop for Excel Tips & Solutions

Using Excel VBA to Push Content to OneNote

October 01, 2004

In August, Microsoft released version SP1 of OneNote. This is a must-have upgrade. They added many incredible features, include an application programming interface that allows other applications to push data to OneNote.

Microsoft offers several excellent websites that will teach you how to use VB.Net in order to push data into OneNote. But, since this is the MrExcel site, you and I and the other 200 million Office users are most concerned about how to push data to OneNote using Office VBA. I am happy to say that this CAN be done. This page will walk you through all that you need to get it done.

I will assume that you are moderately familiar with VBA. If you are not, I highly recommend VBA & Macros for Microsoft Excel, the book designed to take someone up the VBA learning curve.

Overview

You can send data to OneNote by formatting the data as XML data. XML is a fairly new concept. It is sort of like HTML. Think of it as a CSV file on steroids. You can read my Introduction to XML.

Basically, your VBA program needs to write out an XML file, then pass the contents of the XML file to OneNote using the .Import method. The XML file needs to contain these elements:

  • An EnsurePage element for each page that you want to write to. If the page does not exist, OneNote will create the page for you. In theory, you are supposed to have control and place the page after a specific existing page. However, in practice, this does not appear to work.
  • A PlaceObject element for each item that you want to add to the page. You specify the X & Y location for the item and the source of the item. An item can either be an image, an Ink object, or text in HTML format. You would think that since OneNote reads from HTML, you could actually pass a table with TR and TD tags, but this does not work. You are limited to passing text with BR and P tags to add linefeeds. UL & LI tags to appear to work. Font tags do work.

The Gotcha

In order to update an existing page, you must know the Globally Unique Identifier (GUID) for that page. There does not appear to be a way to find the GUID for an existing page in OneNote. This means that you can only update or delete items on an existing page if you programmatically created the page and have stored the GUID used to create that page in your workbook. The example below uses an out-of-the way place on the worksheet to save the GUID for the page, the data table and the chart.

GUIDs

Every new page in OneNote needs a GUID. Every new object placed on a page needs a GUID. While it is easy to generate GUIDs from VB.Net, finding a way to generate GUID's from VBA has been elusive. All 200 million Office VBA users need to give a tip of the cap to Michael Kaplan of Trigeminal Software. Michael seems to be the only guy in the world to break the code on how to generate a GUID from VBA. He has graciously shared this code with the world. Check out the complete code at his website. With Michael's permission, I've copied just the functions needed to generate a new GUID in VBA here. Insert a module in your project and include the following code in that module. 

'------------------------------------------
'  basGuid from http://www.trigeminal.com/code/guids.bas
' You may use this code in your applications, just make
' sure you keep the (c) notice and don't publish it anywhere
' as your own
' Copyright (c) 1999 Trigeminal Software, Inc. All Rights Reserved
'------------------------------------------

Option Compare Binary

' Note that although Variants now have
' a VT_GUID type, this type is unsupported in VBA,
' so we must define our own here that will have the same
' binary layout as all GUIDs are expected by COM to
' have.
Public Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Public Declare Function StringFromGUID2 Lib "ole32.dll" _
    (rclsid As GUID, ByVal lpsz As Long, ByVal cbMax As Long) As Long
Public Declare Function CoCreateGuid Lib "ole32.dll" _
    (rclsid As GUID) As Long

'------------------------------------------------------------
'   StGuidGen
'
'   Generates a new GUID, returning it in canonical
'   (string) format
'------------------------------------------------------------
Public Function StGuidGen() As String
    Dim rclsid As GUID

    If CoCreateGuid(rclsid) = 0 Then
        StGuidGen = StGuidFromGuid(rclsid)
    End If
End Function

'------------------------------------------------------------
'   StGuidFromGuid
'
'   Converts a binary GUID to a canonical (string) GUID.
'------------------------------------------------------------
Public Function StGuidFromGuid(rclsid As GUID) As String
    Dim rc As Long
    Dim stGuid As String

    ' 39 chars  for the GUID plus room for the Null char
    stGuid = String$(40, vbNullChar)
    rc = StringFromGUID2(rclsid, StrPtr(stGuid), Len(stGuid) - 1)
    StGuidFromGuid = Left$(stGuid, rc - 1)
End Function

Adding a Reference

In VBA, use Tools - References to add a reference to the OneNote 1.1 Object Library. This will allow you to declare a new CSimpleImporter object and then use the .Import and .NavigateToPage methods on the object.

Case Study

This Excel workbook contains a daily reporting system. There is one worksheet for each store in a local chain of stores. Each page contains a table showing daily sales and a chart showing progress towards the monthly goal.

The VBA code will add a new section called DailySales. One new page will be added for each store. The Chart from the worksheet is exported as a GIF file and imported to OneNote. The data from the worksheet is added to OneNote as an HTML column.

Daily Sales
Daily Sales

The following code is used in Excel. 

Sub CreateUpdateOneNoteReport()
    ' Requires basGuid module from above
    Dim Cht As Chart
    fname = "C:\OneNoteImport.xml"
    On Error Resume Next
    Kill (fname)
    On Error GoTo 0

    ' Do we need new GUID's?
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Range("J22").Value > "" Then
            ws.Range("J22").Value = StGuidGen()
        End If
        If Not ws.Range("J23").Value > "" Then
            ws.Range("J23").Value = StGuidGen()
        End If
        If Not ws.Range("J24").Value > "" Then
            ws.Range("J24").Value = StGuidGen()
        End If
    Next ws

    ' Build a temporary XML file
    fname = "C:\OneNoteImport.xml"
    On Error Resume Next
    Kill (fname)
    On Error GoTo 0

    Open fname For Output As #1
    Print #1, "<!--?xml version=""1.0""?--> "
    Print #1, "<import xmlns="" http:="" schemas.microsoft.com="" office="" onenote="" 2004="" import""=""> "
    ' Make sure that for each page, we have a page
    FirstPage = True
    DateStr = Format(Date - 1, "yyyy-mm-dd") & "T21:00:00-06:00"
    For Each ws In ThisWorkbook.Worksheets
        ThisTitle = ws.Name
        ThisGuid = ws.Range("J22").Value
        Print #1, " <ensurepage path="" dailyreport.one"""="" print="" #1,="" "="" guid="" &="" thisguid="" """="" date="" datestr="" if="" not="" firstpage="" then="" insertafter="" lastguid="" else="" firstguid="ThisGuid" end="" title="" thistitle=""></ensurepage>"
        FirstPage = False
        LastGuid = ThisGuid
    Next ws

    For Each ws In ThisWorkbook.Worksheets
        ThisTitle = ws.Name
        ThisImage = "C:\" & ThisTitle & ".gif"
        ThisGuid = ws.Range("J22").Value
        ChartGuid = ws.Range("J24").Value
        TableGuid = ws.Range("J23").Value

        ' Export the Chart
        Set Cht = ws.ChartObjects(1).Chart
        Cht.Export Filename:=ThisImage, FilterName:="GIF"

        ' Place the Chart on the top, right side

        Print #1, ""
        Print #1, " <placeobjects pagepath="" dailyreport.one""="" "="" print="" #1,="" pageguid="" &="" thisguid="" """="">"
        Print #1, ""
        Print #1, " <object guid="" "="" &="" chartguid="" """="">"
        Print #1, " <position x="" 258""="" y="" 36""=""></position>"
        Print #1, " <img backgroundimage="" false""="">"
        Print #1, " <file path="" "="" &="" thisimage="" """=""></file>"
        Print #1, " "
        Print #1, " </object>"
        Print #1, ""

        ' Place table of sales on left side
        ' Place the text in the first column
        Print #1, " <object guid="" "="" &="" tableguid="" """="">"
        Print #1, " <position x="" 36""="" y=""></position>"
        Print #1, " <outline width="" 210""="">"
        Print #1, " "
        Print #1, " <data>"
        Print #1, " <!--[CDATA["
        'Build HTML String
        HTMLStr = "<html--><h1>Daily Sales</h1>"
        For i = 2 To 32
            If ws.Cells(i, 2).Value > 0 Then
                HTMLStr = HTMLStr & ws.Cells(i, 1).Value _
                    & " - $" & ws.Cells(i, 2).Value & "<br>"
            End If
        Next i
        HTMLStr = HTMLStr & ""
        Print #1, HTMLStr
        Print #1, " ]]>"
        Print #1, " </data>"
        Print #1, " "
        Print #1, " </outline>"
        Print #1, " </object>"
        Print #1, ""
        Print #1, " </placeobjects>"
    Next ws
    Print #1, ""
    Print #1, "</import>"
    Close #1

    ' Load file into a string variable
    XMLStr = ""
    Open fname For Input As #1
    Do
        Line Input #1, strData
        XMLStr = XMLStr & strData
    Loop While EOF(1) = False
    Close #1

    Dim CSI As New CSimpleImporter
    ' Import the string
    CSI.Import XMLStr

    'navigate to the page
    CSI.NavigateToPage bstrPath:="DailyReport.one", bStrGuid:=FirstGuid

End Sub

The resulting OneNote notebook looks like this.

Resulting OneNote Notebook
Resulting OneNote Notebook

Apparent Bugs

In the book, I mentioned an apparent bug with "insertafter". I forgot that XML is case sensitive. If you use "insertAfter", then everything works fine. Thanks to Donovan Lange at Microsoft for pointing this out.

I am guessing that the next issue is not a bug - the code is probably working like Microsoft intended, but they missed an opportunity to do something the right way. You are allowed to specify a date and time in the EnsurePage section of the XML. This date and time is only used if the page does not exist. Given that Microsoft later allows us to update the page by remembering the GUID, they really should have allowed us to update the date and time on the page. In the example here, we are pushing new data each day, yet the date is always going to show that it is as of the first time that the program was run. This is disappointing.