Excel to PowerPoint PasteSpecial and Keep Source Formatting

vba powerpoint paste slides keep source formatting
copy table from excel to powerpoint with formatting vba
paste table from excel to powerpoint keep formatting
copy and paste from excel to powerpoint vba
vba paste keep source formatting
programatically excel to powerpoint
pppastedatatype
copy multiple charts from excel to powerpoint vba

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

Solved: Retain Excel Format when pasting to PowerPoint, As a user, its very simple to do pastespecial - retain source formatting. How can I accomplish this programatically? To be clear, the powerpoint  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.

For that case, I have always been happy using Copy picture in Excel. To get it, click the arrow next to Copy. In VBA, it translates to

Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

In older versions of Excel (2003 and previous) you need to click Shift+Edit to get that option.

Keep source formatting from Excel to Powerpoint, Copy the content from Excel. 2. Open PowerPoint, go to Home tab, click on Paste​>Paste Special. 3. Select Paste link>Microsoft Excel Worksheet  Adds the shape to the collection of shapes in the specified format. If the specified data type is a text data type, a new text box is created with the text. If the paste succeeds, the PasteSpecial method returns a ShapeRange object representing the shape range that was pasted.

Have you tried using

oPPTApp.ActiveWindow.View.PasteSpecial DataType:=ppPasteDefault

VBA to Keep Source Formatting and Link Data when pasting a chart , I would prefer to have it paste keeping the source (Excel) formatting and Data Link. I cannot figure out how to do Set a VBE reference to Microsoft PowerPoint Object Library Dim PPApp As PasteSpecial ' Align pasted chart Paste Special can make the task of pasting text go more smoothly by letting you choose which formatting you want to keep (source or destination) or by stripping all the formatting and just pasting the text. If you are having issues with Paste Special, continue on to the resolutions below: Resolution. To see if Paste Special is enabled:

Try this solution instead of using the Shapes.PasteSpecial method:

https://stackoverflow.com/a/19187572/1467082

PPTApp.CommandBars.ExecuteMso "PasteExcelChartSourceFormatting"

This does not create a link to the Excel document, it embeds a local copy of the document in the PowerPoint Presentation. I think I understand this is your requirement.

Pasting excel data into Powerpoint table without losing formatting , In PowerPoint there's a function "Paste and Keep Source Formatting". It's not under the Paste Special options, you need to find it in the list and add it to you Ribbon:. Using the Paste Options button. Click the down-arrow on the Paste Options button and you’ll see a menu with icons that lets you format copied text in different ways. The options you’ll see depend on where you’re cutting and pasting from and to, e.g., from within or between documents.

This is a code of mine that Keeps Source Formatting:

Sub SigAcc()
Application.ScreenUpdating = False
Dim myPresentation As Object
Set myPresentation = CreateObject("PowerPoint.Application")
Dim PowerPointApp As Object
Dim PPTApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Set objPPApp = New PowerPoint.Application

    Set PPSlide = myPresentation.ActivePresentation.Slides(2)


lastrow = ThisWorkbook.Worksheets("The worksheet you would like to copy").Range("Letter of longest column (E.I. "A")" & Rows.Count).End(xlUp).Row


For p = PPSlide.Shapes.Count To 1 Step -1
    Set myShape = PPSlide.Shapes(p)
    If myShape.Type = msoPicture Then myShape.Delete
    Next



Set myPresentation = myPresentation.ActivePresentation
Set mySlide = myPresentation.Slides(2)
On Error Resume Next


'assigning range into variable
Set r = ThisWorkbook.Worksheets("Sheet to copy").Range("A1:C" & lastrow)
On Error Resume Next

'If we have already opened powerpoint
Set PowerPointApp = GetObject(Class:="PowerPoint.Application")

'If Powerpoint is not opened
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(Class:="Powerpoint.Application")


r.Copy

'to paste range
PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting")
mySlide.Shapes.PasteSpecial
  Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    'Set position:
      myShape.left = ActivePresentation.PageSetup.SlideWidth / 2 - ActivePresentation.PageSetup.SlideWidth / 2
      myShape.Top = 80
PowerPointApp.Visible = True
PowerPointApp.Activate


'to clear the cutcopymode from clipboard
Application.CutCopyMode = False

End Sub

How to Copy Linked Excel Charts into PowerPoint, Easily Copy and Paste Linked Excel Charts onto PowerPoint Slides If you want the chart to keep the formatting from the Excel file, select Keep Source Formatting & Link Data. Below is the Paste Special dialog box in PowerPoint:. 3. Select a cell or range you want to paste the values and formatting; 4. Choose Edit, Paste Special or right click and choose Paste Special, and click Values and source formatting, see screenshot: 5. Then only the values and cell formatting are pasted in the selected cell.

Solved: Macro to Copy & Paste table into Powerpoint using , Re: Macro to Copy & Paste table into Powerpoint using paste special. Hi Grant,. I recently ran into this and stumbled upon pasting while keeping source formatting. In this way, we can definitely keep the source formatting, and you dont need to create a new powerpoint shape to put Excel range. However, if you want to keep the table, you cannot keep the same formatting totally. Obviously, in powerpoint, there is not much functionalities for data as well as Excel.

Inserting Charts into PowerPoint Slides: IU Only: Files: Excel 2016 , The Paste Special command gives more flexibility in that it allows charts to be NOTE for MacOS Users: To keep source formatting and embed the workbook, 

[PDF] Table of all methods of copying data from Excel to PowerPoint , Manually in PowerPoint. No. Yes. Paste (Keep Source Formatting). Paste Special – HTML. Paste (Embed). Paste Special – Excel Worksheet Object. Paste 

Comments
  • which version of Office you run? your code (except some missing lines) looks and works fine. I have OLE Shape with excel range in PP presentation as required. All formatting is kept as in source range...
  • I'm using 2010. Everything works except when it copies over, it copies some borders of the Excel range. I need to copy with source formatting without all the borders of the cell.
  • is it possible when you do so manually, without vba? I'm not sure... I think you need to change cells borders formatting in your source excel file.
  • How would I go about doing this? I tried it on PowerPoint using keep source formatting and it worked.
  • can you upload a screen shots of what you have and what you need?
  • One DoEvents after ExecuteMso seems to be enough for my Office 2013.
  • Hello David, I have been trying hard to resolve it, whenever i use ExecuteMso method, macro is failing with different errors each time, but with pastespecial method macro runs successfully, but it wont give the sourcce format, I am pasting total 40 shapes from 20 excel sheets. please help.
  • @expfresh Please submit a new question with the details of your particular problem, including the code you are attempting to use and description of error(s) and which lines raise the errors. Please submit this as a new question, so that the entire SO community can attempt to help you.
  • It would be a lot more helpful if you would explain what the basic idea is and what the code does instead of 'just' providing it.