Pasting a table from excel into powerpoint without losing formatting

Question

Hi all - I'm creating a ppt using a mixture of shapes, charts, and tables from excel. For all the shapes I was using ppPasteMetafilePicture as the datatype. However, this corrupted the formatting of the excel table. The only way I can see of getting this into the ppt is to simply copy and paste. This works and keeps the formatting, but when I put it into vba the paste fails until I manually click into the unused area of the slide. This is the code snippet below. Could someone please help me out?

Kind regards

Carl

---

PPApp.ActiveWindow.Selection.Unselect

'copy incident table to presentation

' ActiveSheet.Range("I28:N38").CopyPicture xlScreen, xlPicture

    Set table1 = ActiveSheet.Range("I28:N38") 

    table1.Copy 

    PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count).Select 

    Set PPSlide = PPApp.ActiveWindow.View.Slide 

' PPSlide.Select

    PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count).Shapes.Paste          <<<<<fails at this point

---

Answer 1

Hi Carl

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

When you use ppPasteMetafilePicture, it corrupts the formatting of the table, and when you try to copy and paste, it fails until you click on the unused area of the slide.

To resolve this issue, you can try copying the table as an embedded object instead of a picture. This should preserve the formatting of the table when pasted into PowerPoint. Here's how you can modify your code to achieve this:

PPApp.ActiveWindow.Selection.Unselect

' Copy incident table to presentation Set table1 = ActiveSheet.Range("I28:N38") table1. Copy

' Create embedded object Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count) Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Add objExcel.Visible = True Set objWorksheet = objWorkbook.Worksheets("Sheet1") objWorksheet.Paste Set objTable = objWorksheet.Shapes.Item(1). OLEFormat.Object objTable.Copy

' Paste embedded object onto PowerPoint slide PPSlide.Select PPApp.ActiveWindow.View.PasteSpecial DataType:=ppPasteOLEObject

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi Anna - thanks for the quick response. I'm now getting the following error:

I'm afraid I'm a bit out of my depth here.

Kind regards

Carl

Answer 3

To resolve this issue, you can try modifying the code to first paste the table into the worksheet, then selecting it as a range and copying it, and finally pasting it as an embedded object in PowerPoint. Here's the modified code:

PPApp.ActiveWindow.Selection.Unselect

' Copy incident table to presentation Set table1 = ActiveSheet.Range("I28:N38") table1. Copy

' Create embedded object Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count) Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Add objExcel.Visible = True Set objWorksheet = objWorkbook.Worksheets("Sheet1") objWorksheet.Range("A1"). PasteSpecial Set objTableRange = objWorksheet.Range("A1", objWorksheet.Cells(table1. Rows.Count, table1. Columns.Count)) objTableRange.Copy

' Paste embedded object onto PowerPoint slide PPSlide.Select PPApp.ActiveWindow.View.PasteSpecial DataType:=ppPasteOLEObject

Answer 4

Thanks again Anna - unfortunately it doesn't like the data type:

Answer 5

Hi Anna - in the end I copied the table as a bitmap image and that seemed to do the trick:

Set table1 = ActiveSheet.Range("I28:N38")

table1.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count).Select

'

    Set PPSlide = PPApp.ActiveWindow.View.Slide 

With PPSlide

    .Shapes.PasteSpecial 

    With .Shapes(.Shapes.Count) 

        .Left = 529 

        .Top = 92 

        .Height = 428 

    End With 

End With

Many thanks