Importing an Excel range with source formatting

[croydon]

From PowerPoint I want to open an Excel spreadsheet and copy a range into a slide. I can do this as a picture, but my user wants to edit the imported data on the PowerPoint slide. Manually, she copies the range and uses the Paste option 'Keep Source Formatting (K)' in order to do this. I can't see how to do this in PowerPoint VBA.

Below is the section of code I use for copy and pasting.

ActivePresentation.Slides(21).Select

'Open worksheet and adjust the column widths
Set xlsheet = xlwkb.Worksheets("Customer Stats")

'Get section to copy
Set xlrange = xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(16, 3))
xlrange.WrapText = True: xlrange.Font.Name = "Arial": xlrange.Font.Size = 10

'Insert section and place
Set sldSlide = ActivePresentation.Slides(21)
xlrange.CopyPicture Appearance:=xlScreen, Format:=xlPicture
sldSlide.Shapes.PasteSpecial

Application.ActiveWindow.Selection.ShapeRange.Left = 300
Application.ActiveWindow.Selection.ShapeRange.Top = 80
Application.ActiveWindow.Selection.ShapeRange.ScaleHeight 0.8, msoTrue
Application.ActiveWindow.Selection.ShapeRange.ScaleWidth 0.8, msoTrue

Any help would be appreciated.

[John Wilson]

You don#'t say which version.

In later versions you could try


CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting")

[croydon]

Thanks John. I'd tried CommandBars.ExecuteMso ("PasteExcelChartSourceFormatting") but got an Automation Error.
Eventually, I found that the problem was in the Copy.
Instead of:
xlrange.CopyPicture Appearance:=xlScreen, Format:=xlPicture
I should have used:
xlrange.Copy
Application.CommandBars.ExecuteMso ("PasteSourceFormatting")
This then it copies the range across from Excel and it is editable.

[John Wilson]

I don't know why I thought you were pasting a chart!