用VBA将Excel图片粘贴到OneNote中

Question

我希望通过Excel使用VBA从电子表格中复制单元格，并将其粘贴到OneNote上作为图片。这个是可能的吗？

下面是我一直在编写的代码(不包括excel单元格复制部分)我几乎没有使用XML的经验，也不确定到底需要用XML编码什么才能将图像从excel粘贴到OneNote页面的正文中：

Sub PasteToOneNote()

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

Dim OpenedFile As String

'Open OneNote file and creates an output ID to call on the file (OpenedFile)
oneNote.OpenHierarchy bstrPath:="File Path", _
                  bstrRelativeToObjectID:="", _
                  pbstrObjectID:=OpenedFile

'Use the output ID from OpenHierarchy to open OneNote file
oneNote.NavigateTo bstrHierarchyObjectID:=OpenedFile, _
                    bstrObjectID:="", _
                    fNewWindow:=False

Dim XMLSectionFile As String

'OneNote's GetHierarchy method allows you to get meta-data and data about the OneNote Notebooks.
'Takes data and puts into XML output
oneNote.GetHierarchy bstrStartNodeId:=OpenedFile, _
                     hsScope:=hsPages, _
                     pbstrHierarchyXMLOut:=XMLSectionFile

'Use the MSXML Library to parse the XML

Dim secDoc As MSXML2.DOMDocument
Set secDoc = New MSXML2.DOMDocument

If secDoc.LoadXML(XMLSectionFile) Then

'Select all section nodes
Dim pageNodes As MSXML2.IXMLDOMNodeList
Set pageNodes = secDoc.DocumentElement.SelectNodes("//one:Page")

'Loop through each page to find the page that has the same title as "ABC" then paste picture

Dim node As MSXML2.IXMLDOMNode
Dim pageName As String
Dim pageID As String

For Each node In pageNodes
    pageName = node.Attributes.getNamedItem("name").Text

    If pageName = "ABC" Then
        'Get pageID and load page's XML output
        pageID = node.Attributes.getNamedItem("ID").Text

        Dim XMLPageFile As String

        oneNote.GetPageContent bstrPageID:=pageID, _
                               pbstrPageXMLout:=XMLPageFile

        ' Load Page's XML into a MSXML2.DOMDocument object.

        Dim pageDoc As MSXML2.DOMDocument
        Set pageDoc = New MSXML2.DOMDocument

        If pageDoc.LoadXML(XMLPageFile) Then
            ' Get Page Node.
            Dim pagenode2 As MSXML2.IXMLDOMNode
            Set pagenode2 = pageDoc.SelectSingleNode("//one:Page")

            Dim newElement As MSXML2.IXMLDOMElement
            Dim newNode As MSXML2.IXMLDOMNode

            ' Create Outline node.
            Set newElement = pageDoc.createElement("one:Outline")
            Set newNode = pagenode2.appendChild(newElement)

            ' Create OEChildren.
            Set newElement = pageDoc.createElement("one:OEChildren")
            Set newNode = newNode.appendChild(newElement)

            ' Create OE.
            Set newElement = pageDoc.createElement("one:OE")
            Set newNode = newNode.appendChild(newElement)

            ' Create TE.
            Set newElement = pageDoc.createElement("one:T")
            Set newNode = newNode.appendChild(newElement)

            ' Add the image for the Page's content.
            ?? 

            oneNote.UpdatePageContent pageDoc.XML


        End If

    End If        

Next

End If

End Sub

Answer 1

我对您的代码进行了试验，并得出了以下结论

它在oneNote中的特定位置插入图像

该图像必须是BASE64编码

你将不得不想出代码来做到这一点

这是一个在线编码器https://www.base64-image.de/

Option Explicit

