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.