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

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

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 create a connection object and construct a connection string to talk to the closed Excel file. You'll learn how to query the schema of the 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 statement 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:19 Referencing the ActiveX Data Objects Library
02:13 Setting the Connection String
07:07 Getting a List of Worksheet Names
09:05 Getting Field Names from a Recordset
11:56 Looping Through a Recordset
14:00 Extracting Data from Each Worksheet
18:55 Reducing Open and Close Recordset Actions
20:19 Building up an SQL String
23:25 Adding Criteria to the Query
25:30 Using Wildcard Characters

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