Sub InsertImageInOneNote()

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

    Dim OpenedFile As String

    'Open OneNote file and creates an output ID to call on the file (OpenedFile)
    onApp.OpenHierarchy bstrPath:="C:\test\test.one", _
                      bstrRelativeToObjectID:="", _
                      pbstrObjectID:=OpenedFile

    'Use the output ID from OpenHierarchy to open OneNote file
    onApp.NavigateTo bstrHierarchyObjectID:=OpenedFile, _
                        bstrObjectID:="", _
                        fNewWindow:=False

    Dim XMLSectionFile As String
    ' OneNote's GetHierarchy method allows you to get meta-data and data about the OneNote Notebooks.
    ' Takes data and puts into XML output

    onApp.GetHierarchy bstrStartNodeId:=OpenedFile, hsScope:=hsPages, pbstrHierarchyXMLOut:=XMLSectionFile

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

    If secDoc.LoadXML(XMLSectionFile) Then

        Dim pageNodes As MSXML2.IXMLDOMNodeList                         ' all page nodes
        Set pageNodes = secDoc.DocumentElement.SelectNodes("//one:Page")

        Dim node As MSXML2.IXMLDOMNode
        Dim pageName As String
        Dim pageID As String

        For Each node In pageNodes                                       ' Loop through each page to find the page that has the same title as "ABC" then paste picture
            pageName = node.Attributes.getNamedItem("name").Text

            If pageName = "ABC" Then
                pageID = node.Attributes.getNamedItem("ID").Text         ' Get pageID and load page's XML output

                Dim XMLPageFile As String
                onApp.GetPageContent bstrPageID:=pageID, pbstrPageXMLout:=XMLPageFile

                Dim pageDoc As MSXML2.DOMDocument
                Set pageDoc = New MSXML2.DOMDocument

                If pageDoc.LoadXML(XMLPageFile) Then

                    Dim newElement As MSXML2.IXMLDOMElement
                    Dim imageNode As MSXML2.IXMLDOMNode
                    Dim pageNode As MSXML2.IXMLDOMNode
                    Dim newNode As MSXML2.IXMLDOMNode
                    Dim newAttribute As MSXML2.IXMLDOMAttribute

                    Set pageNode = pageDoc.SelectSingleNode("//one:Page")

                '   <one:Image format="png">
                '      <one:Position x="36.0" y="158.3999786376953" z="2"/>
                '      <one:Size width="162.7296752929687" height="48.74386596679687"/>
                '      <one:Data>iVBORw0KGg........................2MiiWnAXg84I/9j6SjrduAAAAABJRU5ErkJggg==      BASE64 encoded binary file (image)
                '      </one:Data>
                '   </one:Image>

                '   <one:Image format="png">
                    Set newElement = pageDoc.createElement("one:Image")
                    Set imageNode = pageNode.appendChild(newElement)

                    Set newAttribute = pageDoc.createAttribute("format")
                    newAttribute.Value = "png"
                    imageNode.Attributes.setNamedItem newAttribute

                '   <one:Position x="36.0" y="158.3999786376953" z="2"/>
                    Set newElement = pageDoc.createElement("one:Position")
                    Set newNode = imageNode.appendChild(newElement)

                    Set newAttribute = pageDoc.createAttribute("x")
                    newAttribute.Value = "36.0"
                    newNode.Attributes.setNamedItem newAttribute

                    Set newAttribute = pageDoc.createAttribute("y")
                    newAttribute.Value = "158.3999786376953"
                    newNode.Attributes.setNamedItem newAttribute

                    Set newAttribute = pageDoc.createAttribute("z")
                    newAttribute.Value = "2"
                    newNode.Attributes.setNamedItem newAttribute

                '   <one:Size width="162.7296752929687" height="48.74386596679687"/>
                    Set newElement = pageDoc.createElement("one:Size")
                    Set newNode = imageNode.appendChild(newElement)

                    Set newAttribute = pageDoc.createAttribute("width")
                    newAttribute.Value = "162.7296752929687"
                    newNode.Attributes.setNamedItem newAttribute

                    Set newAttribute = pageDoc.createAttribute("height")
                    newAttribute.Value = "48.74386596679687"
                    newNode.Attributes.setNamedItem newAttribute

                '   <one:Data>iVBORw0KGg........................2MiiWnAXg84I/9j6SjrduAAAAABJRU5ErkJggg==      BASE64 encoded binary file (image)
                    Set newElement = pageDoc.createElement("one:Data")
                    Set newNode = imageNode.appendChild(newElement)

                    Dim pic As String     ' BASE64 encoded binary data (image) ... used this: https://www.base64-image.de/

                    pic = "iVBORw0KGgoAAAANSUhEUgAAANkAAABBCAYAAABciwFYAAAAA3NCSVQICAjb4U/gAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUr"
                    pic = pic & "DhsAAApqSURBVHhe7Z1PTB3VHsd/CpLGB5i4o4DmQoAuSDfEYGKMwuP5fKnWWAwLN00TCXZhajS2Llh2UWps0rhoGkxMN29BSo1V45/HA40xkTRsCAsK6SW1BXYkcok2"
                    pic = pic & "iH/OmTnDnTvn95uZM3MOtw2/TzLpnHu59zKX8z2/v2f60F8CYBjGGQ+rfxmGcQSLjGEcwyJjGMewyBjGMSwyhnEMi4xhHMMiYxjHsMgYxjEsMoZxDIuMYRzDImMYx7DI"
                    pic = pic & "GMYx+RqEix/D1v/+CfUjBfVAhKkzsHHimhoEdMOj338KB9rUMA/i8zefG4MdNQyo+2QZ6gfUgLn/KV2FV2auwIIaBgw+9Tmcb1KDB5jMluyPy6/Chpjgf6oxwzA4GUQ2"
                    pic = pic & "DVutHfDL2ei6wzAMhpnIpPvXOgLbasgwTDIGIhMWTIuvGIZJgrOLDOOYB1tkbW9A451leDxycGaRuZ9gS8YwjmGRMYxjEovR2+92wNaEGqSgdvRbaAyK02mK0WhBOWXB"
                    pic = pic & "OkcxWtb56DLEMai/MwZ1apSPuzD+3Uk4t6mGiu5Dl+CzzhY10llZOgUDi0U1krTB+30XYbhBDaOgBd2E10iIQrDEqBi8fgHab8yogaJ5FG719KKfUfHeRsVo/Pv0CD6P"
                    pic = pic & "QP9Ow/TB+NF3oF+NbFJFS7YC9450eAXtqEhAfN2/PieeO/Ix/KEesYVcNDYS63zXvFrgRuurcI/6m6SmBQYO6qvFQmlVneGslKIfXIQv1u+qc4Stn3WhND4LA5TApCiu"
                    pic = pic & "vwzthMAkkzfE8+JnXlmK+dwkCAHJ9z69rgYGTM8RApMiIQQ2PedfBy0wyQwMy+/j+ikYL6mHLFElkUmBvQC/zqshxfwY/PLutBrkx9Qq+2LPL7RC07PCNkdY/RHoK5uF"
                    pic = pic & "rxENxglzei1iRSQNrYA1vHmTLmp1YlhYPAntc7NqZMIdGJ+jRNwHL5q2TImFYRj9CqTFxq2QvFb8NRRFODdjV2hVEdn2WykEFjAxAltT6jwH0j00E1iAFNqZfAX4hqfh"
                    pic = pic & "pUZ1vssMfE2t5KU7cFOdVkAK8y7cQibF4EF9ZZcuk9mkU6yeNRfa6hXC6gianzF0zWbhNLEwdB96D3WJM1+rJ7QLMYugGVUQ2QLspBWYYvurvJc7Db/lagO7Bvcur6jz"
                    pic = pic & "LLRAOzIJbpZwN2xl/Qdi9b+NiglKP8EX2mRGLIWwBPEuUwJCaFlcPAxsAaCRcdhZmFSjCkQchse2s3Apz7WKRfBiHjc5RKLI6j4M6k+X0USATHSEa1S7SY8URF9bP6Se"
                    pic = pic & "iDLxTT5LUiyijcwyQRL+fHk8Nqo5dh471/+fKz7sP9inzsosrP0kHGcdPR4LKMLyljoNg8ZjT0RcRTFRb1IuonS3PodbR4NjFAbVM1Emb2RZ4WVSIfzeZq7iytIHxnEY"
                    pic = pic & "5Q3IZEr5Ov1j6hCeYaP+PqZULfFRkYVUSEHjQluCP/MsSsVlPbkydBnNQNaMfIoLbf5L+D3P79D0jD5xN39G/oh4PBYwuaa7bFg81n3w6UqRrU/gE7XxOEwdjWYge+G8"
                    pic = pic & "mHzjzWpYgekKH42X5HsbZPFKV+Ft1CLRcZgHtvAIq4dlSwudF3Ghbf4AUxZisyqJ7BgcICxeTSduSawjYr0NIqlSM/LWrtUuW7u8e+B64UVt0iJxGRWPBYjnK4WJxWNt"
                    pic = pic & "8FJTpQuFJkbkRO15DU2OSPp7cItmtMLHZTgTEXEYWV5IKE1gxMSVhc7Xd6+1bO0yfAZCdUQ29G+yBlXT3qnOwog4Lo8VoZBC81L1/lFOsPRDvXIfbbZoFRp0lUbjMjoe"
                    pic = pic & "U0StHxaPaRObsI7NrydMol44mXOF1yyqAZM36Dgs82ZOKTQvVe8f5RjTt95SXJnfm6AqIqvtjPna2zqgVp1aY4AWdZjtE2XBbbTmzCgiYKn8qFXQ4rHGtshrItYPc4ui"
                    pic = pic & "qXsqPkmRfEDLD1RsiNDVQBfcs5IqaYK55whBLdA/7GUUw1RFZA+3Z13bstIPdVRShSQoSFsUG5bKr7BMusUZ7HpPe03Y+mFuoDYJMSEKV7GjXp3GIQTbpU7DUJnRvWDy"
                    pic = pic & "5tUU7irmnicRFKTtiq1qiY+9pu5DPDuajC82G7U6vPsjZJk0iyOFoKf/y0VpLB7TM3crpdvqLMyTaFnhgWDzClxKUUqgYspkfLHZKldUQWTdUJsrgZAVGWd9C48eVkND"
                    pic = pic & "tk/YsWiFhifVWZkgY6jFYyq20tL/QVEajceiqfu8NEOHVkgPCz2OlNYyA1iWVUfGWZfgfeT3T0O2coXOvrFkPgU48KVMaGQRW96CtAKLFVTGUIvHgtiq/olIXOQXpbEk"
                    pic = pic & "CZZowISdnlVYRlL/3Q3GvphdVv+bsvWpBYaflwmNLGKzU5DeZyILCMRmJridJQsiw2IFL1uHxGNBbKXFRX7iQS9a66l7GqJ75L5D1sMwt68I55ZM2rwCsZkJLp3Fjmef"
                    pic = pic & "iixMWHAJolssWtkVoHd/CNGsY/GYOkWEObl2QU/LUzUpzRJKUmYIicyki6whhl8PI0oJqa1ZlLDgEkSn1SXN2V8i8+625afoN0nXzxcd2nkyv2xn6w0y6ScXI0XXiGC0"
                    pic = pic & "GtvqjF5DIrruqQxhmrgGr9tl6KDPQqgeFi4Wl0lhzYItPeKgt+z4okM7XNCuHDP2gcj8+0R64gptIE3qRUQ7Tw53QI06zQXalR8hIhi8XlUJXT8i0tmJloBosrWeXMGp"
                    pic = pic & "vB6Ta5iF00pY4S09SZ0qWLOAjWvNLTI7cYpLiBrZ/Bj8RqblV+D36/r6DcJlsSIyNJVfiSYYwhqVibcuWIOyZwnm6JrT9BzecTHYRbdiuaS/8zjq9urWjBBkbOr/Lkyt"
                    pic = pic & "IQsK5R0YYCCyAtRisUqkQ357ykXNPB91/zmmziqRHR6620hvKI3tVDEk3jJhqW9i4gQ0J+zPojogxMQb0HYD+5YA3YvVeBxO7oWriNHwGpxCrZm+zw5fVGRaHnMb6Vsa"
                    pic = pic & "2MiiWnAXg84I/9j6Sj18PzHwJpnM2Dn7wu7v7h/UhlK6qTkTcZaJSGCg7owieTL0wvmn8InnWYMZ5V55B9EzKKiWFQvArRmSam8aIpMZ3k7v3WuVB31Lg1Mx92FJi5El"
                    pic = pic & "e+RoUlQgsJSBs0sBDnx0JldPZO3omxk7RihiLBPhotDWL2XqvukdYvtKOuTNf2w3zxpDWLOFxYmINWuB4R5MkOnpPjQU7x2kxMiS1fzrSPJEtZWBs428EeonuNuYBLb3"
                    pic = pic & "zQaUS0MmMCjrR6XuEfp7qH1iCZA7kPee1NZMCPIz0nrHk3Q3MRPM3EUxUf9B7Bx+IBgYA2qHN4XcT+ZCYB5onBTXioS3OJkG51Jotwwmn7e/KuZWa3sOkZ3VrZlAWO+4"
                    pic = pic & "nd4Y8nptLijGMZncOUxO1MNn4DFr9yt0RbBXLE5s8r6PfnHa7S2/EdHEWiU8K0mn7mPwJp8QTx/lUpVvSVB1F1FDuIJd2CJBtUEFe8XixObueh/K9T9tMgyTiIXsIsMw"
                    pic = pic & "cbDIGMYxLDKGcQyLjGEcwyJjGMewyBjGMSwyhnEMi4xhHMMiYxjHsMgYxjEsMoZxDIuMYRzDImMYx7DIGMYxLDKGcQyLjGEcwyJjGKcA/A2KokblWzrduAAAAABJRU5E"
                    pic = pic & "rkJggg=="

                    newNode.Text = pic

                    onApp.UpdatePageContent pageDoc.XML

                End If
            End If
        Next
    End If
End Sub