使用VBA从Excel 2007自动生成OneNote 2010?

我不知道做你想做的事情有什么好的资源,但是下面的两篇文章有一些信息可以帮助你开始工作:

Creating OneNote 2010 Extensions with the OneNote Object Model

What's New for Developers in OneNote 2007 (Part 1 of 2)

为了找到更多信息,我建议谷歌搜索 Microsoft.Office.Interop.OneNote 希望你能从.NET中得到很多这样做的问题,即使这不太理想,至少也能给你一些提示。

This VBA example code 对你有帮助。我检索所有OneNote笔记本的列表。它是为OneNote 2010编写的,适用于我的Office 2010软件包,但我希望它也适用于2007年。

我修改了示例源以检索所有页面和页面内容。页面内容是XML,因此您必须分析它。

修改过的msdn示例:

'Add the following references (adjust to our office version):
'
' - Microsoft OneNote 14.0 Object Library
' - Microsoft XML, v6.0

Sub ListOneNotePages()
    ' Original example is from http://code.msdn.microsoft.com/office/onenote-2010-retrieve-data-023e69c0
    ' License: Apache 2.0
    ' Modified to get all pages & content instead of the notebook list

    ' Connect to OneNote 2010.
    ' OneNote will be started if it's not running.
    Dim oneNote As OneNote14.Application
    Set oneNote = New OneNote14.Application

    ' Get the XML that represents the OneNote pages
    Dim oneNotePagesXml As String

    ' oneNotePagesXml gets filled in with an XML document providing information
    ' about all OneNote pages.
    ' You want all the data. Thus you provide an empty string
    ' for the bstrStartNodeID parameter.
    oneNote.GetHierarchy "", OneNote14.HierarchyScope.hsPages, oneNotePagesXml, xs2010

    ' Use the MSXML Library to parse the XML.
    Dim doc As MSXML2.DOMDocument
    Set doc = New MSXML2.DOMDocument

    If doc.LoadXML(oneNotePagesXml) Then
        ' Find all the Page nodes in the one namespace.
        Dim nodes As MSXML2.IXMLDOMNodeList
        Set nodes = doc.DocumentElement.SelectNodes("//one:Page")

        Dim node As MSXML2.IXMLDOMNode
        Dim pageName As String
        Dim sectionName As String
        Dim pageContent As String
        Dim temp As String
        ' Walk the collection of Pages.
        ' Read attribute values and write them
        ' out to the Immediate window of your VBA host.
        For Each node In nodes
            pageName = node.Attributes.getNamedItem("name").Text
            Debug.Print "Page name: "; vbCrLf & " " & pageName

            Call oneNote.GetPageContent(GetAttributeValueFromNode(node, "ID"), pageContent, piBasic)
            Debug.Print " content: " & pageContent

        Next
    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

我找到了一个 better VBA example ,标题为“在OneNote 2010中以编程方式搜索”:

Sub SearchTermsInTheFirstNoteBook()
    ' Connect to OneNote 2010
    ' OneNote will be started if it's not running.
    Dim oneNote As OneNote14.Application
    Set oneNote = New OneNote14.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 notebook found.
        Dim node As MSXML2.IXMLDOMNode
        Set node = nodes(0)
        ' Get the ID.
        Dim notebookID As String
        notebookID = node.Attributes.getNamedItem("ID").Text

        ' Ask the user for a string for which to search
        ' with a default search string of "Microsoft".
        Dim searchString As String
        searchString = InputBox$("Enter a search string.", "Search", "Microsoft")

        Dim searchResultsAsXml As String
        ' The FindPages method search a OneNote object (in this example, the first
        ' open Notebook). You provide the search string and the results are
        ' provided as an XML document listing the objects where the search
        ' string is found. You can control whether OneNote searches non-indexed data (this
        ' example passes False). You can also choose whether OneNote enables
        ' the User Interface to show the found items (this example passes False).
        ' This example instructs OneNote to return the XML data in the 2010 schema format.
        oneNote.FindPages notebookID, searchString, searchResultsAsXml, False, False, xs2010

        ' Output the returned XML to the Immediate Window.
        ' If no search items are found, the XML contains the
        ' XML hierarchy data for the searched item.
        Debug.Print searchResultsAsXml
    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 OneNote14.Application) As MSXML2.IXMLDOMNodeList
    ' Get the XML that represents the OneNote notebooks available.
    Dim notebookXml As String
    ' Fill notebookXml with an XML document providing information
    ' about available OneNote notebooks.
    ' To get all the data, provide an empty string
    ' for the bstrStartNodeID parameter.
    oneNote.GetHierarchy "", hsNotebooks, notebookXml, xs2010

    ' Use the MSXML Library to parse the XML.
    Dim doc As MSXML2.DOMDocument
    Set doc = New MSXML2.DOMDocument

    If doc.LoadXML(notebookXml) Then
        Set GetFirstOneNoteNotebookNodes = doc.DocumentElement.SelectNodes("//one:Notebook")
    Else
        Set GetFirstOneNoteNotebookNodes = Nothing
    End If
End Function

它会导致“searchresultsasxml”,其中包含列出“searchstring”所在的所有页面的XML数据;购买时将true指定为

oneNote.FindPages notebookID, searchString, searchResultsAsXml, False, False, xs2010

您可以让OneNote突出显示结果。

