Wise Owl Answers - How do I get data from multiple workbooks with one query in VBA?

[@tejamarneni]

I love it when KZbin 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.

[@WiseOwlTutorials]

Thank you Ravi! Your support and recommendations are very much appreciated!

[@youssefsedkey7616]

Full explanation in utmost magnificence.

[@WiseOwlTutorials]

Thank you for the kind comments Youssef!

[@janezklun]

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

[@WiseOwlTutorials]

Thank you janez!

[@krn14242]

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

[@WiseOwlTutorials]

Excellent Kevin! A satisfying feeling no doubt!

[@KhalilYasser]

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

[@WiseOwlTutorials]

Thank you Yasser, I'm happy that you're enjoying the recent videos!

[@frikduplessis8849]

👏👏👏👏👏👏👏👏 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

[@WiseOwlTutorials]

Thank you Frik! It seems that this topic has been interesting for many people. I'm happy to hear that you're enjoying the videos and thank you for your support!

[@shinrafahell]

Is it possible to INNER JOIN using this technique?

[@frikduplessis8849]

@@shinrafahell HI Rafael Andrew did a brilliant tutorial on JOINTS, UNION'S and some other interesting tips and tricks in this series just browse down 👌

[@HereForTheBrains]

Super interesting as always. Many thanks for the video

[@WiseOwlTutorials]

You're very welcome and thanks, as ever, for the support!

[@초록나무-k8v]

👍Very good, great!!!! Thanks

[@WiseOwlTutorials]

You're welcome, thanks for watching!

[@ericdunham8835]

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!

[@WiseOwlTutorials]

Hi Eric! As far as I know it's not possible to include a password for an Excel workbook in a connection string using the ACE.OLEDB provider. The generally accepted workaround appears to be opening the workbook using either the Workbooks.Open method docs.microsoft.com/en-gb/office/vba/api/Excel.Workbooks.Open or the GetObject method (although this will prompt you for the password) www.connectionstrings.com/how-to-open-password-protected-excel-workbook/ and then use ADO to work with the opened file. Not ideal I appreciate, but I don't know of another method!

[@onurtunc9468]

Great, Thank You so Much

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@khaledseghari1189]

Thank you very much

[@WiseOwlTutorials]

You're very welcome Khaled, thank you for watching!

[@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.

[@WiseOwlTutorials]

Hi Валерий! You can add a calculated column to the SELECT statement to include the name of the file like this: Do Until MovieFileName = "" If IsFirstFile Then SQLString = "SELECT [Sheet1$].*, '" & MovieFileName & "' AS [FileName] FROM [Sheet1$]" IsFirstFile = False Else SQLString = SQLString & " UNION ALL SELECT [Sheet1$].*, '" & MovieFileName & "' FROM [Sheet1$] IN '" & _ MyFilesPath & MovieFileName & "' 'Excel 12.0 Xml;'" End If MovieFileName = Dir Loop I hope it helps!

[@walerij]

@@WiseOwlTutorials Oh, thank you so much! I'll try this.

[@WiseOwlTutorials]

@@walerij No problem!

[@luizhenriquedacostajardim5511]

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!

[@WiseOwlTutorials]

Hi Luiz! You're very welcome for the videos, thank you for watching them. We don't have a video on extracting text from PDF files yet but this topic is on my list of videos to make.

[@luizhenriquedacostajardim5511]

@@WiseOwlTutorials Thank you very much. I'm sure it will be another excellent job. Success! Muito obrigado. Tenho certeza de que será outro excelente trabalho. Sucesso!

[@WiseOwlTutorials]

@@luizhenriquedacostajardim5511 Obrigado Luiz!

[@hajajahirhussain6814]

Hi. I tried this with 29 workbooks total rows around 400000 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.

[@WiseOwlTutorials]

Interesting, thanks for sharing your research!

[@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?

[@WiseOwlTutorials]

Hi Uzman! If you combine the techniques shown in this video kzbin.info/www/bejne/a3ebomqQbtCKfdk I think you'll be pleased with the results! I'll have another video tomorrow which covers similar ideas!

[@johnabram4159]

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.

[@WiseOwlTutorials]

Thanks John, I'll pop that question on the to-do list!

[@timemanon2052]

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

[@WiseOwlTutorials]

Hi! You can see how to use ADODB with late binding in the first 10 minutes of this video kzbin.info/www/bejne/fnacdHyYmtmBpKs You might also find this useful kzbin.info/www/bejne/p56tgZWrl5J3Zqc I hope it helps!

[@timemanon2052]

Thank you Mester WISE for your help and for your amazing videos. Sorry to tell you that l have a problem when i try using creatobject function in your exemple code "Get data from a closed excel file" the code doesn't work.

[@jabinik5865]

Can you do joins from multkple workbooks?

[@WiseOwlTutorials]

Yes! Check out part 58.22 in this playlist kzbin.info/aero/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK&feature=shared

[@shaileshsharma668]

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

[@WiseOwlTutorials]

Hi Shaielsh! Yes, you can use the TransferDatabase method to do this. You can see some examples of the code you need here docs.microsoft.com/en-gb/office/vba/api/Access.DoCmd.TransferDatabase I hope that helps!

[@shaileshsharma668]

@@WiseOwlTutorials thansk sir

[@BachXuanHien]