使用 VBA 从 Excel 2007 自动化 Onenote 2010？

Automate Onenote 2010 From Excel 2007, using VBA?

提问by Taptronic

I would like to use VBA in Excel 2007 to step through about 500 receipts, which have been converted to one large OneNote 2010 notebook. Each notebook tab contains a different receipt. I need to get the pertainant details (Receipt #, Receipt Date, Amount, all of the line item data, tax, etc) out of each and I want to create a sheet in Excel with that data.

我想在 Excel 2007 中使用 VBA 来遍历大约 500 份收据，这些收据已转换为一个大型 OneNote 2010 笔记本。每个笔记本标签包含不同的收据。我需要从中获取相关详细信息（收据编号、收据日期、金额、所有行项目数据、税收等），并且我想在 Excel 中使用该数据创建一个工作表。

The data is semi-structured, meaning that once I find "Order Number" I know there is a space character and then the order number follows. But it might be on different lines or even pushed over, etc. But thats OK. I can write the VBA code, that isnt the problem..

数据是半结构化的，这意味着一旦我找到“订单号”，我就知道有一个空格字符，然后是订单号。但它可能在不同的线路上，甚至被推倒等。但这没关系。我可以编写 VBA 代码，这不是问题。

I figured it would be easier than data entry or cheaper than hiring someone to key all this in by hand... I dont want to go the OCR route because I need the accuracy that I think I can get from some sort of Office Automation between Excel and OneNote. I just cant find any example of automation using OneNote 2010 (either from the OneNote side nor the Excel side). Can another point me in the right direction? MSDN has a developer site for Office and OneNote but I must be blind to not see any examples or even an object model!

我认为这比数据输入更容易，或者比雇人手动输入所有这些更便宜......我不想走 OCR 路线，因为我需要我认为我可以从某种办公自动化之间获得的准确性Excel 和 OneNote。我只是找不到任何使用 OneNote 2010 的自动化示例（从 OneNote 端或 Excel 端）。另一个可以指出我正确的方向吗？MSDN 有一个用于 Office 和 OneNote 的开发人员站点，但我一定是瞎了眼才看不到任何示例甚至对象模型！

采纳答案by Hans Olsson

I don't know of any great resource for doing what you want but the following two articles have some info that might help you get started:

我不知道有什么很好的资源可以做你想做的事，但下面两篇文章有一些信息可以帮助你开始：

Creating OneNote 2010 Extensions with the OneNote Object Model

使用 OneNote 对象模型创建 OneNote 2010 扩展

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

OneNote 2007 中面向开发人员的新增功能（第 1 部分，共 2 部分）

To find more information I'd suggest googling for Microsoft.Office.Interop.OneNotewhich should hopefully get you quite a lot of questions of doing it from .Net, which, even it might not be ideal, might at least give you some hints.

要找到更多信息，我建议使用谷歌搜索，Microsoft.Office.Interop.OneNote希望可以让您从 .Net 获得很多问题，即使它可能不理想，也至少可以给您一些提示。

回答by Daniel Hillebrand

This VBA example codeon MSDN might help you. I retrieves a list of all OneNote notebooks. It is written for OneNote 2010 and works for my Office 2010 package, but I hope it works on 2007 also.

MSDN 上的这个 VBA 示例代码可能对您有所帮助。我检索了所有 OneNote 笔记本的列表。它是为 OneNote 2010 编写的，适用于我的 Office 2010 包，但我希望它也适用于 2007。

I have modified the example source to retrieve all pages and the page content. The page content is XML so you would have to parse that.

我修改了示例源以检索所有页面和页面内容。页面内容是 XML，因此您必须对其进行解析。

Modified MSDN Example:

修改后的 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

回答by jumpHyman

I found a better VBA example, titled "Programmatically Searching in OneNote 2010":

我找到了一个更好的 VBA 示例，标题为“在 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

It results in "searchResultsAsXml" containing XML data listing all pages where "searchString " is present; buy specifying TRUE as 5th parameter in

它会生成包含 XML 数据的“searchResultsAsXml”，列出所有存在“searchString”的页面；购买将 TRUE 指定为第 5 个参数

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

you get OneNote to highlight results.

您可以使用 OneNote 来突出显示结果。

回答by jumpHyman

After a long study of VBA & OneNote I arrived at this solution:

经过对 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

Unfortunately it is very slow... but it works!

不幸的是它很慢......但它有效！

Usage:

用法：

• Copy whole source into an empty VBA module
• Launch SearchStringInOneNote()
• Fill in the text and press OK
• Look at VBA debug output window for results

• 将整个源复制到一个空的 VBA 模块中
• 启动 SearchStringInOneNote()
• 填写文本并按确定
• 查看 VBA 调试输出窗口以获取结果

回答by SlowLearner

Not VBA, but just in case it helps...

不是VBA，但以防万一它有帮助......

Old as it is, I stumbled across this question looking for the same answer (and here's what I found):

虽然很旧，但我偶然发现了这个问题，寻找相同的答案（这是我发现的）：

---

---

OneNote still lacks a VBA editor, however there is support for Macros (not VBA) via an addin called Onetastic- see here: https://www.microsoft.com/en-us/microsoft-365/blog/2013/08/01/try-the-onetastic-add-in-to-bring-tons-of-new-features-to-onenote/

OneNote 仍然缺少 VBA 编辑器，但是通过名为Onetastic的插件支持宏（不是 VBA）- 请参见此处：https: //www.microsoft.com/en-us/microsoft-365/blog/2013/08/ 01/try-the-onetastic-add-in-to-带来大量新功能到onenote/

It seems that Onetastic adds a lot of features to OneNote, including it's own scripting tool - see here: https://getonetastic.com/?r=macros

Onetastic 似乎为 OneNote 添加了很多功能，包括它自己的脚本工具 - 请参阅此处：https://getonetastic.com/?r=macros

I know it isn't an exact answer to the question, but but but... if there's some flexibility around the requirement for VBA this will probably help.

我知道这不是问题的确切答案，但是……如果对 VBA 的要求有一定的灵活性，这可能会有所帮助。

---

---

For what it's worth, when I installed Onetastic on my laptop it 'just worked'. When I installed it on my desktop it didn't seem to do anything (even though the installer reported a successful installation).

值得一提的是，当我在笔记本电脑上安装 Onetastic 时，它“正常工作”。当我将它安装在我的桌面上时，它似乎没有做任何事情（即使安装程序报告安装成功）。

I think the difference is that Laptop has OneNote 365/2016 (comes with Windows 10) whereas the desktop has both OneNote 2010 (from office 2010) and also OneNote 365; I suspect deleting one of the versions from the desktop will get everything working...

我认为不同之处在于笔记本电脑有 OneNote 365/2016（Windows 10 附带），而桌面有 OneNote 2010（来自 office 2010）和 OneNote 365；我怀疑从桌面删除其中一个版本会使一切正常...