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

5
我希望在Excel 2007中使用VBA来处理大约500张收据,这些收据已经转换为一个大的OneNote 2010笔记本。每个笔记本选项卡包含不同的收据。我需要获取每个收据的相关细节(收据编号、收据日期、金额、所有行项目数据、税金等),并创建一个包含这些数据的Excel表格。
该数据是半结构化的,意味着一旦我找到“订单号”,我就知道有一个空格字符,然后是订单号。但它可能在不同的行上,甚至被推开等。但这没关系。我可以编写VBA代码,这不是问题...
我想这比手动输入数据更容易,或者比雇佣人手工输入所有数据更便宜... 我不想走OCR路线,因为我需要我认为可以通过Excel和OneNote之间某种形式的Office自动化获得的准确性。我只是找不到使用OneNote 2010的自动化示例(无论是从OneNote方面还是从Excel方面)。能否指点我正确的方向?MSDN有一个Office和OneNote的开发者网站,但我一定是瞎了,没有看到任何示例,甚至没有看到对象模型!

5个回答

5
这个MSDN上的VBA示例代码 (链接:http://code.msdn.microsoft.com/office/onenote-2010-retrieve-data-023e69c0) 可以帮助您。它能够获取所有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

3

2

在学习了很长时间的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模块中
  • 启动SearchStringInOneNote()
  • 填写文本并按下OK
  • 在VBA调试输出窗口查看结果

2

我找到了一个更好的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

这将导致“searchResultsAsXml”包含XML数据,列出了所有包含“searchString”的页面;通过在第5个参数中指定TRUE

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

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


1

虽然不是VBA,但也许有所帮助...

尽管这篇文章很旧,但我在寻找同样的答案时偶然发现了它(以下是我的发现)


OneNote仍然缺乏VBA编辑器,但是可以通过一个叫做Onetastic的插件支持宏(而不是VBA)- 请参见此处: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/ 看起来Onetastic为OneNote添加了许多功能,包括自己的脚本工具 - 请参见此处:https://getonetastic.com/?r=macros

enter image description here

我知道这不是一个精确的答案,但但但...如果在对VBA的要求上有一些灵活性,这可能会有所帮助。
就我所知,当我在笔记本电脑上安装Onetastic时,“一切正常”。但是当我在台式机上安装它时,似乎什么都没发生(尽管安装程序报告安装成功)。
我认为区别在于笔记本电脑有OneNote 365/2016(随Windows 10一起提供),而台式机上既有OneNote 2010(来自Office 2010),也有OneNote 365;我猜测从台式机中删除其中一个版本将使一切正常...

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接