Excel到PowerPoint PasteSpecial并保持源格式 [英] Excel to PowerPoint PasteSpecial and Keep Source Formatting

问题描述

我正在尝试将Excel文档中的范围复制并粘贴到PowerPoint幻灯片中.

I'm trying to copy and paste a range from an Excel document into a PowerPoint slide.

它将范围复制为图像，而不是保留源格式.

It is copying the range as an image rather than keeping source formatting.

oPPTApp As PowerPoint.Application
Dim oPPTFile As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oPPTSlide As PowerPoint.Slide
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
On Error GoTo 0

Windows("File1.xlsx").Activate
Sheets("Sheet1").Select
Range("B3:N9").Select
Selection.Copy
oPPTApp.ActiveWindow.View.GotoSlide (2)
oPPTApp.ActiveWindow.Panes(2).Activate
oPPTApp.ActiveWindow.View.PasteSpecial DataType:=ppPasteOLEObject
oPPTApp.ActiveWindow.Selection.ShapeRange.Left = 35
oPPTApp.ActiveWindow.Selection.ShapeRange.Top = 150

推荐答案

让我们将这个问题分为几个不同的部分:

Let’s break this problem into a few different parts:

• 创建PowerPoint应用程序
• 复制图表并粘贴
• 以正确的格式绘制图表.

现在查看您的代码，您可以很好地进行前两个操作.它会粘贴导致问题的对象.让我们探索粘贴的不同方法.

Now looking at your code, you are pretty much good to go on the first two. It’s pasting the object that is causing the problem. Let’s explore the different ways to paste.

使用执行方法:

使用此方法时，就好像我们右键单击幻灯片并将对象粘贴到幻灯片上一样.现在，尽管此方法是完全有效的粘贴方法，但在VBA中实现此方法可能会有些挑战.之所以如此，是因为它极易波动，我们必须放慢脚本的步伐，以蜗牛的脚步！

When we use this method it’s like we are right-clicking on the slide and pasting the object on to the slide. Now while this method is a completely valid way to paste, achieving this in VBA can be a little challenging. The reason why is because it is extremely volatile, and we must slow down our script to a snail’s pace!

要实现此方法及其任何不同的选项，请执行以下操作:

To implement this method along with any of its different options, do the following:

'Create a new slide in the Presentation, set the layout to blank, and paste range on to the newly added slide.
 Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutBlank)

   'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO SELECT THE SLIDE
    For i = 1 To 5000: DoEvents: Next
    PPTSlide.Select

   'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO PASTE THE OBJECT
    For i = 1 To 5000: DoEvents: Next
    PPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"
    PPTApp.CommandBars.ReleaseFocus

'PASTE USING THE EXCUTEMSO METHOD - VERY VOLATILE

'Paste As Source Formatting
'PPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"

'Paste as Destination Theme
'PPTApp.CommandBars.ExecuteMso "PasteDestinationTheme"

'Paste as Embedded Object
'PPTApp.CommandBars.ExecuteMso "PasteAsEmbedded"

'Paste Excel Table Source Formatting
'PPTApp.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

'Paste Excel Table Destination Theme
'PPTApp.CommandBars.ExecuteMso "PasteExcelTableDestinationTableStyle"

现在，如果您查看我的代码，我必须将其暂停两次，以确保它能正常工作.这是因为否则VBA的移动速度会太快，而发生的一切就是将所有对象粘贴到第一张幻灯片上！ 如果我们只做一次粘贴，通常情况下我们会很安全，而无需添加暂停，但是要转到新幻灯片的那一刻，就要暂停一下！

Now if you look at my code, I had to pause it two different times to make sure it would work. This is because VBA will move way too fast otherwise and all that will happen is it will paste all the objects on the first slide! If we are only doing one paste we are usually safe without putting in the pauses, but the minute you want to go to a new slide put the pauses in!

使用常规粘贴方法:

使用此方法时，就像我们按Crtl + V一样，它将简单地将对象粘贴为PowerPoint中的常规形状.常规形状表示PowerPoint中的默认粘贴类型.这是我们如何实现简单的粘贴方法的方法:

When we use this method, it’s like we are pressing Crtl+V and it will simply paste the object as a regular shape in PowerPoint. The regular shape means the default paste type in PowerPoint. Here is how we can implement a simple paste method:

'PASTE USING PASTE METHOD - NOT AS VOLATILE

'Use Paste method to Paste as Chart Object in PowerPoint
 PPTSlide.Shapes.Paste

使用粘贴特殊方法:

