none
Excel 2013 macro to save pptx as pdf; error in code? RRS feed

  • Question

  • Hello MSDN community,

    I know that this question probably was asked x1000 times, but i've been struggling for the past 6 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 im 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").Text & " Report" & Sheet17.Range("E10").Text & ".pdf"
    
    WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen
    
    WDReport.Close
    ppt.Quit
    
    Set ppt = Nothing
    Set WDReport = Nothing 

    But right after PPT file opens and updates links, i receive 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".

    Here's the list of Libraries activated:

    • Visual Basic for Applications
    • Microsoft Excel 15.0 Object Library
    • Microsoft Word 15.0 Object Library
    • Microsoft PowerPoint 15.0 Object Library
    • OLE Automation
    • Microsoft Office 15.0 Object Library

    Additional info:

    1. When initially i was converting .docx to pdf via excel vba, everything worked out fine (well except for the format, ofcourse).

    2. I've tried to change FileName2 to FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf", but without any success.

    3. Ranges used in FileName2 use 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 

    4. I've run this part of macro directly from PowerPoint, and it worked out fine.

    Dim FileName2 As String
    FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf"
    
    ActivePresentation.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen, msoFalse
    So i guess there might be a problem with my Excel-PowerPoint commands execution?

    I'd really appreciate if anyone could help me with this issue. Thanks in advance!



    • Edited by EmilioSat Friday, February 27, 2015 1:36 PM
    Friday, February 27, 2015 11:10 AM

Answers

  • Hi Reshma,

    I didn't have a chance to test out your code, because i was suggested a working solution at http://stackoverflow.com/ by the user Porcupine911, which was simple, yet brilliant. All that was required was to change 

    WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen

    to

    WDReport.SaveAs FileName2, ppSaveAsPDF

    And it did the job.

    Thanks everyone for your time, best of luck to you all!

    • Marked as answer by EmilioSat Friday, February 27, 2015 3:44 PM
    Friday, February 27, 2015 3:44 PM

All replies

  • Hello MSDN community,

    I know that this question probably was asked x1000 times, but i've been struggling for the past 6 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 im 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").Text & " Report" & Sheet17.Range("E10").Text & ".pdf"
    
    WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen
    
    WDReport.Close
    ppt.Quit
    
    Set ppt = Nothing
    Set WDReport = Nothing 

    But right after PPT files opens and updates links, i receive 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".

    Here's the list of Libraries activated:

    • Visual Basic for Applications
    • Microsoft Excel 15.0 Object Library
    • Microsoft Word 15.0 Object Library
    • Microsoft PowerPoint 15.0 Object Library
    • OLE Automation
    • Microsoft Office 15.0 Object Library

    Additional info:

    1. When initially i was converting .docx to pdf via excel vba, everything worked out fine (well except for the format, ofcourse).

    2. I've tried to change FileName2 to FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf", but without any success.

    3. I've run this part of macro directly from PowerPoint, and it worked out fine

    Dim FileName2 As String
    FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf"
    
    ActivePresentation.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen, msoFalse
    So i guess there might be a problem with my Excel-PowerPoint commands execution?

    I'd really appreciate if anyone could help me with this issue. Thanks in advance!


    This undoubtedly has to do with the  FileName2 string, something must be incorrect with concatenation. As a test, in the VBA sub in Excel have the following
    FileName2 = "C:\Users\User1\Documents\Folder\Complete Report\Report.pdf"
    , I bet you the code would work :)
    • Marked as answer by EmilioSat Friday, February 27, 2015 3:41 PM
    • Unmarked as answer by EmilioSat Friday, February 27, 2015 3:41 PM
    Friday, February 27, 2015 12:50 PM
  • Hi John. Thanks for your reply. You probably missed that part in my original message, but i've tried that already, and it didnt work =( Same 13 and 429 error messages...
    Re the FileName2 string (i've updated this part in the original message),

    Ranges used in FileName2 use 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 

     
    • Edited by EmilioSat Friday, February 27, 2015 1:49 PM
    Friday, February 27, 2015 1:15 PM
  • Hi,

    Maybe you can use error handeling in this case, to select a case for error number 13 and 429.

    I have an example on the gallery for "VBA Error Handeling", maybe that will help. See below link.

    https://gallery.technet.microsoft.com/VBA-Error-Handling-17bdb38a

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Friday, February 27, 2015 1:50 PM
  • Hi Reshma. Thanks for your reply. Unfortunately i was not able to understand how to use your Error Handeling file =( Should I just copy your macr to my file and change the Case to 13 and 429? Or is there anything else i need to do?

    Thanks in advance!

    Friday, February 27, 2015 2:08 PM
  • Hi,

    The error handeling shows you what the code should do when there is an error.

    In my example, the link to save the excel sheet is incorrect, the error handeling gives an error code and then activates the error handeling to save it on the desktop.

    I saw that you have a error handeling in your code but it goes to 0 , maybe you can adjust that what the code should do with that error and use my code as an example.

    Let me know if it worked.

    Regards,

    Reshma  


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    Friday, February 27, 2015 2:27 PM
  • Hi Reshma,

    I didn't have a chance to test out your code, because i was suggested a working solution at http://stackoverflow.com/ by the user Porcupine911, which was simple, yet brilliant. All that was required was to change 

    WDReport.ExportAsFixedFormat FileName2, ppFixedFormatTypePDF, ppFixedFormatIntentScreen

    to

    WDReport.SaveAs FileName2, ppSaveAsPDF

    And it did the job.

    Thanks everyone for your time, best of luck to you all!

    • Marked as answer by EmilioSat Friday, February 27, 2015 3:44 PM
    Friday, February 27, 2015 3:44 PM