Wise Owl Answers - How do I get data from multiple closed Excel files using VBA?

One way to get data from closed Excel workbooks is to open each file before extracting the information, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? This video explains how to do this using the ActiveX Data Objects library. The video shows how to loop through a collection of Excel files in a folder. You'll learn how to create a connection object and construct a connection string to talk to each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using an SQL Select statement. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the closed workbooks.

Chapters
00:00 The Question
01:16 The ActiveX Data Objects Library
02:10 Setting the Connection String
05:43 Getting the File Path
07:37 Selecting Data into a Recordset
10:05 Writing a Recordset into a Worksheet
12:55 Finding a File in a Folder
14:58 Looping Through Files in a Folder
16:30 Adding a Where Clause to the Query
17:50 Adding Multiple Criteria
18:51 SQL Wildcard Characters

[MyAudioBookCompilation]

It's so amazing that you can create powerful tutorials in such a short period of time. Better than the avengers. Lol!

[frikduplessis]

Thank you Andrew for an brilliant article, I also see I'm not the only one that was inspired by your video's, and thanks for the suttle query information 😃 as requested

[zsoltnemeth]

Hi Andrew,
I really do enjoy all Your videos, and I have learn a lot from them. This is my very first comment on any of those, so now I take the chance to say a really big-big and respectful THANK YOU for all the efforts You've put in all these videos! I am a huge fan of these.

I have just put my question on an other video, but I've found this one at the same time, which is exactly the answer to my Q. So I deleted it.

But I think You still deserve the compliment! :)

Greetings from Hungary!

[AnilKumar-vioe]

Great content as always, keep this series going on.... I wish it will be good if you share a video daily

[KhalilYasser]

Really brilliant and amazing. Thank you very much.

[sundaynomad]

Appreciate you removing the twilight saga ones! Many thanks for the video (all the videos) WiseOwl. Have a fantastic day

[Robertyaro]

Thank you very much Andrews. I can't tell how you've been making me an Excel expert (at least in my environment). This tutorial came right in when I needed it, and I'm more than grateful. Yet I'm still a bit hungry for more: how do I make it work when the data are in protected worksheets? Your answer will save my life. Have a great day!

[schaunoakes]

Hello Andrew
Thanks soo much for the amazing videos .Your teaching style makes understanding a lot easier. I'd like to know if there is a video that shows how to Transfer Data from a Master File in a workbook to multiple other workbooks if a condition is met.
We are eternally grateful!
Shaun

[dangelorrrr]

An excellent tutorial! Thank you!
Question: Is it possible to use this code without referencing the ADO library?

[HarshitSharma-gcjb]

Hi Andrew,
This is one of the best and simplest explanations I've encountered so far. Thank you so much for this.👌👍
Can you guide on how can I modify the code to fetch a specific cell range (e.g. C13:S38) from a common worksheet (Sheet2) of all files?

[philmchunt]

Hi. Great videos as always although I am struggling atm..
can this be adapted for multiple individual cells (not 'currentregions' and sheets of a closed workbook to defined cells/sheets of my open one?
Many Thanks

[martingregson]

Great tutoral Andrew, as they all are. Quick question is there a way to get around the error "odbc data source name too long"?

[mbahdjojo]

Thanks Budddy, you have a great work!!!
I am an old man, so my eyes very very have hard work to watch what you wrote. Can you make the font bigger as big as possible you comfort with that?
My eyes will more enjoyed with that ... have a good day buddy
Thank you so much

[queenb]

can you create a keyword search with multiple closed excel files, extracting the results of the keyword search into a new sheet. For example, I have multiple sheets (huge data within each file) with same headers. Similar to what you've done here with the multiple closed excel files, is it possible to create a keyword search of each closed file, and list the results in a new sheet? thank you, love your tutorials!

[chandrashekhartiwari]

Its really very Nice video, I have tried to create the connecton but getting Run Time error stating External Table not in expected format. Please help me and assist with the error

[imranbhatti]

Hello Andrew! An awesome video like always. Yes, there are questions. 1) How can we print the Source Filename with each line of the record, so that we can have an idea of which record is from which file (just like PQ)? 2) Can we use the full power of the SQL Queries via this library like, Inner join/grouped by, etc?

[londam]

Hello, your videos are awesome! The method of explaining is just right.
I have a question though. I have a slightly different problem. I have to copy a certain cell value (say "SheetName"!C32) from about 2000 workbooks and I need to paste it all into one workbook (1st column workbook file name, 2nd column cell value". How would I do that? And the kicker is, not all workbooks in the folder have the sheet (so I have to put them in 2nd column in red and leave blank).

I was looking online and everyone recommends ExecuteExcel4Macro(argStr), but I have problem with it that when string argument of that function is more than 255 chars, it encounters error. I see you're doing it with ADODB, so I guess that could be used in my example as well?

I managed to solve everything and is working for files which argStr is less than 250, but I still have about 500 workbooks for which this doesn't work due to error mentioned above. Can you please help me out?

[ljcaclin]

First I have to tell you that you changed my career, thanks to your video I got the VBA skills that allowed to reorient my career to VBA developper in finance (I have a master degree financial engineering).
I have 2 questions for you: - Is it possible to store those data directly in an array WITHOUT copying them first on the worksheet ?
- Will you (Please CAN YOU) start back your videos on C# and go into more advanced programming ? (there are so many tutorial videos out there, but truth is you're really the best for teaching it, and I mean it, many ppl have already told you so)

[arvinthnair]

Thank you. This is very helpful. Can you show me how i could get data from an excel file that is stored in SharePoint (via link)?

[RohitJindal-ugsc]

show the video and it is very good i got what i wanted exactly but i have few modification can you help me out.

how can i add cell reference for MovieFileName and in rs Source in condition where a cell value is defined so as we change a cell value condition changes accordingly.