Hi shashacool,
This requires a Code stage to be implemented which is explained as below.
Below is a sample working code that copies data from a specified Excel range and works well under general conditions. You might need to make amendments as per your requirement. For example, it assumes that Sheet1 exists so you would need to add code to check the sheet, add Exception Handling, remove hard coded path of PPT, adjust the top and left of the slide content, etc. Also you need to make modifications if you need it in an existing ppt rather than a new one and insert the data on a specified slide number.
You need to create a new action page in the MS Excel VBO. This can be a duplicate of any other existing page after making the necessary additions/deletions to it. So choose a page that closely resembles the diagram explained below. Lets call the new page as 'Copy Range From Excel2PPT' and it looks like 1.jpg in the screenshots.
- 2.jpg: has the Input parameters to the Start stage of the Object
- 3.jpg: has the Input parameters to the Code stage
- 4.jpg: has the Output parameters to the Code stage
The content of the Code stage is as below. Paste the below in the Code tab of the Code stage.
Code:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object
Try
'Create PowerPoint object
PowerPointApp = CreateObject("PowerPoint.Application")
'Create a New Presentation
myPresentation = PowerPointApp.Presentations.Add
'Add a slide to the Presentation
mySlide = myPresentation.Slides.Add(1, 12) '12 = ppLayoutBlank
'The different slide layout constants available can be found on the below Microsoft link
'https://msdn.microsoft.com/en-us/vba/powerpoint-vba/articles/ppslidelayout-enumeration-powerpoint
'Can be turned to False if you do not want to see the PPT while running the process
PowerPointApp.Visible = True
'Excel related stuff
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate() 'Whichever sheet name was passed as a parameter to this code stage that becomes the active sheet
excel = ss.Application
sheet = excel.ActiveSheet
'Copy the required Excel Range that was passed as a parameter
sheet.Range(cellRef).copy
'Paste to PowerPoint
mySlide.Shapes.PasteSpecial (DataType:=2) '2 = ppPasteEnhancedMetafile
'DataType Numeric Constants
'1:=ppPasteBitmap
'0:=ppPasteDefault
'2:=ppPasteEnhancedMetafile
'8:=ppPasteHTML
'4:=ppPasteGIF
'5:=ppPasteJPG
'3:=ppPasteMetafilePicture
'6:=ppPastePNG
'11:=ppPasteShape
myShape = mySlide.Shapes(mySlide.Shapes.Count)
'Set position:
myShape.Left = 50
myShape.Top = 50
'Activate Powerpoint
PowerPointApp.Activate
'Removes the moving bordered area of excel that was as a result of the Copy
excel.CutCopyMode = False
'Save and close the PPT.
With myPresentation
.SaveCopyAs ("C:\MySavedPPT") 'This code can be modified for passing the Path as a parameter to this code stage
.Close
End With
'Parameters for .SaveCopyAs can be found here
'https://msdn.microsoft.com/en-us/vba/powerpoint-vba/articles/presentation-savecopyas-method-powerpoint
'Quit Powerpoint.
PowerPointApp.Quit
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
myPresentation = Nothing
PowerPointApp = Nothing
End Try
- 5.jpg: is how the call to this new action looks like from the Process
- 6.jpg: is the Input Excel sheet containing data and a chart
- 7.jpg: is the Output after running the process
You need to call the same action for both of your requirements (Excel sheet with data & sheet with data and graph) by simply specifying the range in the parameters.
.