经过对vba&onenote的长期研究,我得出了以下解决方案:

'Add the following references (adjust to our office version):
'
' - Microsoft OneNote 14.0 Object Library
' - Microsoft XML, v6.0

Sub SearchStringInOneNote()
    ' Original example is from http://code.msdn.microsoft.com/office/onenote-2010-retrieve-data-023e69c0
    ' License: Apache 2.0
    ' Modified to get all pages & content instead of the notebook list

    StringToSearch = InputBox("Text to search:", "Search in OneNote")
    StringToSearch = UCase(StringToSearch) ' Case insensitiveness

    ' Connect to OneNote 2010.
    ' OneNote will be started if it's not running.
    Dim oneNote As OneNote14.Application
    Set oneNote = New OneNote14.Application

    ' Get the XML that represents the OneNote pages
    Dim oneNotePagesXml As String

    ' oneNotePagesXml gets filled in with an XML document providing information
    ' about all OneNote pages.
    ' You want all the data. Thus you provide an empty string
    ' for the bstrStartNodeID parameter.
    oneNote.GetHierarchy "", OneNote14.HierarchyScope.hsPages, oneNotePagesXml, xs2010

    ' Use the MSXML Library to parse the XML.

    Dim doc As MSXML2.DOMDocument
    Dim notebooks As MSXML2.IXMLDOMNodeList
    Dim sections As MSXML2.IXMLDOMElement
    Dim page As MSXML2.IXMLDOMElement

    Set doc = New MSXML2.DOMDocument
    result = doc.LoadXML(oneNotePagesXml)

    Set notebooks = doc.ChildNodes
    Set sections = notebooks(1)
    For Each section In sections.ChildNodes
        Debug.Print "Notebook: "; section.Attributes(1).Text
        Set Pages = section.ChildNodes
        For Each page In Pages
            Debug.Print "    Section: " & page.Attributes(0).Text
            For Each node In page.ChildNodes
                Debug.Print "        Page: " & node.Attributes(1).Text
                Call ProcessNode(node, oneNote, StringToSearch)
            Next
        Next
    Next
End Sub


Sub ProcessNode(ByVal node As MSXML2.IXMLDOMNode, ByVal oneNote As OneNote14.Application, ByVal StringToSearch As String)
        Dim SectionName As String
        Dim PageContent As String
        Dim pageXML As MSXML2.DOMDocument
        Dim TextToSearch As String
        Dim TableNode As MSXML2.IXMLDOMNode
        Dim RowNode As MSXML2.IXMLDOMNode
        Dim Outlines As MSXML2.IXMLDOMNodeList
        Dim Tables As MSXML2.IXMLDOMNodeList

        ' Walk the collection of Pages.
        ' Read attribute values and write them
        ' out to the Immediate window of your VBA host.

           Call oneNote.GetPageContent(GetAttributeValueFromNode(node, "ID"), PageContent, 4) ' Put page content in XML format into string variable

           '---- Put XML page content into XML object:
           Set pageXML = New MSXML2.DOMDocument
           pageXML.LoadXML (PageContent) ' Load page content in XML format into XML object
           pageXML.LoadXML (pageXML.ChildNodes(1).XML) ' Reload same XML object with just significative part of page content (=second node)

           Set Outlines = pageXML.DocumentElement.SelectNodes("//one:Outline") ' Store into XML object the collection of outlines of the page
           OutlineNumber = 0
           TableNumber = 0

           For Each Outline In Outlines
                OutlineNumber = OutlineNumber + 1
                TableNumber = 0
                Set TableNode = Outline.ChildNodes(2).ChildNodes(0).ChildNodes(0)  'Outline.SelectNodes("//one:Table").Context 'Outline.SelectNodes("//one:Table").Item(2)
'Debug.Print "Scanning outline n." & OutlineNumber & "..."
                     If TableNode Is Nothing Then
                         ' If page contains no tables (empty page?)...
                     Else
                         ContaRighe = 0
                         For Each RowNode In TableNode.ChildNodes ' Scan all rows of table
                             ContaRighe = ContaRighe + 1
                             If ContaRighe > 1 Then ' Skip first line (contains columns list)
                                 TestoRiga = "" ' Prepare variable to contain all cells of current row
                                 For x = 0 To RowNode.ChildNodes.Length - 1 ' Store all cells text into a variable
                                     TestoRiga = TestoRiga & Chr(9) & RowNode.ChildNodes(x).Text
                                 Next
                                 If InStr(UCase(TestoRiga), StringToSearch) > 0 Then ' Look for string in row.
                                     Debug.Print "FOUND: " & TestoRiga ' Print row if string found
                                 End If
                             End If
                         Next
                         Set TableNode = Nothing
                     End If ' Table exists
            Next ' Outlines
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

不幸的是速度很慢…但它是有效的!

用途:

• 将整个源复制到空的VBA模块中
• 发射 搜索字符串注释()
• 填写文本并按“确定”
• 看VBA 调试结果输出窗口

不是VBA,只是为了以防万一…

虽然老了,但我偶然发现了这个问题,我在寻找相同的答案

OneNote仍然缺少vba编辑器,但通过名为 onetastic的加载项支持宏(而不是vba),请参阅此处: https://www.microsoft.com/en-us/microsoft-365/blog/2013/08/01/尝试OneTastic加载项,为OneNote带来大量新功能/