Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Benefits of using template in generated word document?
No one help? -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=559354 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Benefits of using template in generated word document?
Still stuck here :( :confused -- ChrisMattoc ----------------------------------------------------------------------- ChrisMattock's Profile: http://www.excelforum.com/member.php...fo&userid=3391 View this thread: http://www.excelforum.com/showthread.php?threadid=55935 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert a database-generated list in word into a table? | Excel Discussion (Misc queries) | |||
convert excel document to word document (not a picture) | Excel Discussion (Misc queries) | |||
link excel data to word document template | Excel Worksheet Functions | |||
Can you transform an excel document into a word document? | Excel Discussion (Misc queries) | |||
Word document from template | Excel Programming |