Using ActiveX to copy table from excel to powerpoint and keep the format.

I use an ActiveX server to automatically create a powerpoint presentation. Therefore I open an excel file an copy a formatted table to powerpoint. The copy process works but the format is not copied. Any helps or suggestions? I tried PasteSpecial as well but the results are the same.

I kind of need a paste and keep the format function. Similar to ctrl + Alt + v.

Thank in advance.

%% Open excel and copy table

ExcelApp = actxserver('Excel.Application');
ExcelApp.Workbooks.Open("dummy_path_1"); %Import personal macros

excelWb=ExcelApp.Workbooks.Open( "dummy_path_2" ); % Open up the workbook named in the variable fullFileName.
ExcelApp.Visible = true; %Make workbook visible

ExcelApp.Run('PERSONAL.XLSB!ModifyTable'); %Run layout macro
excelWB_sheet_1=excelWb.Sheets.Item(1); %Get first sheet
excelWB_sheet_1.Range('A1:L21').Copy; %Copy table

%% Open powerpoint to paste the table
h = actxserver('PowerPoint.Application'); %Create an ActiveX object
HPresentation = h.Presentation.Open( "dummy_path_3" ); %Open an existing presentation by supplying the fullpath to the file

% select correct layout, see slide_id above
PanelLayout = HPresentation.SlideMaster.CustomLayouts.Item(9); %Number in slide in new slides

% HPresentation.Slides.count + 1 adds new slide to end of ppt
Slide = HPresentation.Slides.AddSlide(HPresentation.Slides.count + 1, PanelLayout);
Slide.Select      

Slide.Shapes.Paste % Paste table but the format is not pasted
%Slide.Shapes.PasteSpecial % Works but same result as Paste()

Answer 1

[Cris LaPierre]

I looked at the Shapes.PasteSpecial docuemntation page. I think you need to tell it what datatype format to use when pasting. Try this:

Slide.Shapes.PasteSpecial(2) % 2 = enhanced Metafile

[Cris LaPierre]

Something does appear to get lost when pasting a range via VBA code. I would prefer to use the PasteSpecial option using HTML Format, as that pastes a table that is editable. Not sure what is going on, but the closest I could get with the sample data I created was to use the ExecuteMso method. This was still a little problematic until I realized I had to add a brief pause to my MATLAB code to give it time to copy (I guess).

Here's the code I used to copy this Excel range to PowerPoint

%% Open excel and copy table
ExcelApp = actxserver('Excel.Application');
excelWb=ExcelApp.Workbooks.Open("full\path\to\my.xlsx"); % Open up the workbook named in the variable fullFileName.
ExcelApp.Visible = true; %Make workbook visible

excelWB_sheet_1=excelWb.Sheets.Item(1); %Get first sheet
excelWB_sheet_1.Range('A1:C4').Copy; %Copy table

%% Open powerpoint to paste the table
h = actxserver('PowerPoint.Application'); %Create an ActiveX object
HPresentation = h.Presentation.Open( "Full\path\to\my\presentation.pptx" ); %Open an existing presentation by supplying the fullpath to the file

% select correct layout, see slide_id above
PanelLayout = HPresentation.SlideMaster.CustomLayouts.Item(2); %Number in slide in new slides

% HPresentation.Slides.count + 1 adds new slide to end of ppt
Slide = HPresentation.Slides.AddSlide(HPresentation.Slides.count + 1, PanelLayout);
Slide.Select   


% Slide.Shapes.PasteSpecial(2) % Paste table as a Metafile
h.CommandBars.ExecuteMso("PasteSourceFormatting")
pause(0.5) % needs to wait a bit in order to paste all formatting

Close(excelWb)
Quit(ExcelApp)
delete(ExcelApp)

And here is the resulting PPT slide

[jr1995]

Hi Cris. Your first answer works perfectly for me. Thanks a lot!