PDA

View Full Version : [SOLVED] add header/footer to new word document from excel



RonNCmale
12-23-2013, 05:09 AM
I'm trying to use the following code I got from here:
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=169:automate-microsoft-word-from-excel-using-vba&catid=79&Itemid=475

to be able to insert a header/footer into the new document from excel. Is it possible to be able to insert a custom header/footer ie:Company logo with text?

below is the code from the site above.


Sub Automate_Word_from_Excel_4()
'Automate Word from Excel, using Late Binding. You need not add a reference to the Word library in Excel (your host application), in this case you will not be able to use the Word's predefined constants and will need to replace them by their numerical values in your code.


'This example (Automate using Late Binding) shows how to create a new word document, insert a letter by sourcing data from an excel worksheet, using built-in word styles. This method is particularly useful for making letters with similar content being addressed and emailed to multiple addresses.

'Variables declared as Object type, which can be a reference to any object. Variables cannot be declared as a specific object type (ie. specific to the application which is being automated) using Word.Application or Word.Document, because a reference to the Word library has not been added.
Dim applWord As Object
Dim docWord As Object
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet3")

'Create a new instance of the Word application, if an existing Word object is not available.
'Set the Application object as follows:
On Error Resume Next
Set applWord = GetObject(, "Word.Application")
'if an instance of an existing Word object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set applWord = CreateObject("Word.Application")
End If
'disable error handling:
On Error GoTo 0

'make the Word window visible:
applWord.Visible = True

'maximize Word window:
'replaced the Word's built-in constant wdWindowStateMaximize with its numerical value 1.
applWord.WindowState = 1

'add a new word document:
Set docWord = applWord.Documents.Add

'to save document in the default folder:
docWord.SaveAs fileName:="newDoc1.docx"


With docWord


'A built-in or user-defined style is returned by using Styles(index). Index can be mentioned as the user-defined style name, or a WdBuiltinStyle constant or index number. You can set style properties like Font, as follows.
'Set styles for the new word document:
'replaced the Word's built-in constant wdStyleHeading1 with its numerical value -2.
With .Styles(-2)

.Font.Name = "Verdana"
.Font.Size = 13
'replaced the Word's built-in constant wdColorRed with its numerical value 255.
.Font.Color = 255
.Font.Bold = True
'replaced the Word's built-in constant wdAlignParagraphCenter with its numerical value 1.

.ParagraphFormat.Alignment = 1

End With

'replaced the Word's built-in constant wdStyleHeading2 with its numerical value -3.
With .Styles(-3)

.Font.Name = "TimesNewRoman"
.Font.Size = 12
'replaced the Word's built-in constant wdColorBlue with its numerical value 16711680.
.Font.Color = 16711680

.Font.Bold = True

End With

'replaced the Word's built-in constant wdStyleNormal with its numerical value -1.
With .Styles(-1)

.Font.Name = "Arial"
.Font.Size = 10
'replaced the Word's built-in constant wdColorBlue with its numerical value 16711680.
.Font.Color = 16711680
'replaced the Word's built-in constant wdLineSpaceSingle with its numerical value 0.

.ParagraphFormat.LineSpacingRule = 0

End With

'replaced the Word's built-in constant wdStyleBodyText with its numerical value -67 .
With .Styles(-67)

.Font.Name = "Arial"
.Font.Size = 10
'replaced the Word's built-in constant wdColorGreen with its numerical value 32768.
.Font.Color = 32768
'replaced the Word's built-in constant wdAlignParagraphJustify with its numerical value 3.
.ParagraphFormat.Alignment = 3
'replaced the Word's built-in constant wdLineSpaceSingle with its numerical value 0.
.ParagraphFormat.LineSpacingRule = 0

.ParagraphFormat.FirstLineIndent = applWord.InchesToPoints(0.5)

End With



'Use the Range.InsertParagraphAfter Method to insert a paragraph mark. The insertion is made after the specified range. The range expands to increase the new paragraph, after applying this method.
'Use the Range.InsertAfter Method to insert text at the end of a range.
'replaced the Word's built-in constant wdStyleHeading1 with its numerical value -2.
.Range(0).Style = .Styles(-2)
.Content.InsertAfter "Request for Mobile Bill Correction"
'Insert paragraph marks at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter

'set style starting from range after the Heading 1:
'replaced the Word's built-in constant wdStyleHeading2 with its numerical value -3.
.Range(.Characters.count - 2).Style = .Styles(-3)
'Insert text, use Chr(11) to add new line:
.Content.InsertAfter ws.Range("A7") & Chr(11) & ws.Range("B7") & Chr(11) & ws.Range("C7") & Chr(11) & ws.Range("D7") & Chr(11) & ws.Range("E7") & Chr(11) & ws.Range("F7")
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter

'use the Paragraphs.Last Property to return the last para in the document:
'replaced the Word's built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter vbTab & ws.Range("A1")
'Insert paragraph mark at the end of the document. A Range object is returned by the Content property.
.Content.InsertParagraphAfter

'replaced the Word's built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter "Dear Sir,"
.Content.InsertParagraphAfter

'replaced the Word's built-in constant wdStyleBodyText with its numerical value -67 .
.Paragraphs.Last.Style = .Styles(-67)
.Content.InsertAfter ws.Range("A3")
.Content.InsertParagraphAfter

'replaced the Word's built-in constant wdStyleBodyText with its numerical value -67 .
.Paragraphs.Last.Style = .Styles(-67)
.Content.InsertAfter ws.Range("A5")
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter

'replaced the Word's built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter "Yours Sincerely,"
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter

'replaced the Word's built-in constant wdStyleNormal with its numerical value -1.
.Paragraphs.Last.Style = .Styles(-1)
.Content.InsertAfter "Alan Croft"
.Content.InsertParagraphAfter


End With



'close and save the document:
'replaced the Word's built-in constant wdSaveChanges with its numerical value -1.
docWord.Close SaveChanges:=-1

'quit the word application:
applWord.Quit

'clear the object variables:
Set docWord = Nothing
Set applWord = Nothing


End Sub

Kenneth Hobs
12-23-2013, 07:12 AM
When your code does not paste properly, please paste it to Notepad or other and then copy and paste that.

There are two approaches. One is to record an MSWord macro where you add your header/footer and then paste that code into your Excel macro and them modify it to prefix the MSWord object defined in Excel. The second method makes life so much easier. Create a template with your setup already in it and Add in the macro. e.g. http://vbaexpress.com/forum/showthread.php?p=185718

snb
12-23-2013, 09:08 AM
Or even simpler:

make a Word document with the text/graphics you want to be inserted in the header.
Save it as 'G:\OF\0_header.docx' and close the document.

Open a new document.
Insert/header/edit header.
Insert/quickparts/fields/IncludeText.
So the field will look like (alt-F9):

{INCLUDETEXT "G:\\OF\\0_header.docx"}

You can refresh the field using F9.
You can use the 0_header document for all your Word documents that need the same header.
In this case VBA isn't the most appropriate method.

NB. adapt the path "G:\OF\" to your own situation.

RonNCmale
12-23-2013, 04:09 PM
Thanks Kenneth & snb for your suggestions. I think creating a template is the way I'll go with this.
Again thanks!!!!