Wise Owl Answers - How do I get data from multiple closed Excel files using VBA?
14.2 هزار بار بازدید -
3 سال پیش
-
Check out our other answers
Check out our other answers here Wise Owl Answers
Download files https://www.wiseowl.co.uk/vba-macros/...
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!
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
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
Download files https://www.wiseowl.co.uk/vba-macros/...
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!
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
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
3 سال پیش
در تاریخ 1400/01/22 منتشر شده
است.
14,282
بـار بازدید شده