Excel macro to save pptx in pdf format; error with code
I know this question has probably been asked x1000 times, but I've tried the last 3 hours for hidden pptx to pdf via excel vba (this is required for my report generator and in order to keep a clean tidy layout declaration I decided to use PowerPoint. because the word constantly messed things up).
Here is 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 getting the error "13 Type mismatch" on the line
WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen
. I tried to replace WDReport with ActivePresentation, but I also got the error "429 ActiveX Component Cant Create Object".
Everything I have included all required libraries (Microsoft PowerPoint Object Library 15.0, same as in MS Word) but doesn't work yet.
To refine the FileName2 string, Ranges are used to retrieve 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)
Thus, the final file name will be "Test Company No. 1234567891011 Report Feb-15.pdf" . Once again, it worked great when I converted .docx to pdf
I would really appreciate if anyone can help me with this issue.
source to share