[算表] 透由VBA複製Excel儲存格到PPT的指令(內詳)

作者: foolkids (翼をください)   2017-07-04 15:42:20
軟體:MS Office
版本:2010
一般在Excel直接選擇一特定範圍(例如A1:C12)複製並於PPT貼上時,會產生一個類似表格的物件,
可快速進行簡易編輯
但是透由巨集貼上時,卻會變成一個內嵌的Excel檔案,如果要編輯的時候就會切換成一個類似Excel的編輯器
有辦法透由巨集貼上一個簡易的表格物件嗎?
目前的測試巨集內容如下:
Option Explicit
Sub ExcelRangeToPowerPoint()
'PURPOSE: Copy/Paste An Excel Range Into a New PowerPoint Presentation
'SOURCE: www.TheSpreadsheetGuru.com
Dim rng As Range
Dim PowerPointApp As Object, myPresentation As Object, mySlide As Object, myShape As Object
'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("A1:C12")
'Create an Instance of PowerPoint
On Error Resume Next
'Is PowerPoint already opened?
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
'Clear the error between errors
Err.Clear
'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If
On Error GoTo 0
'Optimize Code
Application.ScreenUpdating = False
'Create a New Presentation
Set myPresentation = PowerPointApp.Presentations.Add
'Add a slide to the Presentation
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
'Copy Excel Range
rng.Copy
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=10 ' 2 = ppPasteEnhancedMetafile
'10 = ppPasteOLEObject
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
'Set position:
myShape.Left = 66
myShape.Top = 152
'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate
'Clear The Clipboard
Application.CutCopyMode = False
End Sub
作者: soyoso (我是耀宗)   2017-07-04 15:59:00
datatype改為0,看是否是原po要的
作者: foolkids (翼をください)   2017-07-04 16:18:00
S大,0會卡住,沒辦法續行耶
作者: soyoso (我是耀宗)   2017-07-04 16:43:00
那將myslide.shapes.pastespecial這行改為powerpointapp.commandbars.executemso ("PasteSourceFormatting")
作者: foolkids (翼をください)   2017-07-04 16:51:00
程序呼叫或引述不正確,好像參數不能這樣加上去,因為括號前面出現了空格測試複製一個slide可行,但是複製Range還是不行
作者: soyoso (我是耀宗)   2017-07-04 17:38:00
https://youtu.be/XcsfWAi0qiE 回文,executemso的測試
作者: foolkids (翼をください)   2017-07-04 18:13:00
… 奇怪,為何我會卡住… 我再試試看,謝謝S大!發現要加Application.Wait那行才行… 總算成功了,可是不是很穩,最穩的方式是停在PowerPointApp那行,手動繼續執行… 這是什麼問題?後來透由Shapes.count進行卡控迴圈,終於解決了…結果有了新的問題... 這樣複製過來的Shape無法使用Shape.ScaleWidth或是Shape.ScaleHeight的方法,有解嗎?
作者: soyoso (我是耀宗)   2017-07-05 07:10:00
以shape.width和height來調整試試
作者: foolkids (翼をください)   2017-07-05 07:58:00
好喔,應該可行,只是又要大改,哈哈欸… Shape.width = Shape.wIdth * ScaleRate 就好了…

Links booklink

Contact Us: admin [ a t ] ucptt.com