Wise Owl Answers - How do I get data from a closed Excel file using VBA?

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/copy-from-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!

One way to get data from a closed Excel workbook is to open it first, 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. You'll learn how to create a connection object and construct a connection string to talk to the 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 workbook.

Chapters
00:00 The Question
01:40 The ActiveX Data Objects Library
02:46 Connecting to a Closed Workbook
08:13 Selecting Data into a Recordset
10:33 Copying Data from a Recordset
11:28 Tidying up the Worksheet
12:51 Copying a Specific Range of Cells
13:33 Returning Specific Columns
14:53 Returning Columns Without Column Headers
16:55 Adding Criteria to a Query
18:00 Combining Criteria
19:10 Using Wildcards

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 get data from a closed Excel file using VBA? канала WiseOwlTutorials