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

Check out our other answers here https://youtube.com/playlist?list=PLNIs-AWhQzcluqE43JKakRKslaYwCouJu

Get files here https://www.wiseowl.co.uk/vba-macros/videos/vba-ado-net-recordsets/loop-worksheets-multiple-closed-workbooks/

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link https://www.wiseowl.co.uk/donate?t=1​ to make a donation. Thanks for watching!

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

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Power BI, DAX, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access and more

Видео Wise Owl Answers - How do I loop through sheets in multiple closed Excel files? канала WiseOwlTutorials