(Solved) – Exporting as PDF in VBA

  • by

I would like to create an automated certificate issuing process that allows me to “save as” an existing documents with a new title, and creates a PDF of it.

The problem I’m facing this is in the PDF export routine, I’ve commented below.

Sub GerarCertificado()

Dim Word As Word.Application
Dim DOC As Word.Document
Dim aux As Long

For aux = Range("F8").Value To Range("F11").Value

Set Word = CreateObject("Word.Application")
Word.Visible = True

Set DOC = Word.Documents.Open("C:UsersvitorDownloadsCertificadosDeclaracao.doc")

With DOC

.Application.Selection.Find.Text = "#NOME"
.Application.Selection.Range = Sheets("Gerar Certificado").Cells(aux, 1)

.Application.Selection.Find.Text = "#PALESTRA"
.Application.Selection.Range = Sheets("Gerar Certificado").Cells(aux, 2)

.Application.Selection.Find.Text = "#HORAS"
.Application.Selection.Range = Sheets("Gerar Certificado").Cells(aux, 3)

.Application.Selection.Find.Text = "#DIA"
.Application.Selection.Range = Sheets("Gerar Certificado").Cells(aux, 4)

.Application.Selection.Find.Text = "#DATA"
.Application.Selection.Range = Sheets("Gerar Certificado").Cells(aux, 5)

.SaveAs ("C:UsersvitorDownloadsCertificadosCertificados" & Sheets("Gerar Certificado").Cells(aux, 1) & ".doc")

'Here starts the error
.ExportAsFixedFormat OutputFileName:=_
    "C:UsersvitorDownloadsCertificadosCertificados" & Sheets("Gerar Certificado").Cells(aux, 1) & ".pdf",_
    OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:=_
    wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent,_
    IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:=_
    wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=_
    True, UseISO19005_1:=False


Set DOC = Nothing
Set Word = Nothing

End With

Next aux

MsgBox "Certificados gerados com sucesso!", vbInformation

End Sub

Can anyone help with it or improve it for a better solution? I would really appreciate it. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *