SydneyBuoy
New Member
- Joined
- Sep 9, 2019
- Messages
- 4
I generate a Word Document populating the bookmarks within it from excel and then tried exporting to PDF. Constantly getting error even after adding Microsoft Word Library 16.0. What am I doing wrong here? The code points to wdExportFormatPDF as Variable not Defined.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Option Explicit
Sub GenerateTerminationLetter()
Dim objWord As Object, docWord As Object
Dim wb As Workbook
Dim xlName As Name
Dim Path, SavePath, TempPath, FileName3 As String
Dim EmpFileName As String
Set wb = ThisWorkbook
' ******************************* Primary Letter Template Location ***********************
Sheets("FilePath").Select
TempPath = Sheets("FilePath").Range("C16").Value
If Right(TempPath, 1) <> "" Then
TempPath = TempPath & ""
Else
End If
Path = TempPath & "Termination Letter (Redundancy A023 FPP) (NEW - With Whistle Blowing Statement).docx"
'*******************************Populate Bookmarks ***************************************
On Error GoTo ErrorHandler
'Create a new Word Session
Set objWord = CreateObject("Word.Application")
'Open document in word
Set docWord = objWord.Documents.Add(Path)
'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name exists in the document then put the value at the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName
Sheets("Temp").Visible = xlVeryHidden
'******************************* Activate word and display document **********************
With objWord
.Visible = True
.Activate
End With
'Save Termination Letter
FileName3 = Sheets("R-Copy").Range("D7").Value
'******************************* Export as PDF ********************************************
docWord.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=EmpFolder & "" & "Termination Letter_" & FileName3, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
ExportFormat:=wdExportFormatPDF
objWord.Quit
'Release the Word object to save memory and exit macro
ErrorExit:
Set objWord = Nothing
Exit Sub
'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem. Contact Administrator"
If Not objWord Is Nothing Then objWord.Quit False
Resume ErrorExit
End If
End Sub</code>
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Option Explicit
Sub GenerateTerminationLetter()
Dim objWord As Object, docWord As Object
Dim wb As Workbook
Dim xlName As Name
Dim Path, SavePath, TempPath, FileName3 As String
Dim EmpFileName As String
Set wb = ThisWorkbook
' ******************************* Primary Letter Template Location ***********************
Sheets("FilePath").Select
TempPath = Sheets("FilePath").Range("C16").Value
If Right(TempPath, 1) <> "" Then
TempPath = TempPath & ""
Else
End If
Path = TempPath & "Termination Letter (Redundancy A023 FPP) (NEW - With Whistle Blowing Statement).docx"
'*******************************Populate Bookmarks ***************************************
On Error GoTo ErrorHandler
'Create a new Word Session
Set objWord = CreateObject("Word.Application")
'Open document in word
Set docWord = objWord.Documents.Add(Path)
'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name exists in the document then put the value at the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName
Sheets("Temp").Visible = xlVeryHidden
'******************************* Activate word and display document **********************
With objWord
.Visible = True
.Activate
End With
'Save Termination Letter
FileName3 = Sheets("R-Copy").Range("D7").Value
'******************************* Export as PDF ********************************************
docWord.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=EmpFolder & "" & "Termination Letter_" & FileName3, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
ExportFormat:=wdExportFormatPDF
objWord.Quit
'Release the Word object to save memory and exit macro
ErrorExit:
Set objWord = Nothing
Exit Sub
'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem. Contact Administrator"
If Not objWord Is Nothing Then objWord.Quit False
Resume ErrorExit
End If
End Sub</code>