PDA

View Full Version : VBA to Add 3 Aligned Footers with Custom Formatting and Text



NoIdea
05-27-2016, 11:15 AM
Hey all,

I'm completely new to VBA, and have been assigned an unusually difficult (at least to me) task.

I have an excel file that already has a Macro written to create a formatted word document (this was given to me). I need to create a complex footer for this created word document. The footer must show the File Path (left justified), as well as "Page 1 of X" (right justified) and some text such as "***TEXT HERE*** that will be center justified, one line above the other two footers (so the file path can be long, and not run into this text).

This is pretty far over my head, the only VBA/Macro's I've done before have been simple. I don't the code for creating the doc, just to add and edit this footer.

This is all I could figure out, and (obviously) it does essentially none of what I need it to do... :banghead:



With ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).Range.Text = "File Path"
.Footers(wdHeaderFooterPrimary).PageNumbers.Add _
PageNumberAlignment:=wdAlignPageNumberRight, _
End With


Thanks in advance!


16278

mdmackillop
05-28-2016, 02:59 PM
Hi
Simplest way is to create a Word template containing the Path and Page Numbering footers. For the text footer, use a document field, such as title and pass this to the template, something like the following (extracted from code I use for similar)


wdApp.Visible = True 'Make the application visible to the user (if wanted)
Set MyDoc = wdApp.Documents.Add(Template:=P2W)
Application.ScreenUpdating = False
MyDoc.SaveAs Pth & TextBox4 & ".doc"
wdApp.Selection.Paste
Application.CutCopyMode = False
wdApp.Run "FixLayout" 'This runs a Word Template macro
With MyDoc
.BuiltinDocumentProperties(1) = TextBox3
.BuiltinDocumentProperties(2) = "BILL NO. " & TextBox1 & " - " & UCase(TextBox2)
.BuiltinDocumentProperties(3) = TextBox1
End With

NoIdea
05-31-2016, 01:08 PM
I understand the context of what you are describing, but I have two questions.

1. When I enter this text into the Macro that is already created (the one in Excel which displays the word doc); nothing happens
2. I tried creating a word template from the suggested 3 footer template already, but when i went to run the macro, I got an error (It said something about not being able to access the template).

Also, what would I need to change in your example to display what I had referenced? I'm very clearly new to this.

Thanks for your help, I look forward to your response

mdmackillop
05-31-2016, 01:13 PM
Here is a working example, Add files to C:\VBAX or change code to suit your location

Sub Test()
'Requires reference to Word Object Library
Dim wdapp, mydoc
Dim Pth

Pth = "C:\VBAX\"

'Copy some data
Range("C4:C17").Copy

'See if Word is already running
On Error Resume Next
Set wdapp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'Launch a new instance of Word
Err.Clear
On Error GoTo Error_Handler
Set wdapp = CreateObject("Word.Application")
End If
wdapp.Visible = True 'Make the application visible to the user (if wanted)
'Create new doc based on template and save
Set mydoc = wdapp.Documents.Add(Template:=Pth & "Sample.dotx")
Application.ScreenUpdating = False
mydoc.SaveAs Pth & "Sample1.doc"

'Paste data into Word
wdapp.Selection.Paste

Application.CutCopyMode = False
With mydoc
.BuiltinDocumentProperties(1) = Cells(1, 1)
End With
Error_Handler:
End Sub

NoIdea
06-02-2016, 12:29 PM
Is this supposed to create a word document with that text? When I run the Macro, the Word window will open, but there is no document open/created. Also, I'm curious after just looking at the code (because I couldn't get an output), does this Macro only copy/paste info from those cells into Word, or does it post it into a formatted footer? I don't have any info in the excel file that I need to paste; I just need a footer made as a standalone added text in the word file made by the macro.

