VBA - working with data videos | How do I get data from multiple workbooks with one query in 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? Even better, if the files you're connecting to have the same structure, you can write a single SQL SELECT statement to select the contents of all the files in one go! 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 build a SQL UNION ALL query to get the data from each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using the SQL statement that you've constructed. 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.

See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.