Thread: Benefits of using template in generated word document?
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ChrisMattock[_35_] ChrisMattock[_35_] is offline
external usenet poster
  
Posts: 1
Default Benefits of using template in generated word document?

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").Ra nge
strProjectNumber

appWD.ActiveDocument.Bookmarks("ProjectName").Rang e
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName2").Ran ge
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName3").Ran ge
strProjectname

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

appWD.ActiveDocument.Bookmarks("ContractorName").R ange
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").Rang e
strBAAAddress1
appWD.ActiveDocument.Bookmarks("BAAAddress2").Rang e
strBAAAddress2
appWD.ActiveDocument.Bookmarks("BAAAddress3").Rang e
strBAAAddress3
appWD.ActiveDocument.Bookmarks("BAAAddress4").Rang e
strBAAAddress4
appWD.ActiveDocument.Bookmarks("BAAAddress5").Rang e
strBAAAddress5
appWD.ActiveDocument.Bookmarks("BAAAddress6").Rang e
strBAAAddress6
appWD.ActiveDocument.Bookmarks("BAAAddress7").Rang e
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").Rang e = strReference

appWD.ActiveDocument.Bookmarks("PackageName").Rang e =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName2").Ran ge =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName3").Ran ge =
strPackageName

appWD.ActiveDocument.Bookmarks("WorksServices").Ra nge =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices2").R ange =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices3").R ange =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices4").R ange =
strWorksServices

appWD.ActiveDocument.Bookmarks("ValueNumber").Rang e =
Format(strValueNumber, "£#,##0.00")
appWD.ActiveDocument.Bookmarks("ValueNumber2").Ran ge =
Format(strValueNumber, "£#,##0.00")

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

appWD.ActiveDocument.Bookmarks("ContractType").Ran ge =
strContractType

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

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

appWD.ActiveDocument.Bookmarks("CostIntegrator").R ange =
strCostIntegrator

appWD.ActiveDocument.Bookmarks("CommencementStatem ent").Range =
strCommencementStatement

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

appWD.ActiveDocument.Bookmarks("CompletionStatemen t").Range =
strCompletionStatement

appWD.ActiveDocument.Bookmarks("CompletionDate").R ange =
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


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=559354

Reply With QuoteReply With Quote