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

조회 수: 37 (최근 30일)
jr1995
jr1995 2023년 1월 27일
댓글: jr1995 2023년 1월 30일
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()

답변 (1개)

Cris LaPierre
Cris LaPierre 2023년 1월 27일
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
  댓글 수: 2
Cris LaPierre
Cris LaPierre 2023년 1월 27일
편집: Cris LaPierre 2023년 1월 27일
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
jr1995 2023년 1월 30일
Hi Cris. Your first answer works perfectly for me. Thanks a lot!

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 MATLAB Report Generator에 대해 자세히 알아보기

제품


릴리스

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by