►
Description
Check out the source code of this project and other projects at this site:
https://github.com/sumurthy/officescripts-projects/tree/main/Run%20Scripts%20for%20All%20Excel%20Files%20in%20Folder
(Note - use table.getRowCount() API instead of the one I'm showing in the video to check the number of actual data rows in the table)
Chapters
0:00 Introduction
1:50 Use Recorder to capture actions
3:00 Edit script
8:48 Setup Power automate flow
A
A
A
So,
let's
suppose
they
they're
uploaded
here
through
some
other
process,
so
they
somehow
come
here
and
on
a
daily
basis
or
it
could
be
based
on
any
other
trigger
on
a
frequent
basis,
have
to
go
in
and
apply
a
set
of
automation
tasks
for
each
of
these.
Now,
if
you
can
imagine
an
individual
having
to
do
this
manually,
it
could
take
a
lot
of
time.
A
A
So,
let's
suppose
the
goal
here
is
to
calculate
based
on
maybe
the
new
discount
rates
that
may
have
been
applied
and
then
highlight
the
amount
you
of
one
of
these
rows
that
have
the
highest
value
and
then
inform
a
colleague
of
years
to
actually
take
a
look
at
that
review.
That
now
to
do
this,
I'm
going
to
use
the
automate
tab.
A
So
this
is
a
way
through
which
I
can
access
the
the
record
actions
feature
as
well
as
I
can
then
go
and
edit
that
script
to
perform
the
automation
that
I
that
I
want
to
do
further.
So
the
end
goal
for
me
is
to
highlight
this
and
add
a
comment,
so
I'm
gonna
start
out
by
recording
the
actions
and
I
will
go
apply.
Let's
say
on
one
of
these
cells:
it
doesn't
matter,
I'm
gonna
apply,
yellow
color
and
then
I'm
gonna
add
a
new
comment
and
inform
one
of
my
colleague
and
say:
okay.
A
A
All
right
so
so
to
begin
with
so
here's
a
script
that
was
recorded
for
me
and
it's
performing
the
action
on
the
the
table
and
applying
the
the
formatting.
And
then
it's
inserting
a
comment.
So
to
begin
with,
I'm
going
to
first
ensure
that
I'll
run
this
only
if
there
are
a
set
of
records
to
to
perform
the
action.
So
to
do
that
I'll
say
table
one
get
range
between
header
and
total.
A
A
Simply
return
it
so
that
way,
it's
not
going
to
go
further
further
than
this.
So
that's
good
now
to
begin
with,
I'm
going
to
apply
calculation,
so
I'll
say
get
application.
So
this
is
the
object
where
I
can
perform
the
calculate
function
and
it
takes
an
enum
of
calculation
types
I'm
going
to
perform
full
calculation,
so
this
is
just
one
of
the
options
that
excel
offers,
so
I'm
going
to
perform
full
calculation
now.
A
The
reason
I
have
to
do
that
is
because
this
is
in
a
manual
mode,
and
so,
if
I
have
to
update
the
discount
you'll
see
that
the
amount
doesn't
change
so
because
of
that
I
have
to
do
this
calculation
and
now,
as
you
can
see,
the
amount
use
in
a
separate
column.
So
I
have
to
go
find
find
the
the
amount
you
that
is
highest
in
this
in
this
data
rows.
A
First,
I'm
going
to
get
the
the
amount
due
column,
so
this
is
get
column
by
name
on
the
table
and
from
that
column
I'm
going
to
extract
the
range.
So,
as
you
can
see,
there
is
also
a
total
draw.
I'm
not
interested
in
this!
I'm
only
interested
in
this,
so
the
amount
due
values
on
that
range,
I'm
going
to
get
the
values.
So,
at
the
end
of
this
line,
the
amount
due
values
which
is
a
two-dimensional
array,
will
have
now
one
item
for
each
of
the
the
rows
and
within
each
one.
A
So
each
of
that
itself
is
an
array
which
has
just
one
entry
and
they
contain
the
values.
So
to
do
that
to
to
figure
out
which
is
the
highest
row,
so
I'm
going
to
assume
that
the
the
first
row
in
that
in
the
in
that
data,
the
between
the
the
header
and
the
the
totals
row,
the
first
one
has
the
highest
value
and
then
I'm
going
to
assume
that
it's
you
know
row
zero.
A
A
If
the
current
row
value
is
greater
than
the
value
that
I've
saved
up.
If
so,
I'm
going
to
just
save
the
highest
value
so
that
I
can
compare
that
for
the
next
iteration,
I'm
going
to
say
the
same
with
the
row
value.
So
at
the
end
of
this
loop
I
have
the
the
row
number
at
which
the
highest
amount
you
recite
and
now
so
here
I'm
gonna
change
this
line
here
for
the
formatting.
So
remember
this
is
coloring.
A
The
the
cell
to
be
yellow
so
instead
of
zero,
which
was
hard
coded,
I'm
gonna,
change
this
to
row
so,
which
is
what
I
have
from
this
calculation
that
I've
just
done
now
on
for
the
adding
the
comment,
so
it
is
done
through
workbook.
Add
comment
api,
so
the
very
first
parameter
that
it
it
accepts
is
the
the
the
cell
address.
A
Now
I
can
use
the
the
same
three
lines
here,
which
returns
the
actual
range
and
instead
of
hard
coding
it
I'm
gonna
dynamically
use
that
and-
and
I
think
that's
about
it-
that's
all.
I
need
I'm
going
to
run
this
just
to
make
sure
it
I'm
going
to
save
this,
and
I'm
going
to
run
this
and
make
sure
it's
doing
what
it's
supposed
to.
So,
as
you
can
see,
it
applied
that
so
just
to
confirm
so
I'm
going
to
make.
Maybe
I
run
this
again
I'll.
A
Remove
all
right,
so
it's
doing
what
it's
supposed
to
now,
I'm
happy
with
this
script.
My
next
task
is
to
now
go
run
this
across
all
the
other.
You
know
files
that
I
have
in
this
folder
right.
So
once
I'm
happy
with
a
given
file
I'll
now
go
use
power
automation
to
achieve
this
step
so
to
power
automate
this
flow.
A
I
will
start
out
with
a
new
instant
flow
and
I'm
going
to
find
out
for
the
time
being,
I'm
going
to
say
manually
trigger
this
and
you
can
choose
the
trigger
to
be
whatever
like
any
time
the
file
appears.
It
could
be
time
of
the
day
or
some
other
condition,
so
I'm
going
to
create
a
new
step,
I'll
look
for
onedrive
business
and
if
it's
on
a
sharepoint,
it's
identical,
except
that
you'll
look
for
a
sharepoint
action,
and
so
my
next
task
is
to
list
files
in
that
folder.
A
Now
I
have
saved
all
these
files
in
a
folder
called
sales,
so
I'll
go
look
for
the
sales
and
and
then
I
want
to
compare
or
rather
make
sure
that
I'm
running
the
the
script
that
I
just
wrote
only
in
excel
files.
So
what
if
there
is
like
a
text
file
or
a
word
document
or
something
else
that
might
be
in
the
folder,
so
just
to
make
sure
that
I'm
not
running
the
script
on
an
in
on
non-excel
file?
A
I'm
just
gonna
do
a
condition
to
make
sure
that
I'll
pick
the
the
name
attribute
and
I'll
say
only
if
it
ends
with
xlsx,
and
if
you
have
a
macro
you
could
also
do
xlsm
or
xlsx.
You
can
add
a
condition
that
says
you
know
if
it's
you
know
either
xlxx
or
xlsm,
so
you
can
do
that.
You
can
also
run
office
scripts
on
a
macro
file.
A
So
if
it
satisfies
the
condition
now,
I'm
going
to
pick
the
excel
online
business,
and
so
this
is
the
key,
the
run
script,
no
other
action
within
excel
actual
excel
workflow
can
achieve
this.
So
only
run.
Script
can
do
this
for
you
so
here
and
it
is
bit
redundant,
but
you
still
have
to
pick
that
hey.
A
You
could,
you
know,
give
it
a
name,
that's
more
meaningful,
but
for
the
time
being,
I'm
just
going
to
pick
script
46
from
here
all
right
and
then
simply
save
it.
So
if
the
file
doesn't
end
with
xlsx,
it's
not
going
to
do
anything.
So
that's
fine,
so
I
will
save
this.
So
this
has
given
me
a
workflow
that
I
can
now
test
so
I'll
test
this
manually
to
make
sure
that
it
works
correctly.
It's
going
through
all
the
permission,
checks
and
now
I'm
just
going
to
simply
run
this.
A
All
right
so
now
it
is
going
through
each
of
the
excel
files
and
applying
applying
the
script
that
I
just
I
just
wrote.
So
what
it's
doing
is
it's
opening
up
each
of
those
files
behind
the
scene.
It's
running
the
the
script
and
the
key
advantage
here
is
that
the
users
don't
have
to
do
this,
so
you
could
have
it
set
up
so
that
you
have
let's
say,
series
of
files
that
are
in
manual
mode.
A
You
can
apply
calculation,
it
can
apply
formatting
and
at
the
end
of
the
flow
you
can
have
it
maybe
inform
you
through
an
an
email
or
could
send
a
teams
message
so
pretty
much
any
workflow
that
you
can
use
in
power
automation.
You
can
use
that
to
inform
yourself
for
the
time
being,
though,
I'm
just
going
to
make
sure
that
it
it
ran
successfully.
A
It
looks
like
there
were
three
files
spawn
so
for
each
one.
It
applied
the
action,
so
here
I'm
just
going
to
go
refresh
each
of
the
files
and
and
show
that
it
is
doing
what
it's
supposed
to
all
right.
So
you
can
see
that's
the
highest
amount.
It's
formatted!
It's
applied!
The
comment
same
thing
with
the
other
file
and
then
the
one
here
as
well,
so
three
files
same
results.
A
One
single
script
has
helped
me
achieve
the
automation
using
a
simple
set
of
power,
automation
and
office
script
steps.
So
hopefully
this
was
useful.
So
if
you
wish
to
see
more
such
videos,
please
leave
a
comment.
If
you
like
this
video,
please
subscribe
and
if
you
have
any
questions,
please
do
leave
a
comment
and
in
the
in
the
description
I
have
the
source
code
for
the
script
that
I've
used.
So
please
do
find
that
link,
and
hopefully
this
is
this-
is
going
to
help.