none
Creating a notebook in OneNote from an Excel spreadsheet RRS feed

  • Question

  • I want to create notebooks from spreadsheets.  One notebook per math course.  I have listed in a spreadsheet all topics in a course and have those topics broken into Categories, Subcategories, and Topics.

    For example, 6th grade math has categories: Whole Numbers, Decimals, Fractions...  Whole Numbers has Subcategories: Addition and Subtraction, Multiplication and Division, Ordering and Rounding, Prime Numbers...  

    The 6th grade math course has 438 topics, each of which belongs to a Category and Subcategory.  I have assigned each topic (name) a unique ID number.  So the spreadsheet has columns:  ID/Category/Subcategory/Topic Name.

    The 7th grade math course has 516 topics and its own Categories, Subcategories, and topics.

    Id like to create the notebooks to have the Categories as Sections, the Subcategories as pages, and the topics as subpages.

    Is there a way I can set up these notebooks from the courses' spreadsheets without having to do each of the 438 and 516 topics one-by-one?

    I hope I have explained this well enough.  Any help would be greatly appreciated.

     
    Thursday, November 5, 2015 6:59 PM

Answers

  • Hi Betsynovice,

    >> I tried inserting the macro and got an error message

    What is your error message? Did you use OneNote 2013 or any other OneNote version? For Note 2013, I suggest you try the code below which create a new page.

    Sub CreateNewPage()
        ' Connect to OneNote 2010.
        ' To see the results of the code,
        ' you'll want to ensure the OneNote 2010 user
        ' interface is visible.
        Dim oneNote As oneNote.Application
        Set oneNote = New oneNote.Application
        
        ' Get all of the Notebook nodes.
        Dim nodes As MSXML2.IXMLDOMNodeList
        Set nodes = GetFirstOneNoteNotebookNodes(oneNote)
        If Not nodes Is Nothing Then
            ' Get the first OneNote Notebook in the XML document.
            Dim node As MSXML2.IXMLDOMNode
            Set node = nodes(0)
            Dim noteBookName As String
            noteBookName = node.Attributes.getNamedItem("name").Text
            
            ' Get the ID for the Notebook so the code can retrieve
            ' the list of sections.
            Dim notebookID As String
            notebookID = node.Attributes.getNamedItem("ID").Text
            
            ' Load the XML for the Sections for the Notebook requested.
            Dim sectionsXml As String
            oneNote.GetHierarchy notebookID, hsSections, sectionsXml, xs2013
           ' Dim a As MSXML2.DOMDocument60
            Dim secDoc As MSXML2.DOMDocument60
            Set secDoc = New MSXML2.DOMDocument60
        
            If secDoc.LoadXML(sectionsXml) Then
                ' select the Section nodes
                Dim secNodes As MSXML2.IXMLDOMNodeList
                Debug.Print secDoc.DocumentElement.XML
                soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
                secDoc.setProperty "SelectionNamespaces", soapNS
                Set secNodes = secDoc.DocumentElement.SelectNodes("//one:Section")
                
                If Not secNodes Is Nothing Then
                    ' Get the first section.
                    Dim secNode As MSXML2.IXMLDOMNode
                    Set secNode = secNodes(0)
                    
                    Dim sectionName As String
                    sectionName = secNode.Attributes.getNamedItem("name").Text
                    Dim sectionID As String
                    sectionID = secNode.Attributes.getNamedItem("ID").Text
                    
                    ' Create a new blank Page in the first Section
                    ' using the default format.
                    Dim newPageID As String
                    oneNote.CreateNewPage sectionID, newPageID, npsDefault
                    
                    ' Get the contents of the page.
                    Dim outXML As String
                    oneNote.GetPageContent newPageID, outXML, piAll, xs2013
                    
                    Dim doc As MSXML2.DOMDocument60
                    Set doc = New MSXML2.DOMDocument60
                    ' Load Page's XML into a MSXML2.DOMDocument object.
                    If doc.LoadXML(outXML) Then
                        ' Get Page Node.
                        Dim pageNode As MSXML2.IXMLDOMNode
                        soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
                        doc.setProperty "SelectionNamespaces", soapNS
                        Set pageNode = doc.SelectSingleNode("//one:Page")
    
                        ' Find the Title element.
                        Dim titleNode As MSXML2.IXMLDOMNode
                        Set titleNode = doc.SelectSingleNode("//one:Page/one:Title/one:OE/one:T")
                        
                        ' Get the CDataSection where OneNote store's the Title's text.
                        Dim cdataChild As MSXML2.IXMLDOMNode
                        Set cdataChild = titleNode.SelectSingleNode("text()")
                        
                        ' Change the title in the local XML copy.
                        cdataChild.Text = "A Page Created from VBA"
                        ' Write the update to OneNote.
                        oneNote.UpdatePageContent doc.XML
                        
                        Dim newElement As MSXML2.IXMLDOMElement
                        Dim newNode As MSXML2.IXMLDOMNode
                        
                        ' Create Outline node.
                        Set newElement = doc.createElement("one:Outline")
                        Set newNode = pageNode.appendChild(newElement)
                        ' Create OEChildren.
                        Set newElement = doc.createElement("one:OEChildren")
                        Set newNode = newNode.appendChild(newElement)
                        ' Create OE.
                        Set newElement = doc.createElement("one:OE")
                        Set newNode = newNode.appendChild(newElement)
                        ' Create TE.
                        Set newElement = doc.createElement("one:T")
                        Set newNode = newNode.appendChild(newElement)
                        
                        ' Add the text for the Page's content.
                        Dim cd As MSXML2.IXMLDOMCDATASection
                        Set cd = doc.createCDATASection("Text added to a new OneNote page via VBA.")
    
                        newNode.appendChild cd
                     
                        
                        ' Update OneNote with the new content.
                        oneNote.UpdatePageContent doc.XML
                        
                        ' Print out information about the update.
                        Debug.Print "A new page was created in "
                        Debug.Print "Section " & sectionName & " in"
                        Debug.Print "Notebook " & noteBookName & "."
                        Debug.Print "Contents of new Page:"
                        
                        Debug.Print doc.XML
                    End If
                Else
                    MsgBox "OneNote 2010 Section nodes not found."
                End If
            Else
                MsgBox "OneNote 2010 Section XML Data failed to load."
            End If
        Else
            MsgBox "OneNote 2010 XML Data failed to load."
        End If
        
    End Sub
    
    Private Function GetAttributeValueFromNode(node As MSXML2.IXMLDOMNode, attributeName As String) As String
        If node.Attributes.getNamedItem(attributeName) Is Nothing Then
            GetAttributeValueFromNode = "Not found."
        Else
            GetAttributeValueFromNode = node.Attributes.getNamedItem(attributeName).Text
        End If
    End Function
    
    Private Function GetFirstOneNoteNotebookNodes(oneNote As oneNote.Application) As MSXML2.IXMLDOMNodeList
        ' Get the XML that represents the OneNote notebooks available.
        Dim notebookXml As String
        ' OneNote fills notebookXml with an XML document providing information
        ' about what OneNote notebooks are available.
        ' You want all the data and thus are providing an empty string
        ' for the bstrStartNodeID parameter.
        oneNote.GetHierarchy "", hsNotebooks, notebookXml, xs2013
        
        ' Use the MSXML Library to parse the XML.
        Dim doc As MSXML2.DOMDocument60
        Set doc = New MSXML2.DOMDocument60
        
        If doc.LoadXML(notebookXml) Then
           soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
            doc.setProperty "SelectionNamespaces", soapNS
            Set GetFirstOneNoteNotebookNodes = doc.DocumentElement.SelectNodes("//one:Notebook")
            Debug.Print doc.DocumentElement.XML
            
        Else
            Set GetFirstOneNoteNotebookNodes = Nothing
        End If
    End Function
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, November 10, 2015 8:30 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, your issue might need some VBA code. I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, November 6, 2015 2:50 AM
  • Hi Betsynovice,

    >> Creating a notebook in OneNote from an Excel spreadsheet

    For details about developing notebook from excel, you could refer the link below:
    # Creating a Page in a Notebook in OneNote 2010
    https://msdn.microsoft.com/en-us/library/office/hh377183%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

    For create a new notebook, you could refer the api below:
    # Application interface (OneNote 2013)
    https://msdn.microsoft.com/en-us/library/office/jj680120.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 9, 2015 8:59 AM
  • I thank you both for your responses.  I tried inserting the macro and got an error message.  I'm afraid this is over my head.
    Monday, November 9, 2015 1:56 PM
  • Hi Betsynovice,

    >> I tried inserting the macro and got an error message

    What is your error message? Did you use OneNote 2013 or any other OneNote version? For Note 2013, I suggest you try the code below which create a new page.

    Sub CreateNewPage()
        ' Connect to OneNote 2010.
        ' To see the results of the code,
        ' you'll want to ensure the OneNote 2010 user
        ' interface is visible.
        Dim oneNote As oneNote.Application
        Set oneNote = New oneNote.Application
        
        ' Get all of the Notebook nodes.
        Dim nodes As MSXML2.IXMLDOMNodeList
        Set nodes = GetFirstOneNoteNotebookNodes(oneNote)
        If Not nodes Is Nothing Then
            ' Get the first OneNote Notebook in the XML document.
            Dim node As MSXML2.IXMLDOMNode
            Set node = nodes(0)
            Dim noteBookName As String
            noteBookName = node.Attributes.getNamedItem("name").Text
            
            ' Get the ID for the Notebook so the code can retrieve
            ' the list of sections.
            Dim notebookID As String
            notebookID = node.Attributes.getNamedItem("ID").Text
            
            ' Load the XML for the Sections for the Notebook requested.
            Dim sectionsXml As String
            oneNote.GetHierarchy notebookID, hsSections, sectionsXml, xs2013
           ' Dim a As MSXML2.DOMDocument60
            Dim secDoc As MSXML2.DOMDocument60
            Set secDoc = New MSXML2.DOMDocument60
        
            If secDoc.LoadXML(sectionsXml) Then
                ' select the Section nodes
                Dim secNodes As MSXML2.IXMLDOMNodeList
                Debug.Print secDoc.DocumentElement.XML
                soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
                secDoc.setProperty "SelectionNamespaces", soapNS
                Set secNodes = secDoc.DocumentElement.SelectNodes("//one:Section")
                
                If Not secNodes Is Nothing Then
                    ' Get the first section.
                    Dim secNode As MSXML2.IXMLDOMNode
                    Set secNode = secNodes(0)
                    
                    Dim sectionName As String
                    sectionName = secNode.Attributes.getNamedItem("name").Text
                    Dim sectionID As String
                    sectionID = secNode.Attributes.getNamedItem("ID").Text
                    
                    ' Create a new blank Page in the first Section
                    ' using the default format.
                    Dim newPageID As String
                    oneNote.CreateNewPage sectionID, newPageID, npsDefault
                    
                    ' Get the contents of the page.
                    Dim outXML As String
                    oneNote.GetPageContent newPageID, outXML, piAll, xs2013
                    
                    Dim doc As MSXML2.DOMDocument60
                    Set doc = New MSXML2.DOMDocument60
                    ' Load Page's XML into a MSXML2.DOMDocument object.
                    If doc.LoadXML(outXML) Then
                        ' Get Page Node.
                        Dim pageNode As MSXML2.IXMLDOMNode
                        soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
                        doc.setProperty "SelectionNamespaces", soapNS
                        Set pageNode = doc.SelectSingleNode("//one:Page")
    
                        ' Find the Title element.
                        Dim titleNode As MSXML2.IXMLDOMNode
                        Set titleNode = doc.SelectSingleNode("//one:Page/one:Title/one:OE/one:T")
                        
                        ' Get the CDataSection where OneNote store's the Title's text.
                        Dim cdataChild As MSXML2.IXMLDOMNode
                        Set cdataChild = titleNode.SelectSingleNode("text()")
                        
                        ' Change the title in the local XML copy.
                        cdataChild.Text = "A Page Created from VBA"
                        ' Write the update to OneNote.
                        oneNote.UpdatePageContent doc.XML
                        
                        Dim newElement As MSXML2.IXMLDOMElement
                        Dim newNode As MSXML2.IXMLDOMNode
                        
                        ' Create Outline node.
                        Set newElement = doc.createElement("one:Outline")
                        Set newNode = pageNode.appendChild(newElement)
                        ' Create OEChildren.
                        Set newElement = doc.createElement("one:OEChildren")
                        Set newNode = newNode.appendChild(newElement)
                        ' Create OE.
                        Set newElement = doc.createElement("one:OE")
                        Set newNode = newNode.appendChild(newElement)
                        ' Create TE.
                        Set newElement = doc.createElement("one:T")
                        Set newNode = newNode.appendChild(newElement)
                        
                        ' Add the text for the Page's content.
                        Dim cd As MSXML2.IXMLDOMCDATASection
                        Set cd = doc.createCDATASection("Text added to a new OneNote page via VBA.")
    
                        newNode.appendChild cd
                     
                        
                        ' Update OneNote with the new content.
                        oneNote.UpdatePageContent doc.XML
                        
                        ' Print out information about the update.
                        Debug.Print "A new page was created in "
                        Debug.Print "Section " & sectionName & " in"
                        Debug.Print "Notebook " & noteBookName & "."
                        Debug.Print "Contents of new Page:"
                        
                        Debug.Print doc.XML
                    End If
                Else
                    MsgBox "OneNote 2010 Section nodes not found."
                End If
            Else
                MsgBox "OneNote 2010 Section XML Data failed to load."
            End If
        Else
            MsgBox "OneNote 2010 XML Data failed to load."
        End If
        
    End Sub
    
    Private Function GetAttributeValueFromNode(node As MSXML2.IXMLDOMNode, attributeName As String) As String
        If node.Attributes.getNamedItem(attributeName) Is Nothing Then
            GetAttributeValueFromNode = "Not found."
        Else
            GetAttributeValueFromNode = node.Attributes.getNamedItem(attributeName).Text
        End If
    End Function
    
    Private Function GetFirstOneNoteNotebookNodes(oneNote As oneNote.Application) As MSXML2.IXMLDOMNodeList
        ' Get the XML that represents the OneNote notebooks available.
        Dim notebookXml As String
        ' OneNote fills notebookXml with an XML document providing information
        ' about what OneNote notebooks are available.
        ' You want all the data and thus are providing an empty string
        ' for the bstrStartNodeID parameter.
        oneNote.GetHierarchy "", hsNotebooks, notebookXml, xs2013
        
        ' Use the MSXML Library to parse the XML.
        Dim doc As MSXML2.DOMDocument60
        Set doc = New MSXML2.DOMDocument60
        
        If doc.LoadXML(notebookXml) Then
           soapNS = "xmlns:one='http://schemas.microsoft.com/office/onenote/2013/onenote'"
            doc.setProperty "SelectionNamespaces", soapNS
            Set GetFirstOneNoteNotebookNodes = doc.DocumentElement.SelectNodes("//one:Notebook")
            Debug.Print doc.DocumentElement.XML
            
        Else
            Set GetFirstOneNoteNotebookNodes = Nothing
        End If
    End Function
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, November 10, 2015 8:30 AM
  • Thank you, Edward.  

    I am using my computer with Onenote 2013.  I ran the code and got the error message:

    Compile error:

    User-defined type not defined

    My spreadsheet currently just has the topic names in column A with no heading.  Should the data be laid out differently?

    Tuesday, November 10, 2015 3:06 PM
  • Hi Betsynovice,

    Which line you got this error? In my option, you need to add related references like Microsoft OneNote 15.0 Object library and Microsoft XML, v6.0.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, November 11, 2015 6:34 AM
  • The line that is highlighted when the error comes up is:

    Private Function GetAttributeValueFromNode(node As MSXML2.IXMLDOMNode, attributeName As String) As String

    I don't know what Microsoft OneNote 15.0 Object library and Microsoft XML, v6.0 mean.


    Wednesday, November 11, 2015 1:53 PM
  • Hi Betsynovice,

    With this link Creating a Page in a Notebook in OneNote 2010, you could find the steps to add references. Due to you use OneNote 2013, you need to add Microsoft OneNote 15.0 Object library.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Proposed as answer by Tony---- Friday, November 13, 2015 6:41 AM
    Thursday, November 12, 2015 5:44 AM