6

VBA: Data Extraction


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.

Glenn Chapman

6 Comments

  1. Hi great video,
    I am seeing data extract from 1 consolidated file to multiple sheet

    Can you pls help

    Regards,
    Sajjad

  2. i need a macro to send email with Attachments from excel as mentioned procedure  To address in A column   ,  CC address  in  B Column ,Subject -text in C Column ,Attachment  path in D Column ,Message-text  in  E column  and it should save it on outlook Drafts  please do the need full in this regards expecting your best results on this

  3. Hi! I have a message : "Compile error : Variable not defined" while I have put "Option Explicit". Do you know why ?

  4. it doesnt work. i got an error on this line of command: Windows("Multi-Importer.xlsm").Activate

  5. you could have just wrote activecell offset line after paste command it will make program not to navigate back and forth

Leave a Reply

Your email address will not be published. Required fields are marked *