[RESOLVED] Create document from a template

[mojo69]

I have this code which works but I want to expand on it.

Private Sub Print_Template()
On Error GoTo ERRHANDLE
Dim objWord As Object
'Set objWord = Word.Application
Dim strFileName As String
Set objWord = CreateObject("Word.Application")
strFileName = "invoice1.doc"

With objWord
    .Documents.Open (App.Path & "\invoicetemplate.dot")
    .ActiveDocument.SaveAs (App.Path & "\" & strFileName)
    .Application.Documents.Close
End With
objWord.Quit
Set objWord = Nothing

Exit Sub
ERRHANDLE:
    ErrorCode = Err.Number
    ErrorDesc = Err.Description
    MsgBox Str(ErrorCode) + ErrorDesc
    Resume Next
End Sub

What I want to do is put data into the document at bookmarks. I have these bookmarks set in the .dot already. Can anyone point me to an example? Thanks.

Also should the objWord.Quit statement be inside the With?

[Hack]

Here is an example from one of my programs. Modify as necessary.

 With WordObject
     .Documents.Open ("c:\program files\qcap\transmove.doc")     
     .ActiveDocument.Bookmarks("fromlocation").Select
     .Selection.Text = (lblMoveFrom.Caption)
     .ActiveDocument.Bookmarks("tolocation").Select
     .Selection.Text = (lblMoveTo.Caption)
     .ActiveDocument.Bookmarks("numbers").Select
     .Selection.Text = (txtStatus.Text)
     .ActiveDocument.Bookmarks("preparedby").Select
     .Selection.Text = (UCase(UserAccount))
     .ActiveDocument.Bookmarks("comments").Select
     .Selection.Text = (txtComments.Text)
     .ActiveDocument.Bookmarks("movedate").Select
     .Selection.Text = (Today)
    End With

objWord should be outside the With.

[mojo69]

Can you change the font for each entry. This will be going into an invoice so I may need to change the font size for some parts of the document. How much different is this process if I want to create a .pdf instead?


If I am looping through a dataset containing multiple invoices do I want to use objWord.Quit every loop? How about Set objWord = Nothing?

Thanks.

[Hack]

The only experience I have with PDF files is creating them from an existing document and that I don't do programmatically.

[MartinLiss]

You could try this. (I haven't used it.)

[mojo69]

Can I make the resulting word document 'read only' when I save it?

Here is what I have now.

Private Sub Print_Template()
On Error GoTo ERRHANDLE
Dim objWord As Object
'Set objWord = Word.Application
Dim strFileName As String
Set objWord = CreateObject("Word.Application")
strFileName = "invoice1.doc"
Dim Sdnam As String, Sdadd1 As String, Sdadd2 As String, Sdcty As String, Sdstate As String
Dim SdZip As String, Shnam As String, Shadd1 As String, Shadd2 As String, Shcty As String
Dim Shstate As String, Shzip As String, Shpfr As String, Cno As String, Cst As String
Dim PoNo As String, InvDate As String, Shpvia As String, KeyCar As String, Terms As String
Dim Fob As String, Qty As String
Dim Ref As String
Dim Line1 As String, Line2 As String, Line3 As String

p3RecMgrINVF.CursRow = 0
Do While p3RecMgrINVF.NextRow
    Ref = p3RecMgrINVF.ColValueGet(-1, "Ref")
    Sdnam = p3RecMgrINVF.ColValueGet(-1, "Sdnam")
    Sdadd1 = p3RecMgrINVF.ColValueGet(-1, "Sdadd1")
    Sdadd2 = p3RecMgrINVF.ColValueGet(-1, "Sdadd2")
    Sdcty = p3RecMgrINVF.ColValueGet(-1, "Sdcty")
    Sdstate = p3RecMgrINVF.ColValueGet(-1, "SdState")
    SdZip = p3RecMgrINVF.ColValueGet(-1, "Sdzip")
    Shnam = p3RecMgrINVF.ColValueGet(-1, "Shnam")   'Shipped to Name
    Shadd1 = p3RecMgrINVF.ColValueGet(-1, "Shadd1")
    Shadd2 = p3RecMgrINVF.ColValueGet(-1, "Shadd2")
    Shcty = p3RecMgrINVF.ColValueGet(-1, "Shcty")
    Shstate = p3RecMgrINVF.ColValueGet(-1, "ShState")
    Shzip = p3RecMgrINVF.ColValueGet(-1, "Shzip")
    Shpfr = p3RecMgrINVF.ColValueGet(-1, "Shpfr")
    Cno = p3RecMgrINVF.ColValueGet(-1, "Cno")       'Contract Number
    Cst = p3RecMgrINVF.ColValueGet(-1, "Cst")
    PoNo = p3RecMgrINVF.ColValueGet(-1, "PONo")     'Purchase Order Number
    InvDate = p3RecMgrINVF.ColValueGet(-1, "Date")
    InvDate = Mid(InvDate, 6, 5) + "/" + Mid(InvDate, 1, 4)
    Shpvia = p3RecMgrINVF.ColValueGet(-1, "Shpvia")
    KeyCar = p3RecMgrINVF.ColValueGet(-1, "KeyCar") 'Car/Trk Number
    Terms = p3RecMgrINVF.ColValueGet(-1, "Terms")
    Fob = p3RecMgrINVF.ColValueGet(-1, "Fob")
    Qty = p3RecMgrINVF.ColValueGet(-1, "Qty")
'        Qty = Format(Qty, "###,###,##0.00")
    Qty = Format(Qty, "###,###,##0")
    Line1 = Sdcty & " " & Sdstate & " " & SdZip
    Line2 = Shcty & " " & Shstate & " " & Shzip
    strFileName = Sdnam & Ref & ".doc"
    
    With objWord
        .Documents.Open (App.Path & "\invoicetemplate.dot")
        .ActiveDocument.Bookmarks("soldtoname").Select
        .Selection.Text = Sdnam
        .ActiveDocument.Bookmarks("soldtoaddress1").Select
        .Selection.Text = Sdadd1
        .ActiveDocument.Bookmarks("soldtocity").Select
        .Selection.Text = Line1
        .ActiveDocument.Bookmarks("billtoname").Select
        .Selection.Text = Shnam
        .ActiveDocument.Bookmarks("billtoaddress1").Select
        .Selection.Text = Shadd1
        .ActiveDocument.Bookmarks("billtocity").Select
        .Selection.Text = Line2
        
        .ActiveDocument.SaveAs (App.Path & "\" & strFileName)
        .Application.Documents.Close
    End With
    objWord.Quit
    
Loop
Set objWord = Nothing
Me.Enabled = True
Exit Sub
ERRHANDLE:
    ErrorCode = Err.Number
    ErrorDesc = Err.Description
    MsgBox Str(ErrorCode) + ErrorDesc
    Resume Next
End Sub

[Hack]

Can I make the resulting word document 'read only' when I save it?

Sure

Private Declare Function SetFileAttributes Lib "kernel32" Alias "SetFileAttributesA" _
(ByVal lpFileName As String, ByVal dwFileAttributes As Long) As Long

'add this after your document has been created and saved
SetFileAttributes App.Path & "\" & strFileName, vbReadOnly

'others you can set are:
vbReadOnly
vbHidden
vbSystem
vbArchive

[mojo69]

I can't thank you enough Hack. I will add this and test it out. I now need to set up the rest of the bookmarks on the template and get this rolling.

Thanks Again.

[MartinLiss]

Now that we've helped you, you can help us by pulling down the Thread Tools menu and clicking the Mark Thread Resolved button which will let everyone know that you have your answer. Also if someone has been particularly helpful, or even particularly unhelpful, you have the ability to affect a their forum "reputation" by rating their post.