Excel VBA to open Word doc, then populate, then Save it as PDF

[graham726]

Hi - I'm trying to simply save a Word doc as a pdf after having done a but of find and replace (which works) - but I get the error "Application-defined or object-defined error"

Very grateful if anyone knows what this could be.

thanks in advance, here's the code, Graham

-------------------

Sub DespatchPdf()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Set wApp = CreateObject("Word.Application")
wApp.Visible = True


Set wDoc = wApp.Documents.Open("C:\Users\graham\Dropbox\Dropbox Backed Up Files\4. Southcourt\6. Order Processing\despatch_note_template.docx")


With wDoc.Content.Find
.Text = "Date:"
.Replacement.Text = "Date: " & orderdate
.Execute Replace:=wdReplaceAll

.Text = "Customer Reference:"
.Replacement.Text = "Customer Reference: L9/" & custid
.Execute Replace:=wdReplaceAll

.Text = "Contact Telephone:"
.Replacement.Text = "Contact Telephone: " & custtel
.Execute Replace:=wdReplaceAll

.Text = "Delivery To:"
.Replacement.Text = "Delivery To: " & Chr(11) & Chr(11) & custtitle & " " & custfname & " " & custsurname & Chr(11) & custaddress1 & Chr(11) & custaddress2 & Chr(11) & custaddress3 & Chr(11) & custaddress4
.Execute Replace:=wdReplaceAll


End With


wApp.ActiveDocument.SaveAs "C:\MyDoc.pdf", 17


End Sub

[Kenneth Hobs]

Welcome to the forum! Please paste code between code tags. Click the # icon on a message's toolbar to insert them.

Looks like you did not set the reference to the Word object. Set it in Tools > References > Microsoft Word 14.0 Object Library.

[graham726]

Hi Kenneth - Many thanks for your reply - I've ticked Tools > References > Microsoft Word 16.0 Object Library, does it matter that it's 16 and not 14?

[Kenneth Hobs]

No. This is called early binding. It is what allows Intellisense to work with that object, if it has defined methods and properties and constants. This obviously makes your constants for the object visible.

Some like to code using early binding and then change to late binding. There are advantages and disadvantages to both types. I like early binding but you are limited to the version of the object.

[graham726]

Kenneth - it's still not working, "Run Time Error 4168" Application-defined or object-defined error"

Any thoughts?

Graham

[Kenneth Hobs]

I can not duplicate that error.

Why would you run it? You should always Compile code before running it. I guess you put the code in a Module.

When you compile, you can see the reference error or not if you added it. You will also see that you did not define variables that you concatenated. I most always recommend Option Explicit as first line of code. You can set that Option to be added automatically in VBE.

I do not recommend saving files to c:\.

After saving the file, you should close the document and Set the objects to Nothing.

Use Debug's F8 to execute your code line by line to see which line has the problem.

Option Explicit

Sub DespatchPdf()
  Dim wApp As Word.Application, wDoc As Word.Document
  
  Set wApp = CreateObject("Word.Application")
  wApp.Visible = True
    
  Set wDoc = wApp.Documents.Open("C:\Users\graham\Dropbox\Dropbox Backed Up Files\4. Southcourt\6. Order Processing\despatch_note_template.docx")
    
  With wDoc.Content.Find
    .Text = "Date:"
    .Replacement.Text = "Date: " & orderdate
    .Execute Replace:=wdReplaceAll
    
    .Text = "Customer Reference:"
    .Replacement.Text = "Customer Reference: L9/" & custid
    .Execute Replace:=wdReplaceAll
    
    .Text = "Contact Telephone:"
    .Replacement.Text = "Contact Telephone: " & custtel
    .Execute Replace:=wdReplaceAll
    
    .Text = "Delivery To:"
    .Replacement.Text = "Delivery To: " & Chr(11) & Chr(11) & custtitle & " " & custfname & " " & custsurname & Chr(11) & custaddress1 & Chr(11) & custaddress2 & Chr(11) & custaddress3 & Chr(11) & custaddress4
    .Execute Replace:=wdReplaceAll
  End With
  
  wApp.ActiveDocument.SaveAs "C:\MyDoc.pdf", 17
End Sub

Sub Test_SearchReplaceInDoc()  
  SearchReplaceInDoc "x:\MSWord\SearchReplace\SearchReplaceInDoc.doc", "***XX", "123", True, False
End Sub


'http://www.vbaexpress.com/forum/showthread.php?t=38958
Sub SearchReplaceInDoc(doc As String, findString As String, replaceString As String, _
  Optional docVisible As Boolean = True, _
  Optional closeDoc As Boolean = True)
  
  Dim wdApp As Object, wd As Object
  
  If Dir(doc) = "" Then Exit Sub

  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application")
  On Error GoTo 0
   
  Set wd = wdApp.Documents.Open(doc)
  wdApp.Visible = docVisible
  With wd
    '.FormFields("Brand").Result = Cells(rn, "B")
  End With
  With wd.Content.Find
    .Text = findString                  '"***XX"
    .Replacement.Text = replaceString   '"123"
    .Forward = True
    .Wrap = 1
    .Execute Replace:=2
  End With
  
 If closeDoc Then
  Set wd = Nothing
  Set wdApp = Nothing
 End If
End Sub