The MakeWordPDFFile() routine has some unneeded variables. I originally coded it using a late binding method where those Word constant values are needed. Since I later decided to use an early binding method and added the Word object, those were already defined. It doesn't hurt anything to leave them as is.
Replace your button's sub with this one:
Private Sub PrintView_Click() Dim r As Range, fnd As Range, fn As String, fnPDF As String
Set r = Worksheets("invoice").Range("B2", _
Worksheets("invoice").Range("B" & Rows.Count).End(xlUp))
With r
.NumberFormat = "0"
.Value = .Value
Set fnd = .Find(TextBox5.Value, lookat:=xlWhole)
End With
If Not fnd Is Nothing Then
Unload Me
fn = fnd.Offset(, 3).Value
fnPDF = TempPDF(fn)
MakeWordPDFFile fn, fnPDF
ThisWorkbook.FollowHyperlink fnPDF
Else: MsgBox "Number not found!"
End If
UserForm1.Show
End Sub
Be sure to add the reference to the MSWord object as commented. In a module:
Function GetBaseName(Filespec As String) Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
GetBaseName = FSO.GetBaseName(Filespec)
End Function
Function TempPDF(aDrivePathFilenameExtension As String) As String
TempPDF = Environ("temp") & "\" & GetBaseName(aDrivePathFilenameExtension) & ".pdf"
End Function
'Requires Tools > References > Microsoft Word 14.0 Object Library
Sub MakeWordPDFFile(sMSWordFilename As String, sOutputFilename As String)
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim wdExportFormatPDF As Integer, wdExportOptimizeForPrint As Integer
Dim wdExportAllDocument As Integer, wdExportDocumentContent As Integer
Dim wdExportCreateNoBookmarks As Integer
If Not CreateObject("Scripting.FileSystemObject").FileExists(sMSWordFilename) Then _
Exit Sub
On Error GoTo errorHandler
Set wdApp = New Word.Application
With wdApp
Set wdDoc = .Documents.Open(sMSWordFilename)
.Visible = False
End With
If CreateObject("Scripting.FileSystemObject").FileExists(sOutputFilename) Then _
Kill sOutputFilename
wdExportFormatPDF = 17
wdExportOptimizeForPrint = 0
wdExportAllDocument = 0
wdExportDocumentContent = 0
wdExportCreateNoBookmarks = 0
wdDoc.ExportAsFixedFormat _
OutputFileName:=sOutputFilename, 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
wdDoc.Close False
errorExit:
On Error Resume Next
Set wdDoc = Nothing
Set wdApp = Nothing
Exit Sub
errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit
End Sub
Reply With Quote
Originally Posted by Kenneth Hobs
