Wise Owl Answers - 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.

Chapters
00:00 The Question
01:22 Connecting to One Workbook
06:38 Populating a Recordset
08:37 Writing Data to the Worksheet
10:29 The UNION and UNION ALL Keywords
11:48 Selecting from Multiple Files with One Connection
14:02 Finding an Excel File in a Folder
17:16 Looping Through Files in a Folder
19:18 Building a Select Statement in a Loop
23:24 Returning the Results to the Worksheet
23:56 Sorting the Query Results
25:04 Adding a Where Clause to Filter the Results
26:30 Adding Multiple Criteria

[tejamarneni]

I love it when YouTube sends notification about your video. Awesome video as usual. I wish more people would know about your channel. I recommended your videos to my friends and colleagues and continue to do so.

[youssefsedkey]

Full explanation in utmost magnificence.

[krn]

Great Andrew... Got some good ideas and tried my own example.. It worked thanks to you...

[janezklun]

Great, interesting video, the best on YT, thank you Andrew

[KhalilYasser]

Amazing tutorials as usual. I am waiting for your videos daily.

[frikduplessis]

👏👏👏👏👏👏👏👏 brilliant video Andrew, thank you again for a very clear explanation of a very interesting topic, I can't wait for the next one, God bless

[HereForTheBrains]

Super interesting as always. Many thanks for the video

[ericdunham]

Love all your videos. The timing of this one is perfect for a project I'm working on. One additional thing I need to do is to pass a known consistent password to open the different workbooks that are being connected to. I've been searching for the syntax to add the password to the ConnectionString. Do you have any recommendations for examples to reference? TIA!

[walerij]

Thank you very much for these videos! Very good explained. A question: how can I detect which file the selected data serie is coming from? I have many many files and I suppose that some WHERE-condition is wrong, but I can`t find out where / which files returns the "wrong" data.

[luizhenriquedacostajardim]

Hi,
Thank you very much for the lessons. They are all excellent and have great content for use.
I would like to know if you have already made a video that teaches you how to consult and extract texts (not tables) from files in PDF format.
Thank you very much!

Olá,
Muito obrigado pelas lições. São todas excelentes e de grande conteúdo para utilização.
Gostaria de saber se já fez algum vídeo que ensina a consultar e extrair textos (não tabelas) de arquivos em formato PDF.
Muito obrigado!

[hajajahirhussain]

Hi.
I tried this with 29 workbooks total rows around with no extra criteria just "Select * from sheet1$".
But the time consumption is much longer than normal do while loop with Dir() function.

The ADO query takes 1.29 seconds to complete the process.
But the normal Do While loop takes only 30 seconds to complete the process.

[johnabram]

Hi Andrew, I have a question too. Would appreciate if you can make a video on it.
How to perform Mailmerge in MS Word with the selected record in Excel file. It is so easy to do manually but, could not figure out how to do it in Excel VBA. If possible, the Mailmerge also updates an image based on selected record. For example, picture of a student.

[UzmanExcel]

How to be make this code more dynamic such as collecting data from sheets which names are not like Sheet*
Because, if i understand correctly this code does not work if sheet name is different from Sheet1, am i right?

[jabinik]

Can you do joins from multkple workbooks?

[timemanon]

Thanks you for your greats vedio, l wish if you can make for us a vedio about adodb (ado) using late binding vba.

[shaileshsharma]

Hello Sir,
Can you please help me how to link one Access database to another Access database using Excel VBA