After pasting in the excel data so i've set up a basic example to demonstrate how this will work i've got a basic excel workbook with multiple worksheets.
Each of which contains a table and a chart now i've already written the basic code that will copy this content into a new word document.
So just to show you the basics of that in the visual basic editor it essentially loops through the worksheet's collection and copies the table and the chart from each worksheet.
Into a new page in that document i'll drop a link in the video description so that you can download this file yourself and then follow along with me and write the code that i'm.
Going to add and just to demonstrate how this works let's just see what it gives us if we run the subroutine we should find a brand new word document.
Add a header that has some simple text saying something like wise owl movies so let's close down this word document and head back to the visual basic editor i will bother saving the changes there.
And just to help us write the code to manipulate the header that we're about to create let's declare a variable that holds a reference to a word header footer object.
So i'm going to declare a variable called hdr as word dot header footer so you use the same class regardless of whether you're manipulating a header or a footer object.
The variable isn't essential here it just makes our life a little easier next we can set this variable to refer to the header that we want to manipulate and we'll do that after we finish.
Pasting in all the content so just after the end with statement let's say set hdr equals then we need to refer to the document that.
Contains the header we want to
Manipulate so we'll use our doc variable to do that then we need to refer to a section within that document so i'll use the.Sections property our document has only a single section so we can refer to sections one and then the headers property within there and then we have to pick from one.
Of three different headers so we've got the even pages the first page and the primary header footer option initially i want to affect.
All of the headers in the same way so the headers on every single page should be set to the same so i'm going to use the header footer.
Primary option here and then having done that we can manipulate that header by referring to its properties the main property we're going to be.
Manipulating here is the range property so i'm going to begin another with block and say with hdr dot range give myself a couple of blank lines scroll down a little so you can see.
What's going on and then add the end with statement and then inside here let's do a couple of basic things let's set the text of our header.
So i'm going to use the text property and make that equal to wise owl movies then maybe we'll have a new line character so we can concatenate a vb new line.
And then let's also have a formatted version of today's date so i'm going to use the format function and then refer to the date and then after a comma type in a date.
Format so i'm going to see the full
Name of the day of the week a two-digit day of the month the full month name and then the year as well.Okay so having done that let's just run our subroutine again and see what effect that has and what we should see of course is the same document but this.
Time on each page of the document a header with the text that we've asked to display next let's just change some basic.
Formatting of the text we've added we won't spend too long on this but just to show you some of the basic things we can do we'll head back to the visual basic.
Editor and then inside the width block for the header range property we can say things like dot font dot size equals 12. we could say dot font dot text color and then i'm going to refer.
To the rgb property and make it equal to then if i press control and space bar on the keyboard and type in the letters rgb i can either use the rgb function.
And define the precise color i want or just pick from one of the named colors in the list let's go with let's go with rgb tomato then we can make the font bold so we can.
Say dot bold equals true and then let's also change the paragraph alignment i want to right align the text in this header so let's say dot paragraph format dot.
Alignment equals wd align paragraph right okay so having done that we can head back to the existing word document and close that one down without.
Saving the changes then we can run the subroutine again and hopefully see a slightly differently formatted set of page headers.
When we look at the final word document there we go right aligned bold text in a different font size and a different color now let's say that we wanted to have a.
Different style of page header for the odd and even numbered pages in the document to do that in word we could double click into the header section and choose the.
Different odd and even pages option to do this in vba is similarly straightforward let's close down the header section and in fact close down that word document without.
Saving the changes and then back in the visual basic editor before we start modifying the header options let's change a property of the documents.
Page setup so we're going to say doc dot page setup dot odd and even pages header footer equals true so this allows us to specify different options for the odd and even.
Numbered pages i think the simplest thing to do here rather than rewrite all of this code is just to copy everything that we've done with the.
Primary header footer and then paste that in immediately below and then change wd header footer primary if i press control and spacebar while in.
These round brackets i can choose wd header footer even pages so the primary header footable control the odd numbered pages this one will control all of the even.
Numbered pages just to demonstrate that this is absolutely working let's uh change the color of the font let's say rgb and cornflower blue and then let's change.
The paragraph alignment to wdl line paragraph left so it should be fairly obvious that we've manipulated the headers independently of course you could change the text change.
What's displayed whatever else you wanted to do but this should be sufficient to demonstrate that our changes have had an effect so let's run the subroutine and we.
Should find that this time when the document has finished creating itself that we end up with a different header on the odd numbered pages that's our original.
Primary header footer on the first page on the second page the first even numbered page the text is in blue and left aligned now we could do a similar thing to.
Affect the page header for the first page of the document differently again if i double click into the header section there's an option for a different.
First page header what i'm going to do to demonstrate that if i close down that word document and i won't bother saving the changes and then again we have to set a property.
Of the page setup of the document first so let's say doc dot page setup dot different first page header footer equals true so what we could do next is.
Similar to what we've just done to affect the even numbered pages differently let's just copy the even page headers section and all the code.
We've written to manipulate that and then we can change the section sorry the header type we're referring to there from even pages we can refer to wd header.
Footer first page we could of course then change the text we're displaying we could change the uh the color to make it obvious let's do something like.