PDA

View Full Version : Excel VBA to open Word doc, then populate, then Save it as PDF



graham726
08-08-2016, 12:58 PM
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
08-08-2016, 01:18 PM
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
08-08-2016, 01:54 PM
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
08-08-2016, 02:26 PM
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
08-08-2016, 02:40 PM
Kenneth - it's still not working, "Run Time Error 4168" Application-defined or object-defined error"

Any thoughts?

Graham

Kenneth Hobs
08-09-2016, 06:02 AM
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