使用此方法时，就像我们在键盘上按 Ctrl + Alt + V 一样，我们得到各种不同的选项如何粘贴它. 范围从图片一直到我们可以链接回到源工作簿的嵌入式对象.

When we use this method it’s like we are pressing Ctrl+Alt+V on the keyboard and we get all sorts of different options of how to paste it. It ranges from a picture all the way to an embedded object that we can link back to the source workbook.

使用粘贴特殊方法，有时我们仍然不得不暂停脚本.之所以像我上面提到的那样，VBA易变. 仅仅是因为我们将其复制并不意味着它将被复制到剪贴板中.这个问题可能会突然出现，然后同时消失，因此，我们最好的选择是在脚本中暂停一下，以使VBA有足够的时间将信息放入剪贴板.长时间停顿，但只有一秒钟或2秒.这是我们如何使用可以使用的不同选项来实现粘贴特殊方法的方法:

With the paste special method, sometimes we will still have to pause our scripts. The reason why is like the reason I mentioned above, VBA is volatile. Just because we copy it doesn’t mean it will make it to our clipboard. This problem can pop up and then disappear at the same time, so our best bet is to have a pause in our script to give VBA enough time to put the information in the clipboard. It usually doesn’t have to be a long pause but only a second or 2. Here is how we implement the paste special method with the different options we can use:

'PASTE USING PASTESPECIAL METHOD - NOT AS VOLATILE

'Paste as Bitmap
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteBitmap

'Paste as Default
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteDefault

'Paste as EnhancedMetafile
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile

'Paste as HTML - DOES NOT WORK WITH CHARTS
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteHTML

'Paste as GIF
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteGIF

'Paste as JPG
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteJPG

'Paste as MetafilePicture
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteMetafilePicture

'Paste as PNG
 PPTSlide.Shapes.PasteSpecial DataType:=ppPastePNG

'Paste as Shape
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteShape

'Paste as Shape, display it as an icon, change the icon label, and make it a linked icon.
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteShape, DisplayAsIcon:=True, IconLabel:="Link to my Chart", Link:=msoTrue

'Paste as OLEObject and it is linked.
 PPTSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse

话虽如此，如果大多数情况下将对象粘贴为带有链接的OLEObject，则格式化会随之结束.除非您有一个仅在Excel中存在的特殊主题，否则您将遇到麻烦.当我从Excel到Word绘制图表时遇到了这个问题，但是Excel图表具有自定义主题.

With all that being said, if you paste an object as an OLEObject with a link most of the time the formatting comes over with it. Unless you have a special theme that only exist in Excel, that’s where you get into trouble. I ran into this problem when I was taking a chart from Excel To Word, but the Excel chart had a custom theme.

这是您的代码，将其重写，以便它将使用源格式粘贴对象并设置其尺寸.我希望您不要介意我重新调整一些代码以使其成为现实更加简洁.

Here is your code, rewritten so that it will paste an object using the source format and setting the dimensions of it. I hope you don't mind me readjusting some of your code to make it a little more concise.

Sub PasteRangeIntoPowerPoint()

'Declare your variables
Dim oPPTApp As PowerPoint.Application
Dim oPPTFile As PowerPoint.Presentation
Dim oPPTShape As PowerPoint.Shape
Dim oPPTSlide As PowerPoint.Slide
Dim Rng As Range

'Get the PowerPoint Application, I am assuming it's already open.
Set oPPTApp = GetObject(, "PowerPoint.Application")

'Set a reference to the range you want to copy, and then copy it.
Set Rng = Worksheets("Sheet1").Range("B3:N9")
    Rng.Copy

'Set a reference to the active presentation.
Set oPPTFile = oPPTApp.ActivePresentation

'Set a reference to the slide you want to paste it on.
Set oPPTSlide = oPPTFile.Slides(3)

    'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO SELECT THE SLIDE
    For i = 1 To 5000: DoEvents: Next
    oPPTSlide.Select

    'WARNING THIS METHOD IS VERY VOLATILE, PAUSE THE APPLICATION TO PASTE THE OBJECT
    For i = 1 To 5000: DoEvents: Next
    oPPTApp.CommandBars.ExecuteMso "PasteSourceFormatting"
    oPPTApp.CommandBars.ReleaseFocus
    For i = 1 To 5000: DoEvents: Next

    'Set the dimensions of your shape.
    With oPPTApp.ActiveWindow.Selection.ShapeRange
        .Left = 35
        .Top = 150
    End With

End Sub

这篇关于Excel到PowerPoint PasteSpecial并保持源格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！