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

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

[youssefsedkey]

Your explaining is complete and wonderful, thanks.

[thomaskelley]

I followed your instructions explicitly and it worked great! Thank you for your videos.

[CQXRay]

I have been resistant about doing data extraction stuff outside the usual Excel method (open the source file, copy things over, close source file) because the tutorials are usually abstruse and frustrating. But this video is crystal clear and you go through the steps at the appropriate level of detail and with the right pacing for me to follow. Excellent stuff! Thank you, thank you.

[MasterChelsea]

Sir, your lectures are perfect!!!! THANK YOU SO MUCH!!!!

[ab]

Dear Wise Owl Highly appreciate the way you explain, its just Excellent.

[oleksijm]

I had been looking for this for years.

Thank you so much.

[chmytoj]

Thanks! I have been looking around for these for the whole day!

[emcsquare]

Wow! Just wow! Excellent material and demonstration. I'm going on 60 and been writing VBA and Studio for about 5-6 years now. To see other examples and styles of coding is so enlightening. Thank you!

[janezklun]

Great tutorial, the best vba on YT, Thank you Andrew

[RohithKK-uhpp]

Very nice and interesting. Wonderfully explained. Thank you.

[hadireg]

Greatest tutorial ever on this topic 👍👍

[jassi]

👍🏻Thanks a lot.. exactly what i was looking for. Very nicely explained in simple manner

[gregggordon]

Nicely done. Worked perfectly. Thank you

[safeerahmed]

I have learned excel and VBA using your

[KhalilYasser]

Really amazing tutorial. Thanks a lot.

[Victor-ollo]

Great video! Thanks for sharing and Thumbs Up!!

[naiduvikas]

Very Extortionary and power full . Next month i will get promotion

[andrewwest]

WOW that was really really well explained, thanks so much!

[robertbendkowski]

For this video you get at least one Oscar!

[AmitMishra]

You are amazing. You explained everything step by step 😍.