CODEWITHSUNDEEP
exceloffice-interoponenotevba
Sebastien
Sebastien

Reputation: 55

Updating OneNote from VBA Excel

I'm trying to automate the addition of a link to a report in OneNote. Basically, I have a already running macro producing a daily report in VBA Excel and saving a values only copy in a given folder. I would now like to add to the code so that an hyperlink is created in OneNote so the end-user may access the values-only file by cliking on that hyperlink.

I've found a piece of VBA code that creates a new page in a Notebook on Microsoft webpage: Create New Page

I have modified the code so that it'll go to the Notebook, Section and then Page I tell it to. Then I can have VBA it fetch back the PageContent through XML format.

Now here's the thing: I have a table for the month that starts only with one line two columns ie. the headers (report date and link). Now everyday, I wish to add a line to that table before writing the day's date and the link.

How would I do that in OneNote with VBA Excel ? Is it possible ? Or would I need to change to VB or C# to be able to use Office-InterOp to do so??

If I manage to create an extra line, I think I'd have no problem finding the two empty cells and writing the date and an hyperlink.

Can someone please help?

thanks Sebastien

Upvotes: 1

Views: 4894

Answers (2)

MT1
MT1

Reputation: 984

The sample page XML given by Omer does not work as posted for a couple of reasons, first change is 2010 should now be 2013! The changed XML also needs a couple of extra modifications so I have posted it here. Note the placeholders for the substitution of the page ID and the page title.

<one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2013/onenote" ID="{00}">
    <one:Title selected="partial" lang="en-US">
        <one:OE style="font-family:Calibri;font-size:17.0pt">
            <one:T><![CDATA[{11}]]></one:T>
        </one:OE>
    </one:Title>
    <one:Outline>
        <one:OEChildren>
            <one:OE>
                <one:Table>
                    <one:Row>
                        <one:Cell>
                            <one:OEChildren>
                                <one:OE>
                                    <one:T><![CDATA[Cell1]]></one:T>
                                </one:OE>
                            </one:OEChildren>
                        </one:Cell>
                        <one:Cell>
                            <one:OEChildren>
                                <one:OE>
                                    <one:T><![CDATA[Cell2]]></one:T>
                                </one:OE>
                            </one:OEChildren>
                        </one:Cell>
                    </one:Row>
                    <one:Row>
                        <one:Cell>
                            <one:OEChildren>
                                <one:OE>
                                    <one:T><![CDATA[Cell3]]></one:T>
                                </one:OE>
                            </one:OEChildren>
                        </one:Cell>
                        <one:Cell>
                            <one:OEChildren>
                                <one:OE>
                                    <one:T><![CDATA[Cell4]]></one:T>
                                </one:OE>
                            </one:OEChildren>
                        </one:Cell>
                    </one:Row>
                </one:Table>
            </one:OE>
        </one:OEChildren>
    </one:Outline>
</one:Page>

We are using the above XML and replacing the whole page so you do need to start from scratch with a new page. Tables cannot be manipulated by their ID so a table cannot be added to an existing page.

This function fnMakePage returns the ID of the new page it creates using oneNote.CreateNewPage

Function fnMakePage(sSectionName As String, sPageName As String) As String
    Dim oneNote As New oneNote.Application
    Dim sNewPageID As String
    oneNote.CreateNewPage fnGetSectionID(sSectionName), sNewPageID
    fnMakePage = sNewPageID
    ' this creates an untitled page
    Dim sPageContentXML As String
    Dim xDoc As New MSXML2.DOMDocument60
    oneNote.GetPageContent sNewPageID, sPageContentXML, piAll, xs2013
    xDoc.LoadXML (sPageContentXML)
    ' Find the Title element.
    Dim xTitleNode As MSXML2.IXMLDOMNode
    xDoc.SetProperty "SelectionNamespaces", "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
    Set xTitleNode = xDoc.SelectSingleNode("//one:Page/one:Title/one:OE/one:T")
    ' Get the CDataSection where OneNote stores the Title text.
    Dim xCdataChild As MSXML2.IXMLDOMNode
    Set xCdataChild = xTitleNode.SelectSingleNode("text()")
    ' Change the title in the local XML copy.
    xCdataChild.Text = sPageName
    ' Write the update to OneNote.
    oneNote.UpdatePageContent xDoc.XML
