CODEWITHSUNDEEP
excelpdfpowerpointvba
Echo_2
Echo_2

Reputation: 71

Excel macro to save pptx as pdf; error with code

I know that this question probably was asked x1000 times, but I've been struggling for the past 3 hours to covert pptx to pdf via excel vba (this is required for my report generator, and in order to keep layout clean ad tidy I've decided to use PowerPoint, because word constantly mess things up).

Here's the code I'm using:

Dim ppt As Object
On Error Resume Next

Set ppt = GetObject(, "PowerPoint.Application")
If ppt Is Nothing Then
Set ppt = CreateObject("PowerPoint.Application")
End If
On Error GoTo 0

Set WDReport = ppt.Presentations.Open("C:\Users\User1\Documents\Folder\Final Report Template.pptx")

WDReport.UpdateLinks

Dim FileName2 As String
FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\" & Sheet14.Range("Q3").Text & " No " & Sheet14.Range("U21") & " Report" & Sheet17.Range("E10").Text & ".pdf"

WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen

WDReport.Close
ppt.Quit

Set ppt = Nothing
Set WDReport = Nothing

But I keep receiveing an error message "13 Type Mismatch" on the line WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen. I've tried to replace WDReport with ActivePresentation, but received and error "429 ActiveX Component Cant Create Object".

All I've included all necessary libraries (Microsoft PowerPoint Object Library 15.0, same with MS Word), but no effect so far.

UPD:

Just to clarify the FileName2 string, Ranges are used to get the following variable data:

Range("Q3") is Name (e.g. Test Company)
Range("U21") is Number (e.g. 1234567891011)
Range("E10") is Date (e.g. Feb-15)

So the final file name would be like "Test Company No 1234567891011 Report Feb-15.pdf". Once again, it worked fine when I was converting .docx to pdf

I'd really appreciate if anyone could help me with this issue.

Upvotes: 1

Views: 3953

Answers (1)

Porcupine911
Porcupine911

Reputation: 928

I was able to reproduce your errors. The following solution worked for me. Make sure you have enabled your reference to the Microsoft Powerpoint Object Library.

WDReport.SaveAs FileName2, ppSaveAsPDF

Upvotes: 1

Related Questions