Macro Express Pro Tutorial (5 of 5) - Macro Example

This video is a tutorial for a Macro Express Pro example macro. This is video 5 of 5.

---

Closed Caption:

hi welcome to my macro Express Pro
tutorials
in this tutorial I'm going to show you
an example macro
going to show you how it runs an
and what it does and then I'm gonna go
through each other commands and
described
what's going on ninety watch my previous
tutorials
on the program and for commands macro
Express Pro
the should be relatively small your
ground
so the first thing that i'm gonna do
Simon to go ahead and run this example
macro
by holding control alt
shift E so what you're gonna see
happening this macro
is it's going to open an excel sheet the
contacts excel sheet and there are three
contacts in there
fridge contact its gonna take a name the
city
email address and then a message and its
gonna plug it into a form on a web page
and submit so go ahead and watch that
operate
your ego we open Excel
copying it to the liner each other
cells in the records and refinished
now it opened up the page submitted the
form
closes this form open for next one
submits the next one closes that
open to last one and finely
after it closes tells us how many
messages
percent so let's go ahead and
open up that example macro and have a
look at what's happening
in the commands on second thought let's
look at the variables for a quick
so I've defined three integers here
message and
and then rowing current row well message
and I'll describe a little bit later
what we're doing
to Rohan current row are related to
working with each row in the
excel sheet for each record know we have
text variables
we have name City email message to store
the information that we capture from
Excel you'll notice that these are raise
$100
that means that we can work with up to
100 records from
excel at one time and then we have club
or
will explain why we're doing that a
little bit later
okay back to our script so you'll notice
I started with lot keyboard/mouse
and then near the end I put unlucky
portents
now it's important that I put the
message box after unlock the keyboard
mouse
otherwise use your cat click okay on the
message box
I could present a little bit of a
problem something else you wanna do
is when you're developing a macro if you
decide you want to lock the keyboard
mouse while you're working with a
you wanna include the command flock
keyboard mouse but then you want to
disable them while you're working on a
macro
this will make it a lot more convenient
if something goes wrong or
you misjudged something in your macro
TEM activator launch
we are going to activate this window if
it exists
and if not we're going to open up so
you'll notice in the window title here
I used abstract and I have this contains
wild cards check so that I can use an
ass trick which is a wild card
sheesh and if this window doesn't exist
when we're gonna do is work um
execute Excel we're gonna pass at the
proud owner of the file name
now it's important that I put this in
quotes
and the reason why is because if I
didn't
you would think this was one this was
another
and this was yet a third parameter to
the program why don't want it to be
three primers I wanted to be one
a single file after that
we have Excel open why would I do a
control home
well if you had XO open already and you
were somewhere else on the page
we want to control home to get back to
sell 81 before we start to do the work
she so that's why did that and then I
set
in a churro to zero now what row is it's
going to take count
how many rose we have and if I don't
ever get a row
I wanted to remain 0 so it later on when
I get to
plugging it into the form I can say oh
we don't have any rose
don't even open a browser the next thing
you're gonna do is we're going to
start repeating over the rows in Excel
now I'll bet you noticed that as I
played that macro there were three
records and that stopped after the third
record
despite repeating one hundred times
we'll get to that in a minute why that
happened
as we come in here we're gonna simulate
a keystroke control C
you're familiar with Windows you know
that's copy
why did I use that and said Oh the
clipboard copy command here
well I could use credit card copy
my experience control C is a little bit
faster
but then I come down here and I wait 15
or seconds
no fraction of a second make sure that
we got everything in clipboard before
image
forward sheesh next I'm setting the
variable to the clutter the
clipboard to the clipboard variable why
well look here now I'm stripping of the
control line feed
what I found as I was putting this macro
together is when I copied
the cell had nothing in it
right here still thought it had
something in it
but it didn't really well it did add a
line feed
or a line break will soon as I strip
this control line feeder line break
now report equals quote quote
and wicked exit knowing that there is
nothing in the cell
before it just continued to go for
everyone up the 100 repeats
so with the helps block we know that
there is a record that we're operating
on so the first thing we want to do is
go ahead in
increment row sure member up above we
set it to 0 so now at this point
sure we're going in command that which
means add one to it
so now it's one well go ahead and take
the contents of the current clipboard
which would be that first field that we
copied test to see if there's record
I'm not going to be the name so let's go
unplug that in for the name
well name I'll row what is that
well currently rose 1 so the first
position
in the name array is where we're going
to place
the text that to my clipboard
and then we wanna move a rogue right
that's going to take us to the next
column
in Excel wait 15 all second source make
sure that we got there before we
continue
sheesh and then much copy again give
ourselves a delay
make sure that we got contents and then
could get value in city
the cellular and and we're going to
continue to do this
all the way down through message now
you might think dozens pretty repetitive
maybe we should use a repeat
well we would use repeat except
you're putting in a name here we're
putting intensity here we're putting in
DINO on my message
so that makes a less valuable to trying
to repeat
with message we did something a little
bit different here
what happened as one XL has a
value that is multi-line like the
message that we happen happen at 6 I'll
value
it adds quotes around the text when you
copy it
well we don't want those quotes we paste
them into the form
we need to strip him of so how are we
gonna do that
well the first quotas easy all we gotta
do is delete the first character
but the last quote that's going to be a
little more tricky
we're gonna have to do is we're going to
have to grab the lamb took the stand up
the
value the later the text
and now we're gonna have to back up a
couple positions and then
remove character so that's what we're
doing here we got we go ahead and put it
message ray
and then we say are right how long is a
string
she can see let's get the link to the
text variable
and will play certain end well now we
need to back up a couple of characters
so we're gonna say message and equals
message and -2
so now we are two characters before the
end y tu
then I thought the quote was on the very
end well almost what's
should remember that line break issue we
had earlier
well it applies here as well so if we
back up two characters we back up before
the line break
and the way back up before the court
next
we're going to on our message row start
at our message and which is two
characters before the end and early
one character that would be our quote
now the end is removed let's go ahead
and remove the quote from
first position and remove one character
now are quotes are strapped and let's go
down to the next record in Excel
narrow down home takes us to column a
but leaves us in the row
just below the record we just completed
right now we're done
grabbing all of the records in Excel
will Heller and FNR and repeat and we
will have Loopt
through every record and remember once
we hit a blank record we're going to
stop and leave the
repeat so next what are we gonna do
well next will close Excel
we don't need it anymore and then we'll
check to see if we got any records
if we got some records then we need
repeat
so what are we doing within this Rippy
in this repeat we're gonna open the
website
we're going to still in the former gonna
submit
and then we're going to close the
browser and then we'll do that again for
each record
well how many records so we have what we
don't know
well are variable that we defined does
now
so that's what we're gonna play in here
so it it repeats the number of times for
every record that we have
we'll start with one well increment by 1
and then we applaud that
counter into our current that way we'll
know what we're we're on
the whole way through the slough next
we're going to use the Website command
will plug in the URL
we use our default browser and we're not
going to use the wait for web page in
this command
why because it only works on exploring
we like chrome
soon X we're going to explicitly wat
for this window to arrived and here's
window title were looking for
we're gonna look for it for a maximum in
10 seconds to take stronger in 10
seconds were gonna stop waiting
so what happens if it never does arrive
most say you don't have an internet
connection
well that's what are if there's four in
the air for checking to see if the
window is running
this poor hidden but as long as it's
running and again here's our window
title
and sure we make sure that the windows
on top activate it make sure it's the
current window
and then we'll get to the name field by
using seven taps
now you could use the mouse and position
relative to the window and click on the
name Joe
but I find that the keyboard provides a
little more accuracy
so we're good pace the name value
you can get the variables right down
here it's a convenient way to insert
them
but we don't want to specify a static
number here we want the current row
and then we will tab and pace the city
tab pasty email tab and paste the
message
now we filled out the form we want to
tab down to the
submit button and press Enter
now we wanted to wait about a second in
the house
make sure that it was done submitting
before we close the window
and then we close the window wait
another cordova second just make sure
that we need
have everything in order everything is
good and then
start the process over with the second
record and third record
and so on until they're all complete
finally we want
unlock the keyboard mouse and display a
message that
tells the user how many rows or how many
messages
we submitted via the form
well that concludes my macro Express Pro
macro example
thank you for watching and I hope you
found it useful