Wise Owl Answers - How do I loop through sheets in multiple closed Excel files?

Looping through the collection of worksheets in a workbook is a standard technique in Excel VBA and easy to accomplish when the workbook is open. Did you know that you can do the same thing when the workbook is closed? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to loop through the Excel files in a folder, creating a connection to each file using an ActiveX Data Objects Connection object. You'll learn how to query the schema of each workbook to return a list of the worksheets and loop through this list to read the name of each sheet. You'll also learn how to create a recordset object and populate it with data using an SQL Select statement. As a bonus you'll learn about the SQL Union All statment to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.

Chapters
00:00 The Question
01:30 Looping Through Excel Files in a Folder
03:55 Connecting to Each Workbook
09:54 Getting a List of Worksheets
12:28 Returning Column Names from a Recordset
14:45 Getting Rows from a Recordset
18:13 Looping Through an Array
21:05 Constructing an SQL Query
26:52 Selecting Data into a Recordset
29:16 Writing Recordset Data into a Worksheet
33:15 Adding Criteria to the Query

[RohithKK-uhpp]

Fentastic video. I have become master in this now all because of you. Many Thanks.

[bryantai]

Very good explanation. Clear and concise

[tejamarneni]

Thank you for amazing videos on VBA. Andrew is the best!!!

[KhalilYasser]

Amazing my tutor. Thank you very much. Very enjoyable tutorials.

[VillaOuk]

Thank you very much. This is so great.

[bitric]

amazing tutorials! Thanks a lot! Is it possible to write/modify data to a closed workbook as well? Thank you master

[UzmanExcel]

Hi Andrew, perfect tutorial. You always suprised us with great content. But this code gives me error if there is a blank sheet. Thats is an usual case at business life. How we can bypass a blank sheet at workbook while using Ado

[FistDaMonkey]

Hi Andrew, thanks for the videos. Do you know if event procedures in outlook are something that exist? Kind of like event procedures in excel for workbook? I just watched the making excel speak video also and I'm just trying to figure out whether it's feasible to make outlook read the contents of a new email in the event that a new email is received.

[demsnhb]

Thank you for your video!
I have a question, there is a bug here.
If the running environment is nas, and the 'movies 2011.xlsx' file is opened by another pc, 'ado.commectionc->cn.open' will run the file.
How can I fix it?

[dnyaneshdeshpande]

Hello Andrew, thanks a lot for your efforts in creating such high quality videos ! I have a query here : If I want to take only a 1) few columns of data (not all) from 2) only a few tabs (not all) in closed workbooks, how would I go about it?

[aamalster]

Hi sir, many thanks for the video, it's very helpful.
anyway, I have a question, is it possible to put an UDF inside the SQL syntax of ADO?
many thnx again, anyway.

[MrSantoshKm]

Hi Andrew, I love your videos as every time you come with new magic in excel VBA. Just a quick question on this, is there a way to connect with excel which has password. I only find its working once I open my excel file after providing the password. Please share any video or suggestions. Many thanks!

[mr.akshay]

Thank you for the amazing video. Could you please guide how can I import the content of text file between two specified line numbers into excel. My text file contains lines more than allowed in the excel. Let us say total lines are 1978553.
I want to import text from 100023 to 110347.

[ybskumawat]

Hello Andrew,
Just a little question,
I want data from some specific cells only from all tabs like only A1:H2

I am writting code:
SQLString= “SELECT * FROM [“ & SheetList(0, i) & “A1:H2]”

But when I check my SQLstring in LocalWindow then it is appearing like this:
“SELECT * FROM [‘april 2022$’A1:H2]”

And I am getting error.
Please help

[martingregson]

On the second loop the code is opening up the closed file, any ideas why this might be?

[graywolfweb]

Hello. Awesome video. I am a beginner in VBA but that was very easy to follow. I did come across an error that I am not able to correct. The debugger keeps indicating an issue with this line.
MovieFileName = Dir(MyFilesPath & "*.xlsx")
my files are all .xls so I modified it to
MovieFileName = Dir(MyFilesPath & "*.xls")
I still get the error "Bad File name or number"

I am not sure what the issue might be.
Thank you

[frikduplessis]

👏👏👏👏 Andrew you make it look so easy, you have answered so many questions on this topic thank you so so much the array waw 👏, however there is something I'm struggling with, we use a 24hour cycle starting at 7:00 and stop at 7:00 the next day at work to calculate daily downtime, SQL is working from 0:00 - 0:00 😬 how can I calculate the totals from 7 to 7 I hope this is understandable, and hopefully there is a solution.

[smooth_mo_dee]

I receive a runtime error when trying to open rsData

Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’

Can you please help with this?

[shiva]

I am fetching data from another excel file which has rows using ADO, but it gets too slow and some time does not work at all. Is there any solution for that ?

[patrickmueller]

I might be a bit late to the party but does anyone know how to make this working in a .xlam file to be able to use it as an Addin? The connection didn’t fail but no data came through. The same code directly in a .clam worked like a charm! Thanks for your great content!