VBA Save PPT as PDF through Excel

[mkmiller]

I have a macro that writes specific charts and ranges to a powerpoint shell. I would like to then have the macro save the powerpoint as a pdf with the file name specified in the excel file and close it. I have pasted my existing macro below. Can you please help??



Private Sub CommandButton2_Click()
Dim ppApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim rowCnt As Integer
Dim Startingrow As Integer
Dim Endingrow As Integer
Dim SlideNumber As Long
Dim pptfilepath As String
Dim Top As Integer
Dim Left As Integer
Dim Width As Integer
Dim Height As Integer
Dim DataType As String

pptfilepath = Cells(5, 3).Value
Startingrow = Cells(2, 3).Value
Endingrow = Cells(3, 3).Value

Set ppApp = CreateObject("PowerPoint.Application")
ppApp.Visible = True
Set PPPres = ppApp.Presentations.Open(pptfilepath)

' Reference existing instance of PowerPoint
Set ppApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = ppApp.ActivePresentation
ppApp.ActiveWindow.ViewType = ppViewSlide

For rowCnt = Startingrow To Endingrow
' set parameters of first chart
DataType = Cells(rowCnt, 3).Value
chtname = Cells(rowCnt, 4).Value
Worksheetname = Cells(rowCnt, 5).Value
SlideNumber = Cells(rowCnt, 6).Value
Top = Cells(rowCnt, 7).Value
Left = Cells(rowCnt, 8).Value
Width = Cells(rowCnt, 9).Value
Height = Cells(rowCnt, 10).Value


' copy chart as a picture
If DataType = "Chart" Then
Worksheets(Worksheetname).ChartObjects(chtname).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
End If

' copy range as a picture
If DataType = "Range" Then
Worksheets(Worksheetname).Range(chtname).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
End If

' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set ppSlide = PPPres.Slides(SlideNumber)
ppApp.ActiveWindow.View.GotoSlide ppSlide.SlideIndex
With ppSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
ppApp.ActiveWindow.Selection.ShapeRange.Left = Left
ppApp.ActiveWindow.Selection.ShapeRange.Top = Top

If Width <> 0 Then
ppApp.ActiveWindow.Selection.ShapeRange.Width = Width
End If

If Height <> 0 Then
ppApp.ActiveWindow.Selection.ShapeRange.Height = Height
End If

End With

Next

' Clean up
Set ppSlide = Nothing
Set PPPres = Nothing
Set ppApp = Nothing



End Sub

 

[rmunsun1]

I have the exact same question if anyone has any insight.