Sorry if this is confusing (or if I'm downright incorrect, which is entirely possible), it's very specific and hard to explain. Especially when you haven't learned VBA :crying:

Thanks again

mdmackillop
06-02-2016, 12:34 PM
The zip folder contains 2 files, Save both of these to a folder called C:\VBAX
Open the excel file and go to the code page. Make sure you have a reference to Word Object Library and run the code.

NoIdea
06-02-2016, 01:01 PM
This is the process I followed, and the Word window will open, but a document will not. I also tried changing the path (code, too) and saving it elsewhere, to no avail

mdmackillop
06-02-2016, 01:35 PM
What version of Word are you using? Have you tried stepping through the code?

NoIdea
06-03-2016, 11:18 AM
I have Version 14.0.

The good news for both of us, is that it looks like I'm slowly figuring out my stupidity. I entered the code into the Macro I have already, but I am still having some problems. The code I already have will generate a word doc; however after adding your code it will only add 17 blank lines to the beginning of the Doc. There is unfortunately no footer added.

With that being said, I did open the Sample word doc you provided, and i saw it worked there. (however, I think the left footer was file name instead of file path. This may be because the Doc wasn't saved.)

I'm not sure how to tweak the code to get the footer to work in the doc I have; any help is appreciated!

mdmackillop
06-03-2016, 12:41 PM
The path and page number are part of the Word template: you do not need to provide this information. The centre footer is a field which is based upon the Word document title. The Macro passes this information only in this line.
.BuiltinDocumentProperties(1) = Cells(1, 1)
This could probably be written to the footer, but I was just using some old code which works for me.

Save your word document as a template with the footer as you want it including the title field.

NoIdea
06-03-2016, 12:57 PM
Ah I see, it's starting to make more sense.

Is there any way to get the center footer to just be some entered text? As I believe I just need it to display something along the lines of "Confidential"

Also, any idea why the footer isn't showing up at all in the document I'm using? That's the main issue I'm trying to address at the moment.

mdmackillop
06-03-2016, 01:04 PM
Can you attach your Word Document?

mdmackillop
06-03-2016, 01:33 PM
Sub Test()
'Requires reference to Word Object Library
Dim wdapp, mydoc
Dim Pth


Pth = "C:\VBAX\"

'See if Word is already running
On Error Resume Next
Set wdapp = GetObject(, "Word.Application")


If Err.Number <> 0 Then
'Launch a new instance of Word
Err.Clear
Set wdapp = CreateObject("Word.Application")
End If

wdapp.Visible = True 'Make the application visible to the user (if wanted)
Set mydoc = wdapp.Documents.Open(Pth & "temp.docx")
Application.ScreenUpdating = False
With mydoc
.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore "Confidential"
End With
End Sub

NoIdea
06-06-2016, 11:49 AM
I inserted this code into the current Excel Macro, and again, nothing happened. There is no footer in the created Word doc and the document looks unchanged with this added code. Is there anything special that has to be done to get the footer to work? I'm working with an excel file in my downloads, so I changed the path to that. However, I can't think of anything else I would be doing incorrectly.

Do I have to have a word doc already created / saved to a file for a footer to be added? Because the excel file creates a word doc that does not yet exist, and it's unsaved. Not sure if this is relevant, but I remember I could only get your sample to work with the attached word Doc you already had.

Thanks again

mdmackillop
06-06-2016, 12:11 PM
I believe I've addressed all these points previously. Have you added the footer fields to your Word Document? see Post #12

NoIdea
06-06-2016, 12:26 PM
I'm afraid I don't know what you mean when you say "have you added the footer fields to your Word Document"; is that not the purpose of the Macro - to add the footer to the Document?

And I would have uploaded the word Document long ago, but unfortunately the Document is confidential (hence the tag in the footer). Also, the document is essentially blank besides the text my current Macro is adding. If you can inform me what you would be looking for if I were able to pass it on to you, I would be more than willing to inform you.

mdmackillop
06-06-2016, 12:38 PM
Why would you add information from Excel which is a property of the Word Document. With regard to your document contents you can delete all the text. Post it blank with the document name and path.

NoIdea
06-06-2016, 01:03 PM
The whole Word document is the result of adding information from Excel; the Macro in Excel creates the whole word document and formats it accordingly, so why wouldn't you add information from excel that's a property of the Word Doc? It seems that's the same way you suggested to do it from the beginning. (A genuine question, my intention isn't to sound snarky. I could be misinterpreting your comment)

The word document is created with the default name of "Document1" (as it hasn't been saved anywhere). When saved, the name suggested is the 1st line of the document "Correspondence".

The path I'm working with is C:\Users\XXX\Downloads

16329

mdmackillop
06-06-2016, 01:20 PM
Excel does not know the number of pages in the word document, so how do you provide it? You use a word document or template with an appropriate footer field. You can add the path field also in the appropriate location. You then use this code


With mydoc
.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore "Confidential"
End With

to add the custom text into the preformatted footer.

NoIdea
06-07-2016, 10:09 AM
I see your point, I was unaware this was a functionality limitation between the two programs. With that being said, it's also not possible for me to use a pre-formatted word doc, as the macro creates a completely new document every time I run a report (unless there is a way to attach a template including a formatted footer to the Macro). But, again, I may be misunderstanding; I just haven't had any luck with this so far.

mdmackillop
06-07-2016, 10:14 AM
I refer you to Post #10

NoIdea
06-07-2016, 10:33 AM
Yes, I recall the post. However, at no point have you said that what I am speaking of (passing the info through via a Macro) is impossible. You have merely suggested alternatives e.g. "Save your word document as a template with the footer as you want it including the title field." Unfortunately this is not an alternative I can use, because a new document is created every time a report is run.

I now understand that Excel cannot pass page / file info through to Word; if there is no other way I can make a workaround for this besides your suggestion of having a pre-formatted Word document, then it seems as if it may be one of the impossible scenarios.

mdmackillop
06-07-2016, 10:45 AM
Please post your whole excel code, omitting any sensitive information.

NoIdea
06-07-2016, 11:25 AM
Here's the document creation / formatting portion.


Private Function WriteToWordDoc() Dim objWord
Dim ObjDoc
Set objWord = CreateObject("Word.Application") 'Selects the Word Application
Set ObjDoc = objWord.Documents.Add 'Creates Word document.
objWord.Visible = True 'Makes the Word document open in the taskbar

'The following code formats and inserts the text of the Word report.
With ObjDoc

With .Styles(wdStyleHeading1).Font
.Name = "Cambria"
.Size = 14
.Bold = True
.Color = 300
End With
'---------------------------------------------------------------------


With .Styles(wdStyleHeading2).Font
.Name = "Cambria"
.Size = 13
.Bold = True
.Color = 300
End With

'---------------------------------------------------------------------

Set Legend = ObjDoc.Styles.Add(Name:="Legend", _
Type:=wdStyleTypeCharacter)

With Legend.Font
.Name = "Times New Roman"
.Size = 8
.Bold = False
End With

'---------------------------------------------------------------------


Set Bold = ObjDoc.Styles.Add(Name:="First", _
Type:=wdStyleTypeCharacter)

With Bold.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
End With

'---------------------------------------------------------------------

Set Normal = ObjDoc.Styles.Add(Name:="Second", _
Type:=wdStyleTypeCharacter)

With Normal.Font
.Name = "Times New Roman"
.Size = 12
.Bold = False
End With

'---------------------------------------------------------------------


Set largeBold = ObjDoc.Styles.Add(Name:="Third", _
Type:=wdStyleTypeCharacter)

With largeBold.Font
.Name = "Times New Roman"
.Size = 14
.Bold = True
End With

'---------------------------------------------------------------------


Set redNote = ObjDoc.Styles.Add(Name:="Fourth", _
Type:=wdStyleTypeCharacter)

With redNote.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
.Color = RGB(300, 0, 0)
End With

'---------------------------------------------------------------------

.Range(0).ParagraphFormat.Alignment = wdAlignParagraphCenter
.Range(0).Style = .Styles(Bold)
.Content.InsertAfter "Correspondence"
.Content.InsertParagraphAfter
.Range(.Characters.Count - 1).Style = .Styles(Normal)
.Content.InsertAfter "Subtitle"
.Content.InsertParagraphAfter

End With

End Function

mdmackillop
06-07-2016, 01:39 PM
Copy the attached template into C:\VBAX and run the following.


Private Function WriteToWordDoc()
Dim objWord
Dim ObjDoc
Dim Pth As String
Dim TPlate As String
Dim TextToAdd As String


Pth = "C:\VBAX\"
TPlate = "Sample.dotx"
TextToAdd = InputBox("Footer Text", "Footer", "Confidential")


Set objWord = CreateObject("Word.Application") 'Selects the Word Application
Set ObjDoc = objWord.Documents.Add(Template:=Pth & TPlate) 'Creates Word document.
objWord.Visible = True 'Makes the Word document open in the taskbar


'The following code formats and inserts the text of the Word report.
With ObjDoc

.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore TextToAdd

With .Styles(wdStyleHeading1).Font
.Name = "Cambria"
.Size = 14
.Bold = True
.Color = 300
End With
'---------------------------------------------------------------------




With .Styles(wdStyleHeading2).Font
.Name = "Cambria"
.Size = 13
.Bold = True
.Color = 300
End With


'---------------------------------------------------------------------


Set Legend = ObjDoc.Styles.Add(Name:="Legend", _
Type:=wdStyleTypeCharacter)


With Legend.Font
.Name = "Times New Roman"
.Size = 8
.Bold = False
End With


'---------------------------------------------------------------------




Set Bold = ObjDoc.Styles.Add(Name:="First", _
Type:=wdStyleTypeCharacter)


With Bold.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
End With


'---------------------------------------------------------------------


Set Normal = ObjDoc.Styles.Add(Name:="Second", _
Type:=wdStyleTypeCharacter)


With Normal.Font
.Name = "Times New Roman"
.Size = 12
.Bold = False
End With


'---------------------------------------------------------------------




Set largeBold = ObjDoc.Styles.Add(Name:="Third", _
Type:=wdStyleTypeCharacter)


With largeBold.Font
.Name = "Times New Roman"
.Size = 14
.Bold = True
End With


'---------------------------------------------------------------------




Set redNote = ObjDoc.Styles.Add(Name:="Fourth", _
Type:=wdStyleTypeCharacter)


With redNote.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
.Color = RGB(300, 0, 0)
End With


'---------------------------------------------------------------------


.Range(0).ParagraphFormat.Alignment = wdAlignParagraphCenter
.Range(0).Style = .Styles(Bold)
.Content.InsertAfter "Correspondence"
.Content.InsertParagraphAfter
.Range(.Characters.Count - 1).Style = .Styles(Normal)
.Content.InsertAfter "Subtitle"
.Content.InsertParagraphAfter


End With


End Function

snb
06-08-2016, 01:39 AM
You can use 'G:\OF\__docvar snb.docx' as a 'template'.
Do all the formatting in the 'template'; don't use VBA for that purpose.
Use Documentvariables to export data from Excel into a Word-document.

in Excel you use


Sub M_snb()
With GetObject("G:\OF\__docvar snb.docx")
.variables("snb_001") = InputBox("footertext", "snb")
If Dir("G:\OF\new.docx") <> "" Then Kill "G:\OF\new.docx"
.Windows(1).Visible = True
.saveas2 "G:\OF\new.docx"
.storyranges(9).Fields.Update
.Save
.Close 0
End With
End Sub

NoIdea
06-08-2016, 08:05 AM
I must be doing something incorrectly, as this is again not working. The template is not getting used; the word document that is created only has "confidential" in the bottom left footer, and nothing else in the footer. Also, I'll note that I do not have access to save / create files directly under c:\ which is why I was working in the Downloads folder. However, I've changed the path in your code to Pth = "C:\Users\XXXX\Downloads" and saved the Sample folder there.

Eventually, the Excel doc will be saved to a server (it already is, but if this ever works it will be updated).

NoIdea
06-08-2016, 08:10 AM
You can use 'G:\OF\__docvar snb.docx' as a 'template'.
Do all the formatting in the 'template'; don't use VBA for that purpose.
Use Documentvariables to export data from Excel into a Word-document.

in Excel you use


Sub M_snb()
With GetObject("G:\OF\__docvar snb.docx")
.variables("snb_001") = InputBox("footertext", "snb")
If Dir("G:\OF\new.docx") <> "" Then Kill "G:\OF\new.docx"
.Windows(1).Visible = True
.saveas2 "G:\OF\new.docx"
.storyranges(9).Fields.Update
.Save
.Close 0
End With
End Sub



Unfortunately I'm not looking to reformat the entire document. The code for this macro (for formatting alone) is around 30 pages, and it totals around 50; it's way too much to re-do. I'm just looking for a solution to add a footer at this point in time.

snb
06-08-2016, 08:21 AM
Sub M_snb()
With GetObject("C:\Users\XXXX\Downloads\__docvar snb.docx")
.variables("snb_001") = InputBox("footertext", "snb")
If Dir(""C:\Users\XXXX\Downloads\new.docx") <> "" Then Kill ""C:\Users\XXXX\Downloads\new.docx"
.Windows(1).Visible = True
.saveas2 ""C:\Users\XXXX\Downloads\new.docx"
.storyranges(9).Fields.Update
.Save
.Close 0
End With
End Sub

NB Save my file as "C:\Users\XXXX\Downloads\__docvar snb.docx"

NoIdea
06-08-2016, 08:26 AM
Ah, I see what you're referring to now. What portion of the original VBA would the code you provided replace?

snb
06-08-2016, 09:03 AM
Just run the code and assess the result.

It should replace the whole code, because formatting in VBA is the least efficient thing you can do.

NoIdea
06-09-2016, 11:15 AM
I see this creates a new document. Is there any way to just use the template and add the formatting to the document that the code already creates? Like I said, there's an insane amount of formatting already done in VBA, which I'm not technically allowed to mess with. Unfortunately I have little to no freedom for creativity with this.

snb
06-09-2016, 02:21 PM
Yo can run the formatting code on the template once, and save the template.
After that you only need the code to create a new document based on the template.

NoIdea
06-10-2016, 12:25 PM
The document has to be formatted piece by piece every time, as new / different information (and different amounts) is submitted every time. Also, like I said, unfortunately I'm instructed to keep the current macro, so formatting the template isn't necessarily a feasible option. I have no doubt it's a smarter option, it's just not one I can take.

With that said, is there any way to use your template as the sole document for the code I shared above? So there won't be a second new document created, but the code already written in VBA will still add info / do the formatting on your template.

mdmackillop
06-10-2016, 01:03 PM
Have you added the File Path and Page Number fields to your Word document footer yet?

NoIdea
06-13-2016, 01:12 PM
If you are referencing the word document titled "correspondence" created by the VBA code I have already - no, my assumption was that part of the purpose of the provided VBA code was to add footers to the document

mdmackillop
06-13-2016, 01:32 PM
I give up!

snb
06-13-2016, 01:51 PM
VBA is a language. As long as you can't 'speak/read' the language we can't communicate

Please, before asking questions read ......


Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble
(http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

NoIdea
06-16-2016, 12:34 PM
I'm not a VBA programmer, nor did I claim to be. I made it very clear from step 1 that I didn't have the slightest idea what was going on. With that being said I understand as well that it's difficult to converse with someone who isn't communicating efficiently.

I did, however, provide the code and explain the solution I needed. Unfortunately I helped as much as I could, however, I understand it's not always as easy as that and it's also not your problem.

Thanks for the help.

Cheers