0:00
hello everyone my name is Barbara
0:02
welcome back to my YouTube channel today
0:04
I'm going to show you how to get a list
0:07
of file names from a folder without any
0:11
VBA or macros we're actually going to
0:13
use Power Query it's really simple but
0:16
really powerful i know you're going to
0:17
enjoy it so let's get started so the
0:20
first thing you need to do is open Excel
0:21
if you haven't already got it opened and
0:23
go to the data tab this will show you
0:26
the get and transform area which is
0:28
where we're going to be working click on
0:30
get data from file and then we're
0:33
looking to take a list of files from a
0:36
folder so click on folder
0:39
now you need to navigate to the folder
0:42
that you want to look at uh mine is on
0:44
my desktop and it's called Excel so I'm
0:47
just going to navigate to that and open
0:49
it up and already we can see there's two
0:53
files in there Excel files and it gives
0:56
the folder path as well and some other
0:58
information when it was created modified
1:01
accessed etc the next thing to do is
1:06
and that will load that data into the
1:08
Power Query editor a really powerful
1:10
interface but we're going to do
1:12
something just really simple today so
1:14
you can see that we've got many columns
1:16
so I only really want the name and let's
1:19
take the date it was created and also
1:22
the folder path now what you can do is
1:24
you can rightclick and remove or remove
1:26
over the columns i tend to use this
1:28
little drop down here and choose columns
1:31
gives me a nice list of columns that I
1:33
can select which ones I want to keep so
1:36
I want to keep the name created and
1:39
folder path so that's a nice little neat
1:42
set of data that I've got the next thing
1:45
I want to do is hit close and load and
1:48
load this information into the Excel
1:52
and I want to show it as a table in the
1:54
existing worksheet let's just pop it on
1:58
and you can see that immediately I know
2:00
what Excel files are in that folder
2:04
which is great so you're probably
2:05
thinking well what if another file you
2:08
know you want to monitor if another file
2:10
has gone into the folder well all we
2:12
need to do is let's drop
2:16
let's drop an extra file into the folder
2:24
so this is great if you're expecting
2:25
files from people and you want to keep
2:27
an eye on it and you you don't want to
2:29
keep navigating to the folder to have a
2:31
look what we can now do is you can
2:33
probably guess we can go to the queries
2:35
and connections and hit refresh all or
2:38
you can actually just rightclick and hit
2:39
refresh and there you go the data has
2:43
updated it has gone and fetched the
2:45
updated list of files from the Excel
2:49
from of Excel files from our folder
2:54
no code no VBA you can let this run and
2:57
just keep refreshing it and you will
2:59
know when a new file has been dropped
3:02
into your folder i hope you enjoyed it
3:04
if you did consider subscribing 90% of
3:07
my viewers aren't subscribed it really
3:09
does help the channel and if you hit the
3:11
notification bell you'll get notified
3:12
when I upload a new video give it a
3:14
thumbs up ask a question in the in the
3:16
comment box below i answer all the
3:18
questions and hopefully see you next
3:19
time for another Excel video thanks for