In this screencast I am going to be showing
you how to use VBA to extract data from multiple
Excel files so we can consolidate information
that's on multiple sheets into a single sheet.
So for example, we're going to be working
with a central Excel workbook.
What we want to do is we want a VBA sub to
go to different files, and we have information
in these different files.
It's going to open up that file, sort of behind
the scenes, it's going to extract some information
from that file and it's going to bring it
back into the central Excel workbook.
So then it'll open up a second file, take
information from that file, bring it into
the central workbook, it'll open up a third
file, a fourth file, and so on, and we could
do this as many times as we wanted to, but
during each iteration removing some information
from each of those files and bringing it into
the central Excel workbook.
The first thing I am going to do is go over
to Excel and use Alt+F11 to bring up the Visual
Basic Editor.
I am going to insert a new module, and we're
going to name this "Sub ImportantDataFromMultipleFiles()".
The first thing I am gong to do is dim filenames
as a variant.
Filenames is going to represent an array
that's going to be all the files that the
user selects to import data from.
We're going to be doing an iteration, so I
am going to dim i, which is going to be just
an index of iteration.
I am also going to add something here called
screen updating, "Application.ScreenUpDating=false".
The reason for that is this will make it so
that when we're opening up those files and
closing them we are not going to be showing
that in the background.
It basically freezes it and it only gives
us the result, which is the information that
we want, into our central workbook, and then
i am going to select range A1, so cell A1
of our current workbook.
Next I am going to define what this filenames
array is going to be, and I am going to use
this "Application.GetOpenFilename", and we
have a file filter.
This is essentially where the user can browse
for the files that he or she wants to import.
So this is our name, and I need a quotation
there, and we also have a title here, so "Title:="Open
Files(s)" that's going to be the name of the
box that comes up, and multiselect is true,
so that means we can select multiple files,
and the files that we select is going to be
placed into this filenames array.
Now we're going to perform an iteration through
all the files that we're importing for i=
1, 2, upper bound of file names, and we're
going to be doing something.
I am going to put a next i here, but let me
go back and show you we've got five files
that look like this, where we have one word
in cell A1 of each of those five different
files, and their different words, and we're
going to use this to import the name, or the
word that's in A1 into our main worksheet,
each time we're going to increment down by
one cell, so we're going to put those five
words in cells A1 through A5, so that's what
we're doing with this.
Let me close this, and we'll go back.
We're iterating through those five files,
and you could select more if you wanted to.
So in each iteration we're going to open file
name i, we would open file 1, 2 , 3 , and
4 during each of those, we're going to select
range A1 from each of those five files, we're
going to copy that selection, and then we're
going to activate the main filename, so the
main file is going to be called multi importer,
so I need to save this file as multi importer,
so I will do that.
I am going to save that as an Excel macro
enabled workbook, go back to the editor.
The next step, we've activated that main central
file.
Now after we've copied that we've activated
the multi importer, the main sheet where we
want to paste special, so we can use this
selection, paste special, paste Excel, paste
all.
We want to transpose that cause normally if
you paste that it will paste it as a row,
we want that as a column, so we're going to
transpose that, and then we want to go back
over to the file that we're iterating through,
and sometimes it doesn't work just to activate
that new file, especially because filename
is so big we don't just have a specified filename,
so we can use "Workbooks.Open.Filename(i)"
to re-select, or reactivate the file that
we're trying to import data from, and now
we want to close that file, so we're going
to "ActivreWorkbook.Close".
We are not going to save changes, we're just
extracting data.
So we're going to close that workbook, and
at the end of each import we want to offset
the active cell by one row and zero columns,
because then what that does is we're going
to import it into A1, and then at the end
of each iteration we want to bump it down
so it's ready for the next import, otherwise
we're just over riding cell A1 every time.
OK, I am going to comment out this screen
updating, just kind of show you what happens,
and I think we're ready to go, the last thing
I am going to do is insert a button.
I am going to assign that sub that we created
to that button, Maybe I am just going to say
"Import Data" OK.
So I click that and it brings up this open
file box, and you can navigate to your files,
so I've got five files here, each with different
words in cell A1 of those respective sheets.
I am going to open that, and you see it's
going through, it's opening all five of those
files, and it's sort of flashing, and it's
importing cell A1 from all of those five files,
and there's a different word in each of those,
and then we put that in cells A1 to A5.
So that is how it works, and we will turn
this application screen updating to false.
Now I am just going to clear this, you'll see
it looks a little different now because with
the screen updating false it just sort of
runs and then gives us the final result just
in one foul swoop.
That is how we can use VBA to import data
from different files and there's a lot of
different things you can do with this.
Hope you enjoyed this screencast.
