1

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.

Community
  • 1
  • 1
Echo_2
  • 71
  • 1
  • 10
  • Could it be your `FileName2=` assignment? Specifically `Sheet14.Range("U21")`... Are you missing `.Text`? – Porcupine911 Feb 27 '15 at 02:17
  • Hi Porcupine911. Thanks for noticing that i've missed that `.Text`, but unfortunately that was not the solution :( And, strangely enough, that `FileName2` worked without any problems (even without that `.Text` :D) when i initially used MS Word instead of PPT. I'm not sure if the problem is in `FileName2`, because i changed that line to `FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf"`, but still received both 13 and 429 errors... – Echo_2 Feb 27 '15 at 09:19
  • I've tried to run run this piece of code from PowerPoint `Dim FileName2 As String: FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf"; ActivePresentation.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen` and it works just fine, so i guess there is a problem with Excel-PowerPoint commands execution.. Although no idea how to fix it... – Echo_2 Feb 27 '15 at 10:33

1 Answers1

1

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
Porcupine911
  • 928
  • 9
  • 22
  • Hi Porcupine911. Thank You, it worked indeed! Such a simple, yet effective, solution! Makes me wonder why most of the sources suggest using `ActivePresentation.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF`. Thanks you for spendin your time on reproducing my macro and finding the solution! You saved my day! :) – Echo_2 Feb 27 '15 at 15:51
  • You're welcome! I just do this for fun to help others. I was disappointed I couldn't figure out why the `ExportAsFixedFormat` wouldn't work but I'm glad this workaround did the trick! – Porcupine911 Feb 27 '15 at 16:09
  • For an answer that actually addresses the issue with ExportAsFixedFormat, refer to here: https://stackoverflow.com/questions/63817805 – jramm Aug 04 '21 at 20:25