End Function

This function fnGetSectionID returns the ID of a named section. If the named section is not found then the function returns the ID of the first section in the notebook.

Function fnGetSectionID(sSectionName As String) As String
    Dim oneNote As New oneNote.Application
    Dim sSectionsXML As String
    oneNote.GetHierarchy "", hsSections, sSectionsXML, xs2013
    Dim xDoc As New MSXML2.DOMDocument60
    xDoc.LoadXML (sSectionsXML)
    Dim nodes As MSXML2.IXMLDOMNodeList
    Dim node As MSXML2.IXMLDOMNode
    xDoc.SetProperty "SelectionNamespaces", "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
    Set nodes = xDoc.DocumentElement.SelectNodes("//one:Section")
    For Each node In nodes
        If sSectionName = node.Attributes.getNamedItem("name").Text Then
            fnGetSectionID = node.Attributes.getNamedItem("ID").Text
            Exit Function
        Else
            ' if given section is not found use the first section in the notebook
            Dim node0 As MSXML2.IXMLDOMNode
            Set node0 = nodes(0)
            fnGetSectionID = node0.Attributes.getNamedItem("ID").Text
        End If
    Next
End Function

This routine loads the above XML from a file, this is much simpler than trying to code it in VBA!

Sub sbPageTable()
    Dim oneNote As New oneNote.Application
    Dim sPageTable As String
    Dim sPageID As String
    sPageTable = fnRead2("C:\Users\david\Desktop\on_Table.xml")
    sPageID = fnGetPageID("The Table Page Two")
    If sPageID = "" Then
        MsgBox "page not found"
        Exit Sub
    End If
    sPageTable = Replace(sPageTable, "{00}", sPageID)
    sPageTable = Replace(sPageTable, "{11}", "TablePage12")
    MsgBox sPageTable
    oneNote.UpdatePageContent sPageTable, , xs2013, False
End Sub

This function reads the named file into a string.

Public Function fnRead2(sFile As String) As String
 Dim sText As String
 Dim i As Integer
 Open sFile For Input As #1
 sText = ""
 For i = 1 To 1000
    If Not EOF(1) Then sText = sText & Input(1, #1)
 Next i
 Close #1
 fnRead2 = sText
End Function

Upvotes: 0

Omer Atay - MSFT
Omer Atay - MSFT

Reputation: 287

After you get the page content in XML format, you just need to modify it to include the Table (or add new rows/cells to it and then call UpdatePageContent. Here is a sample page XML that has a simple Table:

<one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2010/onenote">
  <one:Outline>
    <one:OEChildren>
      <one:OE>
        <one:Table>
          <one:Row>
            <one:Cell>
              <one:OEChildren>
                <one:OE><one:T><![CDATA[Cell1]]></one:T></one:OE>
              </one:OEChildren>
            </one:Cell>
            <one:Cell>
              <one:OEChildren>
                <one:OE><one:T><![CDATA[Cell2]]></one:T></one:OE>
              </one:OEChildren>
            </one:Cell>
          </one:Row>
          <one:Row>
            <one:Cell>
              <one:OEChildren>
                <one:OE><one:T><![CDATA[Cell3]]></one:T></one:OE>
              </one:OEChildren>
            </one:Cell>
            <one:Cell>
              <one:OEChildren>
                <one:OE><one:T><![CDATA[Cell4]]></one:T></one:OE>
              </one:OEChildren>
            </one:Cell>
          </one:Row>
        </one:Table>
      </one:OE>
    </one:OEChildren>
  </one:Outline>
</one:Page>

Upvotes: -1

Related Questions