Writing Notes field data to a MSWord / Excel Header or Footer

HCL Domino Domino Forum
1

We are building an application similar to the MS Office Document Libary, where we will allow the users to create a MS Word / Excel document from a list of templates. I have found many code samples to help move Notes field data to fields created in the Word Template. But, I have not been able to figure out how to write to the MS Word / Excel Header / Footer, where I need some field values to go (such as a DocNumber, Notes document title, Version field, Author Name, classification, etc.) We need to place the Notes document field value data in the footer of the created Word documents. Thank you kindly for any suggestions, tips, pointers, documentation, etc.

2

Subject: Writing Notes field data to a MSWord / Excel Header or Footer

In Excel the header and footer are properties of the PageSetup class. Assuming you have a handle to the active sheet (Set xlSheet = xlApp.Workbook.ActiveSheet) you can set these properties as follows:

With xlSheet.PageSetup



	'Header and footer for print page

	.LeftHeader = doc.ExcelHeaderLt(0)

	.CenterHeader = doc.ExcelHeaderCt(0)

	.RightHeader = office

	.LeftFooter = doc.ExcelFooterLt(0)

	.CenterFooter = doc.ExcelFooterCt(0)

	.RightFooter = doc.ExcelFooterRt(0)



	'Set the title rows

	.PrintTitleRows = "$1:$2"

	.PrintTitleColumns = ""



	'Set the page margins (including header/footer margins)

	.LeftMargin = xlApp.InchesToPoints(0.25)

	.RightMargin = xlApp.InchesToPoints(0.25)

	.TopMargin = xlApp.InchesToPoints(0.5)

	.BottomMargin = xlApp.InchesToPoints(0.5)

	.HeaderMargin = xlApp.InchesToPoints(0.25)

	.FooterMargin = xlApp.InchesToPoints(0.25)



	'Set additional print parameters

	.PrintHeadings = False

	.PrintGridlines = False

	.PrintComments = xlPrintNoComments

	.PrintQuality = 600

	.CenterHorizontally = False

	.CenterVertically = False

	.Orientation = xlLandscape

	.Draft = False

	.PaperSize = paperSize

	.FirstPageNumber = xlAutomatic

	.Order = xlDownThenOver

	.BlackAndWhite = False

	.Zoom = 50

	

End With

In Word the Header/Footer is its own class and I’m not as familiar with it. You can get help for it by looking up the class in the VBEditor in Word by clicking on Tools-Macros-Visual Basic Editor. Select the Word library, run a search for HeaderFooter, select the class and then click on the “question mark” for help.

Have fun.

3

Subject: RE: Writing Notes field data to a MSWord / Excel Header or Footer

I am able to set formfields, but not docproperty fields in the header or footer. Since word only allows docproperty fields in the header. Does anyone have code to set a docproperty field in word?

Here is the code on the formfields, but I do not find any thing in the help on docproperty fields:

Set vApp = CreateObject(“Word.Application”) 'Create Word object

vApp.Visible = True

Call vApp.Documents.Add("Dana2.dot") 'Create a new document based on the template "Return and Uplift.Dot"

Set wordDoc = vApp.ActiveDocument 'Get a handle for the active document

With ActiveDocument

    .FormFields(1).Result = "dana"

End With