none
Convert Word document to PDF via Excel VBA RRS feed

  • Question

  • I have seen several references to this particular activity but don't seem to get the right result.

    I am taking data from excel to open and update a word document and then wish to save as a PDF.  

    I can save the document as a DOCX but when it tries to run the code for PDF, it craps out.

    The error message I get is  "Invalid procedure call or argument"

    The Folder variable that stores the file in a given folder and works fine for the word document so don't believe that has anything to do with the issue.

    Function Receipting(Cnt)
        Dim objWord As Object
        
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = True
        objWord.Documents.Open "C:\Users\kindyd1\Desktop\COPE\Receipt Letter.docx"
    
        objWord.Activate
    
        With objWord.ActiveDocument.Content.Find
            .Text = "<<DateToday>>"
            .Replacement.Text = Format(Date, "yyyy/mm/dd")
            .Execute Replace:=2
        End With
    
        With objWord.ActiveDocument.Content.Find
            .Text = "<<Address>>"
            .Replacement.Text = Contributor(Cnt, 2) & " " & Contributor(Cnt, 3) & vbCr & Contributor(Cnt, 5) & vbCr & Contributor(Cnt, 6) & ", " & Contributor(Cnt, 7) & vbCr & Contributor(Cnt, 8)
            .Execute Replace:=2
        End With
        
        With objWord.ActiveDocument.Content.Find
            .Text = "<<Receipt#>>"
            .Replacement.Text = Contributor(Cnt, 13)
            .Execute Replace:=2
        End With
        
        With objWord.ActiveDocument.Content.Find
            .Text = "<<Donatation>>"
            .Replacement.Text = Format(Contributor(Cnt, 12), "Currency")
            .Execute Replace:=2
        End With
        
        With objWord.ActiveDocument.Content.Find
            .Text = "<<DonationDate>>"
            .Replacement.Text = Contributor(Cnt, 1)
            .Execute Replace:=2
        End With
    
        With objWord.ActiveDocument.Content.Find
            .Text = "<<Name>>"
            .Replacement.Text = Contributor(Cnt, 2) & " " & Contributor(Cnt, 3)
            .Execute Replace:=2
        End With
    
        With objWord.ActiveDocument.Content.Find
            .Text = "<<EmailAddress>>"
            .Replacement.Text = Contributor(Cnt, 4)
            .Execute Replace:=2
        End With
        
        Receipting = Folder & "\" & Contributor(Cnt, 13) & " " & Contributor(Cnt, 2) & " " & Contributor(Cnt, 3) & ".docx"
        objWord.ActiveDocument.SaveAs (Folder & "\" & Contributor(Cnt, 13) & " " & Contributor(Cnt, 2) & " " & Contributor(Cnt, 3) & ".docx")
        
        objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=Folder & "\" & Contributor(Cnt, 13) & " " & Contributor(Cnt, 2) & " " & Contributor(Cnt, 3) & ".pdf", _
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
            
        objWord.Documents.Close
        Set objWord = Nothing
    
        
            
    End Function

    Saturday, June 15, 2019 2:06 AM

All replies

  • It appears you are using Word specific commands in the PDF section while using late binding to Word. You should use instead the numeric equivalents of such commands e.g. assuming the rest works

    objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=Receipting & ".pdf", _
                                 ExportFormat:=17, _
                                 OpenAfterExport:=False, _
                                 OptimizeFor:=0, _
                                 Range:=0, _
                                 From:=1, To:=1, _
                                 Item:=0, _
                                 IncludeDocProps:=True, _
                                 KeepIRM:=True, _
                                 CreateBookmarks:=0, _
                                 DocStructureTags:=True, _
                                 BitmapMissingFonts:=True, _
                                 UseISO19005_1:=False


    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, June 15, 2019 7:52 AM
  • Thank you.  That did work but I am now getting a file is in use by another application....sometimes.   It will run once or maybe 3 times and get the error message.   I can then give it a moment and hit continue and it might keep working.  Thoughts?
    Saturday, June 15, 2019 1:02 PM
  • Almost certainly you haven't allowed enough time for the document to close. Rather than create a new Word instance each time, first check if Word is open and use the open application.

    Graham Mayor - Word MVP
    www.gmayor.com

    Sunday, June 16, 2019 10:41 AM