Benefits of using template in generated word document?

C

ChrisMattock

I have an excel spreadsheet which generates a word document, currentl
using a file LOA_Template.doc - a read only file then saving as anothe
doc file.

What would be the advantage of using a template file? And how would
make the file save itself as a doc file after the information has bee
added... code follows...

Sub main()
On Error GoTo Handler

strLOADate = Cells(16, 2)
strProjectNumber = Cells(6, 2)
strProjectname = Cells(7, 2)
strFAO = Cells(8, 2)
strContractorName = Cells(9, 2)
strContractorAddress1 = Cells(10, 2)
strContractorAddress2 = Cells(11, 2)
strContractorAddress3 = Cells(12, 2)
strContractorAddress4 = Cells(13, 2)
strContractorAddress5 = Cells(14, 2)
strContractorAddress6 = Cells(15, 2)
strReference = Cells(17, 2)
strPackageName = Cells(18, 2)
strWorksServices = Cells(19, 2)
strValueNumber = Cells(20, 2)
strValueWord = Cells(21, 2)
strContractType = Cells(22, 2)
strPMName = Cells(23, 2)
strPMTelephone = Cells(24, 2)
strCostIntegrator = Cells(25, 2)
strCommencementStatement = Cells(26, 2)
strCommencementDate = Cells(27, 2)
strCompletionStatement = Cells(28, 2)
strCompletionDate = Cells(29, 2)
strSecondaryOptions = Cells(30, 2)
strStage = Cells(31, 2)
strSignature = Cells(64, 1)
strTitle = Cells(65, 1)
strBAAAddress1 = Cells(67, 1)
strBAAAddress2 = Cells(68, 1)
strBAAAddress3 = Cells(69, 1)
strBAAAddress4 = Cells(70, 1)
strBAAAddress5 = Cells(71, 1)
strBAAAddress6 = Cells(72, 1)
strBAAAddress7 = Cells(73, 1)


Fname$ = InputBox("Save Letter of Acceptance as
PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If Fname$ = Cancel Then
End
End If

Dim Fnum As Integer
Fnum = FreeFile
Open ThisWorkbook.Path & "\Templates\log.txt" For Append As #Fnum
Print #Fnum, Fname$, Format(Now, "dd-mmm-yyy hh:mm"), "LOA"
Environ("username")
Close #Fnum

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Ope
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airpor
Team\SE Airports TSA Tea
Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range
Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate2").Range
Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate3").Range
Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate4").Range
Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate5").Range
Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate6").Range
Format(strLOADate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("ProjectNumber").Range
strProjectNumber

appWD.ActiveDocument.Bookmarks("ProjectName").Range
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName2").Range
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName3").Range
strProjectname

appWD.ActiveDocument.Bookmarks("FAO").Range = strFAO

appWD.ActiveDocument.Bookmarks("ContractorName").Range
strContractorName

appWD.ActiveDocument.Bookmarks("ContractorAddress1").Range
strContractorAddress1
appWD.ActiveDocument.Bookmarks("ContractorAddress2").Range
strContractorAddress2
appWD.ActiveDocument.Bookmarks("ContractorAddress3").Range
strContractorAddress3
appWD.ActiveDocument.Bookmarks("ContractorAddress4").Range
strContractorAddress4
appWD.ActiveDocument.Bookmarks("ContractorAddress5").Range
strContractorAddress5
appWD.ActiveDocument.Bookmarks("ContractorAddress6").Range
strContractorAddress6

appWD.ActiveDocument.Bookmarks("BAAAddress1").Range
strBAAAddress1
appWD.ActiveDocument.Bookmarks("BAAAddress2").Range
strBAAAddress2
appWD.ActiveDocument.Bookmarks("BAAAddress3").Range
strBAAAddress3
appWD.ActiveDocument.Bookmarks("BAAAddress4").Range
strBAAAddress4
appWD.ActiveDocument.Bookmarks("BAAAddress5").Range
strBAAAddress5
appWD.ActiveDocument.Bookmarks("BAAAddress6").Range
strBAAAddress6
appWD.ActiveDocument.Bookmarks("BAAAddress7").Range
strBAAAddress7

appWD.ActiveDocument.Bookmarks("Title").Range = strTitle

appWD.ActiveDocument.Bookmarks("Signature").Range = strSignature

appWD.ActiveDocument.Bookmarks("Reference").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference2").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference3").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference4").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference5").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference6").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference7").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference10").Range = strReference

appWD.ActiveDocument.Bookmarks("PackageName").Range =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName2").Range =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName3").Range =
strPackageName

appWD.ActiveDocument.Bookmarks("WorksServices").Range =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices2").Range =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices3").Range =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices4").Range =
strWorksServices

appWD.ActiveDocument.Bookmarks("ValueNumber").Range =
Format(strValueNumber, "£#,##0.00")
appWD.ActiveDocument.Bookmarks("ValueNumber2").Range =
Format(strValueNumber, "£#,##0.00")

appWD.ActiveDocument.Bookmarks("ValueWord").Range = strValueWord

appWD.ActiveDocument.Bookmarks("ContractType").Range =
strContractType

appWD.ActiveDocument.Bookmarks("PMName").Range = strPMName

appWD.ActiveDocument.Bookmarks("PMTelephone").Range =
Format(strPMTelephone, "0#### ######")

appWD.ActiveDocument.Bookmarks("CostIntegrator").Range =
strCostIntegrator

appWD.ActiveDocument.Bookmarks("CommencementStatement").Range =
strCommencementStatement

appWD.ActiveDocument.Bookmarks("CommencementDate").Range =
Format(strCommencementDate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("CompletionStatement").Range =
strCompletionStatement

appWD.ActiveDocument.Bookmarks("CompletionDate").Range =
Format(strCompletionDate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("SecondaryOptions").Range =
strSecondaryOptions

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" &
strProjectNumber, vbDirectory) = "" Then MkDir
"\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE
Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs
FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport
Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
& "\" & Fname$
appWD.ActiveDocument.Close
appWD.Quit

Exit Sub
Handler: 'Get here means failure
Select Case Err.Number
Case 13
MsgBox "Missing data"
Case Else
'Any others ?
End Select
appWD.ActiveDocument.Close False
'Clean Up objects etc
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inputbox help... 3
Macro help, populating excel spreadsheet 3
Challenging? 7
copying data in a sheet to a new Word document problem 1

Top