Using ActiveX to copy table from excel to powerpoint and keep the format.
35 次查看(过去 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()
0 个评论
回答(1 个)
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
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
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 MATLAB Report Generator 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!