Runtime error 5 on Document.ExportToFixedFormat

[MrQuestioner]

Hi all, I am trying to build a vba in excel that will read an excel sheet of another opened excel workbook and then do a mail merge with a master document and save the new mail merged document and also exporting it to pdf. It also encrypts the pdf afterwards but that is not so important. I am getting a runtime error at the newDoc.ExportAsFixedFormat call and I am not sure why is that so. I am guessing it is because I am calling a word function which may introduce some parsing issues when being called in an excel macro. Does anyone know the cause of the error? I appreciate any responses.

Sub Contract()
Dim wordfile As String
wordfile = "path\to\MasterDoc.docx"
sheetName = "sheetName"
Dim wdApp As Object, wdDoc As Object

Dim StrMMSrc As String: StrMMSrc = "path\to\other\opened\excel\withMailMergeFields.xlsx"
If Dir(wordfile) = "" Then
MsgBox "Cannot find:" & vbCr & wordfile, vbExclamation
Exit Sub
End If
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
With wdApp
.Visible = True
.WordBasic.DisableAutoMacros
.DisplayAlerts = 0 ' wdAlertsNone
Set wdDoc = .Documents.Open(wordfile)
With wdDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
LinkToSource:=False, _
SQLStatement:="SELECT * FROM `" & sheetName & "$`", SubType:=wdMergeSubTypeAccess
''With .DataSource
' .FirstRecord = wdDefaultFirstRecord
'.LastRecord = wdDefaultLastRecord
For i = 1 To .DataSource.RecordCount
.DataSource.FirstRecord = i
.DataSource.LastRecord = i
.Execute Pause:=False
Set newDoc = wdApp.ActiveDocument


FullName = .DataSource.DataFields("FullName").Value ' Files should be named as full names by default due to similar given names
If FullName Like "*[:*?|<>\/]*" Then ' Check if FullName contains special characters
newFileName = Replace(FullName, "/", "") ' Remove special characters from full name
Else
newFileName = FullName
End If


newDoc.SaveAs2 _
Filename:=.DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
"Doc" & Application.PathSeparator & sheetName & _
Application.PathSeparator & newFileName & ".docx", _
FileFormat:=wdFormatXMLDocument ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data

'newDoc.ExportAsFixedFormat _
'OutputFileName:=.DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
'"Pdf" & Application.PathSeparator & sheetName & _
'Application.PathSeparator & newFileName & ".pdf", _
'ExportFormat:=wdExportFormatPDF ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
newDoc.ExportAsFixedFormat _
OutputFileName:="path\to\pdfFile.pdf", ExportFormat:=wdExportFormatPDF

'''Encryption'''
InputFileName = .DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
"Pdf" & Application.PathSeparator & sheetName & _
Application.PathSeparator & newFileName & ".pdf"
OutputFileName = .DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
"Encrypted" & Application.PathSeparator & sheetName & _
Application.PathSeparator & newFileName & ".pdf"

Password = .DataSource.DataFields("PWD").Value
error = protectObj.ProtectPdfStandard(Password, Password, InputFileName, _
OutputFileName)

newDoc.Close False
Next i
End With
End With
End With
'End With
wdDoc.Close
Set wdDoc = Nothing: Set wdApp = Nothing

End Sub

 

[MrQuestioner]

Here is the code again formatted using the helpers in the text fields.

Sub Contract()
   Dim wordfile As String
   wordfile = "path\to\MasterDoc.docx"
   sheetName = "sheetName"
   Dim wdApp As Object, wdDoc As Object

   Dim StrMMSrc As String: StrMMSrc = "path\to\other\opened\excel\withMailMergeFields.xlsx"
   If Dir(wordfile) = "" Then
     MsgBox "Cannot find:" & vbCr & wordfile, vbExclamation
     Exit Sub
   End If
   On Error Resume Next
   Set wdApp = GetObject(, "Word.Application")
   If wdApp Is Nothing Then
     Set wdApp = CreateObject("Word.Application")
   End If
   On Error GoTo 0
   With wdApp
     .Visible = True
     .WordBasic.DisableAutoMacros
     .DisplayAlerts = 0 ' wdAlertsNone
     Set wdDoc = .Documents.Open(wordfile)
     With wdDoc
       With .MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
         .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
           LinkToSource:=False, _
           SQLStatement:="SELECT * FROM `" & sheetName & "$`", SubType:=wdMergeSubTypeAccess

           For i = 1 To .DataSource.RecordCount
               .DataSource.FirstRecord = i
               .DataSource.LastRecord = i
               .Execute Pause:=False
               Set newDoc = wdApp.ActiveDocument
       
               
               FullName = .DataSource.DataFields("FullName").Value    ' Files should be named as full names by default due to similar given names
               If FullName Like "*[:*?|<>\/]*" Then                                      ' Check if FullName contains special characters
                   newFileName = Replace(FullName, "/", "")                        ' Remove special characters from full name
               Else
                   newFileName = FullName
               End If
               
               
               newDoc.SaveAs2 _
               Filename:=.DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
               "Doc" & Application.PathSeparator & sheetName & _
               Application.PathSeparator & newFileName & ".docx", _
               FileFormat:=wdFormatXMLDocument                                        ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
           
               'newDoc.ExportAsFixedFormat _
               'OutputFileName:=.DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
               '"Pdf" & Application.PathSeparator & sheetName & _
               'Application.PathSeparator & newFileName & ".pdf", _
               'ExportFormat:=wdExportFormatPDF                                      ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
                newDoc.ExportAsFixedFormat _
                OutputFileName:="path\to\pdfFile.pdf", ExportFormat:=wdExportFormatPDF
               
               '''Encryption'''
               InputFileName = .DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
                   "Pdf" & Application.PathSeparator & sheetName & _
                   Application.PathSeparator & newFileName & ".pdf"
               OutputFileName = .DataSource.DataFields("FolderPath").Value & Application.PathSeparator & _
                   "Encrypted" & Application.PathSeparator & sheetName & _
                   Application.PathSeparator & newFileName & ".pdf"
               
               Password = .DataSource.DataFields("PWD").Value
               error = protectObj.ProtectPdfStandard(Password, Password, InputFileName, _
               OutputFileName)
               
               newDoc.Close False
           Next i
         End With
       End With
     End With
   'End With
   wdDoc.Close
   Set wdDoc = Nothing: Set wdApp = Nothing

End Sub

 

[Domenic]

Have you made sure that the specified path is correct?

Since you're using constants from the Word object library, you should have a reference (VBE >> Tools >> References) set to the Word object library. Is this the case?