Ok so I know there are at least a handful of issues when copying-pasting from Excel to PowerPoint when you have different versions of office suite.
I had a couple of subs that used [vba].PasteSpecial pptPasteDefault[/vba] to get a formatted range of cells into powerpoint, while retaining the editability of the range in powerpoint. Needless to say, everything worked fine when we were all running Office 2003.
Now we are starting to migrate to 2010, and lo and behold pastedefault is broken. I have seen this question asked in a few places, and it seems that to get a range of cells into PowerPoint as an editable table you need to use [vba]Windows(i).Views.PasteSpecial ppPasteDefault[/vba] as opposed to [vba]Slides(i).Shapes.PasteSpecial ppPasteDefault[/vba]
This DOES get you an editable, unlinked table in PowerPoint... but does NOT retain any formatting. As a user, its very simple to do pastespecial - retain source formatting.
How can I accomplish this programatically?
To be clear, the powerpoint "table" is not meant to be linked. It just needs to be a copy of an Excel range which is editable in PowerPoint.
Here is the function I use. What change do I need to make to retain the formatting?
[vba]Private Function CreateTable(RangeAdded As Excel.Range, PptPresentation As PowerPoint.Presentation, _
SlideNumber As Long, ShapeName As String) As PowerPoint.Shape
RangeAdded.Copy
With PptPresentation
.Slides(SlideNumber).Select
.Windows(1).View.PasteSpecial ppPasteDefault
.Slides(SlideNumber).Shapes(.Slides(SlideNumber).Shapes.Count).Name = ShapeName
Set CreateTable = .Slides(SlideNumber).Shapes(ShapeName)
End With
Application.CutCopyMode = False
End Function
[/vba]
Any help is greatly appreciated!