Microsoft Excel 2010 - Macros Writing Lesson 4 - How to generate Word Docs from Excel

Please visit http://www.techflutter.com to download the code and see our other macros. We also provide a custom macro service, see our website for more details.

See Macro 9 which is an add-on for Macro 4. It will allow you to automatically enter commas into your currency values in Word.

---

Closed Caption:

hi folks it's gone today is lesson for
and i'll be showing you something quite
interesting today so we have a she here
i made a burner on and we have four
different columns fort with client
number on client name and we also have
two other pieces of information so we
have a total fee which is to be paid
which is a hundred girls and column C
then we have the total and the total
amount of being paid and also in column
deed and the total out that was a
standing so what this macro does is it
will take all this data and for each
each person each client it will generate
a matter according to specific to them
and we're saving a file so what I'm
going to do first is not to run the
macro and i'm going to then go to the
code just to show you how it works
so you can get grass for that as well so
what I'm going to do is I'm just going
to show you this macro which folder
which is the output folder and I'm going
to run the macro here on the left-hand
side so i'll click view macros macros
i'm going to run this one's called micro
so what you see here on the right here
is you'll see and the dlc x-files be
generated for each individual user so
there should be an m16 buzz and total
that's correct or 16 and 16 different
users now bear in mind there's two
different types of output documents are
going to consider her the first one is
for people who don't have any money of
standing on their account so for example
this value here is 0-3 individuals like
that and then for the rest of the
individuals we have turkey roles of them
and they all have money remain on their
account so i'm going to go through two
different and two different and types of
and export word documents
ok folks so i'm just going to show you
the macro name and how it works and i
will hit out at f/11 just to get into
the microcode if you do need a copy of
the same code i'll be more than happy to
give it you just email us are m.tech
folder @ gmail.com or you can follow us
on Twitter attack for no issues at all
ok so this disco does build on the
previous lessons that we looked at and
and we have our submicron defining the
macro we have our application . screen
of death n equals false and we also have
that at the bottom as well
application.screenupdating equals true
ok so about won't let the excel file
refraction at speeds of the process now
several different variables here i have
to find I have counter which is the main
character of the program i also have
total counter which is the which is
going to count up to the very end up to
find the limit of the micro and how long
the micro wrong so as we did before in
previous lessons and we did this section
of code here just it run the total count
to see how many roles and take the loop
mainly people have to go to so it's very
use of we also have names with this will
be the name of the client and then we
have the amount and that is old day off
and also and then client as long which
is and and declined said number of the
client number one so I'm just going to
initialize the variables here i have
counter equals 22 ok so character equal
to is because if you go back into your
xl5 and we want to start here so that's
why counter is equal to 2 we could set
the total count of one it doesn't really
make a difference but we have to set
count or equal to 2 here because we have
this header row
back into the corner and then we have
the main loop of the program so the main
loop of the program is that the main
part here on we have the definition here
heard of criteria for the main aim of
the program it's a doin to counter
equals to $OPERAND total counter so once
the camper is is counted up to the total
counter that this little stock and the
increment here for total counter is down
here so counter equals counter +1 now we
actually have two different em snippets
of code here so i actually went into
microsoft word and i run a macro to
record what I wanted but you have to
enter into some extra code in front of
did the market up is generated from work
for it to work ok so I'm just going to
go through the m2 the create object here
did a couple of six steps you have to do
you have to enter in this code here and
in order to create a word and object so
it's set obj word is equal to create
object word.application and he wanted
set this one as well equals to fall so
that the word them word won't keep a
fraction so to speed up the process
efficient and then you this extra line
of code here set obj doc and equals
always a word
the doctors that are also this last one
here you'll need this is so outset
object selection equals object work the
selection so you can copies straight in
there is no issues whatsoever it's fixed
text the next one it is and you want to
enter the date so this will actually
pull out today's date for you so far you
go into one of the files for example and
let's go into Becky i'll just show you
here so this is the date that the micro
Apollo only pull up today's date is
different format date as well and if you
want to use that if you can record a
micro Microsoft Word and just click and
surf on date and time and pick whichever
one of these you like on then you can
take that code in that code for
microsoft them were that micro coulomb
input a denture excel file the code in
the excess flow if you want to change
that they can
going to close this down okay so we have
the date the next thing is the this this
snippet will this typical them with
generate this will generate them and no
10 so basically putting the text so
we've got some different items here that
would be m and different every time you
have the names which we pulled it from
this hair cells counter is to that will
actually pull out the name free then we
have to try number then client numbers
pull that each time from this encounter
months of sales counter water reason
counter encounters to in reading and 72
and 11 put out the client name and we
also have then the am
this part here which will actually save
the document so each document as you
notice there how to name that is safe so
i'm not using the name the name here to
to assign each document and an
individual named that's good practice as
well and then the next part then is just
to have this part of cold here which is
obj word quick so that'll actually close
Microsoft Word down each time after each
iteration is run
ok so the first snippet of code was for
this letter which is the matter to send
to somebody if they've fully paid so for
example these treat people here they're
fully paid they don't have any
outstanding money on to pay their
account so the letter that will be sent
to them if i take and number two which
is Jack find Jack here so this will be
the letter that will be sent to them so
this is a note to say that your account
is paid in full on the other hand we
have some individuals who haven't paid
so we need to generate a separate letter
for them and these are the people there
who haven't paid and they still have
money and standing on their account so
they lead to have a separate letter to
go back in here to the code so what the
fine start then this this if statement
within the loop so if amount is equal to
0 here so if there's no money basically
standing on their account
we'll run this section this will
generate that the word document just to
say this this is another save your
account is paid in full
otherwise if amount does not equal to 0
so if there is still money outstanding
on their account we run this snippet of
code here so we have exactly the same as
i showed you before just to create the
the word document inserting the date and
then we have and two main snippet them
for snippet of code for and to put in
them amount don't need to put in the
amount of this time we need to put in
the name on also the client number so
the only thing different is this section
of cold here to have the amounted so we
need to say okay your account that is
standing by X and we need to read that
in each time from here so it does it
separately each time and also we will
save two foot just important saved by
the end and also to give it that unique
name for each person I mean you can put
the toy number here if you wanted you
can use on percent as well so just give
it show you an example of one of those
documents so we will pick a person for
example doesn't have money paid let's
pick for them for example for them it
always 85-yard her account i'm just
going to go into fidelis better on this
is your name give your flight number
which is 12 here and she owes a
five-year-old's so you can have as many
as many columns of data here that you
you need to have I'm you can customize
your your em you can customize your RM
your word document template so you just
go into tools or Edward and record a
macro and customize it and remember
Dennis the last bit of cold here just
two quick word
so if you have any further questions you
can follow us on twitter it's off text
folder or you can email us text floater
@ gmail.com
I'm really more than happy to help you
and you'd like a copy of this xml file
in sends an email and we can send and
you