How to use ADO and VBA to Read from Worksheets

Подписаться 85 тыс.

Просмотров 76 тыс.

50% 1

How to use ADO and VBA to read from worksheets
The one big advantage of ADO is that is allows to easily read data from closed workbooks.
So what is ADO. ADO is ActiveX Data Objects which help use to read from databases and other data sources. It is a programming library that does all the hard work for us as it provides a common interface for us to talk to all our data sources.
In this video I am going to show you the different ways you can harness the power of ADO with lots of examples.
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO HERE: bit.ly/3GWkgfK
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)
Excel VBA Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Related Links
Connection Strings (www.connectionstrings.com/ace...)
VBA Class Modules - The Ultimate Guide (excelmacromastery.com/vba-cla...)
Using ADO and SQL (bit.ly/2Sk9Qjs)
Useful VBA Shortcut Keys:
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Explorer Window.
F4: View the Properties Window.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).

Опубликовано:

19 сен 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 192

@darkfelony 4 года назад

I am learning VBA alongside your videos and you are an enormous help.

@houstonvanhoy7767 3 года назад

After watching this video and a few others, I wonder whether Sir Paul is the only person creating technical content and teaching courses who defines ADO and other alphabet soup without just assuming that everybody knows what the heck it is. Thank you, Sir Paul.

@maciejdolinski1036 4 года назад

Great video (as all ones from you). You expand my VBA knowledge every time!

@homejonny9326 4 года назад

Why is this fantastic Excel resource soo overlooked?? OMG, we can SQL data Worksheets!!! Even from a closed workbook!!

@Excelmacromastery 4 года назад

Thanks Sergio

@stefanogattoCH 3 года назад

Thank you very much! This is clearly explained, simple and powerful!

@MrVicmanr90 4 года назад

Your videos are awesome for learning VBA. Thanks for sharing your knowledge.

@lastdance903 9 месяцев назад

I like the difference sources handling at 8:54. New technique learnt, thanks.

@benjaminhinojo9998 4 года назад

Most helpful VBA content on internet so far!

@Excelmacromastery 4 года назад

Thanks Ben

@dejanhense9455 4 года назад

Awesome video. ADO explained very simply. Greetings from Germany

@michaelmaguire6709 4 года назад

Paul, your RU-vid videos and blog articles are excellent. The best out there IMHO!

@Excelmacromastery 4 года назад

Thanks Michael

@ibrahimezz4317 4 года назад

You are amazing just hopping for free complete serious that would help us all with amazing work you do

@mtotowamungu8259 4 года назад

Thank you for this video and your social engagement. This is the best ADO introduction I've seen so far. I'm really looking forward to your next video.

@Excelmacromastery 4 года назад

Thanks

@claychan6389 4 года назад

much-awaited,thanks!

@karnabudhathoki5311 3 года назад

Thank you Paul... I have successfully used my first ADO from your tuts....Thanks ...a lot from Nepal......

@maciejgrabowski3393 4 года назад

You are the best, thanks!!

@bouimizarmohamed3240 2 года назад

For all the VIDEOS, and continued efforts that you have made in my education, I will always be thankful.

@Excelmacromastery 2 года назад

Thanks😀

@aleksandrshvechkov4453 4 года назад

Very nice lesson, thank you!

@Mandelbrot567 4 года назад

Excellent video, and thanks for providing the Excel file to download, this really helps .

@homejonny9326 4 года назад

Yes, type yourself the code helps learning in theory, but, in practice, I do prefer copy paste the original code, understand its fundamentals and then modify it as to my own needs. :)

@GabrielCagna 4 года назад

amazing video! thanks so much for sharing it!

@samivirta1250 4 года назад

Thanks a lot for sharing, very useful! I might replace Power Query solution with this , in one specific case - needs more testing.

@homejonny9326 4 года назад

You, Sir, are amazing!!

@sasavienne 4 года назад

Thanks in advance Paul. Waiting impatiently :-)

@directiva108 4 года назад

omg this was so simple and they never told me! now everything is clear and simple 8l

@DataNovelties 4 года назад

Brilliant; very lovely.

@bondniko 3 года назад

Another jewel in my collection

@albertoghiglia9566 4 года назад

Good job! Very professional video. Pls continue in this direction!

@Excelmacromastery 3 года назад

Thank you! Glad you like it.

@adamploof3528 4 года назад

Fantastic, thanks for posting!

@Excelmacromastery 4 года назад

Glad you like it Adam

@lopher70 4 года назад

Great! I loved this solution, I will implement it is several crucial process in my job Regards from Mexico 🇲🇽

@homejonny9326 4 года назад

Me too, I will do the same, in Brazil!!

@thearchibaldtuttle 4 года назад

Exciting!

@alvarojanioravichaguavitor408 4 года назад

Thanks a lot!

@dallas9101 3 года назад

Thanks a lot for your video, you are a master!!!

@Excelmacromastery 3 года назад

Glad you liked it!

@ouzytheoriginal 3 года назад

thank you sir, its very helpful

@sasavienne 4 года назад

You are one of the best VBA teachers who show how to usefully and effectively use VBA using practical examples which are stunning. I know how to create connections using power query, but learning such a VBA code is excellent. Thanks for sharing.. 🌟 🌟 🌟 🌟

@Excelmacromastery 4 года назад

Thanks Salim

@nax9225 Год назад

Amazing sir 👌. Very easy to learn 👍

@MrLawlorterry 3 года назад

Fantastic. This is great. I will be able to some a data migration headache with this method.

@Excelmacromastery 3 года назад

Glad it was helpful!

@kamalam29 3 года назад

I didn't know that we have to use the connection strings for both workbooks when we work with multiple workbooks. that's why I was keep getting not supported table format error. thanks, paul again.

@antonioravenna 3 года назад

thank you very much for the information.

@Excelmacromastery 3 года назад

Glad it was helpful!

@boomer5015 4 года назад

Interested to see how to interact with Excel Tables with ADO, hopefully that gets covered.

@Deepak_Singh1008 2 года назад

Superb.... It's really helpful

@bestscenes1469 4 года назад

Great work.

@Excelmacromastery 4 года назад

Thanks Daniel.

@krn14242 4 года назад

Thanks Paul.

@Excelmacromastery 4 года назад

You're welcome.

@dildokafir5058 4 года назад

Hi paul, Thanks a ton for this short & beautiful content. Is there a way to include multiple excel files in zip folder using vba.

@KhalilYasser 4 года назад

You're awesome. Thanks a lot for awesome videos

@Excelmacromastery 4 года назад

Thanks Yasser.

@JStruthers89 2 года назад

Thanks for this! Can you should how to retrieve specific named range cell values from different worksheets in a closed workbook?

@frikduplessis3869 4 года назад

I can't wait,

@jeffwright6616 4 года назад

Currently managing the build of a VSTO Excel add-in that is using ADO to interact with SQL Server data (and creating a dynamically generated Task Pane for user criteria entry). Interested in seeing your techniques. Have been thoroughly enjoying your uploads.

@Excelmacromastery 4 года назад

Thanks Jeff

@johannesvanderzwan7122 4 года назад

Thank you very much Paul for the EXCEL Videos. It would be nice if you could add the files you are using as an example.

@Excelmacromastery 4 года назад

There is a link to the files in the description

@dainluso 2 года назад

Great video again, thank you Paul. Is there a way to assign the recordset directly to an array without copying it first to a worksheet?

@reeteshism 3 года назад

Hello there. Its a really nice video which you have made, crisp and clearly. I would like to ask, can ADO be used to extract or copy data from multiple workbooks in a folder into a master file, without our opening all those workbooks?

@tsaodon1786 4 года назад

Great video, one problem for using ADO is that you have to select the relevant VBA Projects under Tools/Reference menu.

@tommyfilano3970 4 года назад

Great video! Is it possible to join several sources to one table by using the sql syntax?

@SquatFull 4 года назад

I have used ADO and VBA to connect Acess Databases and Excel worksheets for years. Once I set up the Microsoft ActiveX Data Object on the data user's computer, the Excel workbook VBA code retrieved data from Access. Data was also sent to Access by VBA code.

@mikhailk5690 4 года назад

Hello Paul, your videos are fantastic! In one of my projects SharePoint lists are used as a database. Although I think it is very easy to set up such "databases" there is so little information on the Web about ADO connections to SharePoint lists. I think it will be interesting for those who have a need in a database but don't want to struggle with IT to request and manage one.

@Excelmacromastery 4 года назад

Thanks Mikhail

@TheUnicleo 4 года назад

quite interesting! never used ADO on Excel data before! what kind of SQL can ADO use for queries? Can we make more complicated queries such as joining two or more tables / sheets? Can we use Excel tables as SQL table? I appreciate telling us more and giving a reference to study!🙏

@nadeemsalim807 2 года назад

Very good tutorial, something new to learn. Can you upload the correct source code file please? The one I have downloaded is different! Thanks

@Laxmanmane007 4 года назад

Nice video please make video on web scrapping

@robertmarzec2855 4 года назад

Super video! How do you reset the out without using the Delete rows command for Insert Into mode query? Solved: ClearFormats :) But why ClearFormats I do not know.

@trinhthanhlinh150 4 года назад

I am waiting for your released video

@1234zztechman 3 года назад

Nice video. Thanks for Sharing. Could you please let me know, for excel as database, queries follows MS Access SQL dialect or we can use SQL server queries as well? ,

@atiqsarwar4222 4 года назад

Hello sir, hope you doing well, can you tell me how to data grid view programming

@thanhdoquang7830 4 года назад

This stunning when combine insert and select statement. I wonder if we can combine SQL update and select statement?

@thanhdoquang7830 4 года назад

I have tried this combine Insert and Select but it error the syntax. The difference here is I openning the souce file and try to insert into the database file. Pls help this case.

@ajinkyaankush3648 2 года назад

Thanks a lot for your valuable information on ADO. Just a quick question, how can we write data back to closed workbook using ADO??

@duongsamvanna1830 3 года назад

Hi Master Can you make a video show us how we can download OLE object from access using ADODB object ?

@TheDinga1977 2 года назад

Can you use this technique to create a reccordset from an excel workbook stored on an external website?

@jithinrg100 4 года назад

Is it possible to create a userform which reads excel/txt file and show as grid view preview. And I have a standard list of columns names which should display in that user form and drag and drop those standard fields to each respective columns in the grid view?

@sammir6859 4 года назад

How can I get ADO to work when the workbook I am trying to connect to is already in use? Also, can it return named Excel tables (not just a named range) within a worksheet?

@imranbhatti8580 4 года назад

Hi Paul! Thanks for this great video. Can we retrieve data from a closed csv file likewise?

@Excelmacromastery 4 года назад

Yes. As long as it is in record format.

@awadhbinamro948 4 года назад

thanks a lot wished you included this work file

@Excelmacromastery 4 года назад

In the description.

@insane_rana 3 года назад

Thanks for your videos, its a great help. However, is there any way where we can reconcile two files basis one column to check if all values are available in both files. Example: doing vlookup in one file with another file and vice versa. I have to run the loop ~44 times and new workbook to be opened with new loop.

@marianraab2384 3 года назад

Many thanks for your tutorial it helps a lot. To find out the right search query string is for a non SQL user very tricky, but if you got it done then it is awesome fast. 10x faster compared to my "old" method to opens the worksheet in background and read the data and close it again. And all without any trouble if the "other" worksheet is open in parallel by somebody or myself. Can I search in a specific range and get as return the same row from a other range, without run a loop through the first one and then a loop through the second with the row count? (Similar like a vlookup or xlookup) Worse enough I don't have headers like "Fruit" from your example.

@longjohn7070 3 года назад

Can I do it on a excel inner table? if my sheet include multiple tables

@prasitkeng001 4 года назад

I have read the code of my colleague and the connection string that connect to the Excel worksheet is "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" Thisworkbook.FullName Is this the same connection string as what you show in this video? Anyway, thanks for making this video. The explanation about the ADODB connection is so clear.

@MykeMarvelous 4 года назад

Great

@Excelmacromastery 4 года назад

Thanks Michael.

@ilyastrojnov7627 4 года назад

Sad, what Microsoft drivers for ado to old and dont support function like full join. I have much problem with excel/VBA/ADO/ Access with big data, and now i happy use Excel/VBA(vbs)/ADO/oracle.

@jimfitch 4 года назад

Hi Paul, Great tutorial on ADO. Best I’ve seen. I did quite a bit of it in Excel & Access some years back (Excel & Access 2007 & 2010), then shifted to other things. Getting ready to replace those old programs with modern code (including Power Query, etc.) & migration of all content from file server to SharePoint. Also, need to incorporate your advice to use arrays. (Man, the code flies when I do that!) Question: Is there a general rule about speed of arrays v. recordset? ADO v. Power Query? Or, does it depend on each situation? Are there good rules of thumb for when it is best to use each technique? Thanks for your great tutorials & willingness time share your expertise!

@KaiWeissmann 2 года назад

I would love to hear that as well :)

@TheHellis 4 года назад

Thank you! I have managed as far as your video shows but how do I write to a closed workbook? At work there is a group that all use the same workbook locally but they share the data from a worksheet on the network. This is really slow. It takes about 20-25 seconds to open the "database" worksheet write some simple values and close it. (It's a xlsb and is about 650 kb currently). Is it possible to write to a file with ADO and have formulas inserted in the database worksheet?

@Djblois1 4 года назад

I wonder do you know if anyone has compared these two for speed: 1) Vlookup (or now xlookup) 2) An ADO connection that reads the lookup values into an array, use an ADO connection to pull in the return values and then paste it back to the sheet.

@charlesdawnbeltran9603 3 года назад

Amazing video. Is it possible to populate the mutiple columnn recordset into userform listbox instead of worksheet?

@KaiWeissmann 2 года назад

You could read the recordset into an array with arr = rs.GetRows and then load this array into the listbox. Hope that helps :)

@Shampand 4 года назад

Love video and this is very helpful. I have a question regarding the copyfromrecordset. Instead of putting the results into a worksheet. If I know my query will only return one result, is there a way to store this as a variable instead?

@Shampand 4 года назад

I’ve got this I think. I’ve added in - rs.movefirst Str = rs.fields(0).value

@RowanB86 3 года назад

Great video. Thanks for this. If you could help with a problem we’ve been having with this kind of query at work, it would be massively appreciated. Since we started working on a new terminal server , we have discovered that SQL queries of this type will sometimes query a cached version of a worksheet table rather than the latest version. Once the workbook is saved, the query recognises the latest version of the table, but we never used to have this problem. The problem occurs sporadically. If you can provide any insight into why this might occur, it would be massively appreciated. Thank you.

@sudhirpatyal8407 4 года назад

Hi Paul Your lectures are very easily understandable Very nicely explain. I have one query that is what happen if excel file has a password and we try to open that file for edit and add data in closed excel file.

@Excelmacromastery 3 года назад

You can supply the password in the connection string.

@danielp1569 4 года назад

Hi Paul, would you have any tips on how to get the data from a column with more than 1 data type i.e. text and numbers? I’m browsing the web and tinkering with some solutions mainly for reading from csv (Schema etc) but haven’t been able to read the data from xlsx file yet. The cells with numbers are coming through but cells with text is blank.

@kristiansalloum6188 4 года назад

Hi Paul, been using this code structure for fair bit and I am starting to notice some sql statements slow down the macro. But if you where to execute it in MySQL it is immediate, could it be that the copyrecordset is slowing it down (copying 17000 rows)? Any advice on this would be great! Btw love your work mate

@dgjanes917 4 года назад

Is it more efficient to use ADO to copy from a closed workbook or to open it in vba and use advancedfilter?

@jrcryo Год назад

Hello paul, i tried based on your tutorial a query and no luck of course, on very simple sheet your tutorial works just fine. what i need to query is a structured table (listobject) lost in the many useless data to me. How can i query this data?

@givanildosilvadeamorim326 Год назад

Hello, it is possible to connect to an excel spreadsheet in onedrive personal using ADO.

@TheNeilsolaris Год назад

Is it possible to change data on a closed Excel workbook, using VBA? Thanks!

@paulucheoma9266 2 года назад

Paul, this is once again top notch content. I have a quick question for you. Can I use a wildcard in the file name? For instance, let's say that I have an Excel file named "Paul's Budget (2020)". The file name will always start with "Paul's Budget", but the year in the parentheses will change from (2021) to (2022) etc. The less technical approach would be to change the file name in the code every year, but can I use a wildcard like this in my file name: Paul's Budget* ? Thanks in advance for your reply.

@andywawa7227 2 года назад

Paul, very informative. How do I display only one field from a recordset in my range ("B2"). I've got a function getSQLData(query) and use Range("B2").copyfromRecordset getSqlData(query).Fields(1) but it doesn't work...

@abhishekbhatt8901 Год назад

Sir, Can i add rows ,columns and format cell to a closed excel file with ADO?

@kingpin11111 3 года назад

Hello Paul! Thanks for a great tutorial. I have a question related to what is being reffered to as "memory leaks" in excel. I read some time ago that certain actions can cause excel to not properly free objects and variables from memory, causing it to slow down over time. Those "memory leaks" persisted even if those objects were explicitly erased from the memory by setting them to nothing. It was stated that the only solution was to quit excel application a then open it again. ADO objects were mentioned as one of possible causes for such memory leaks. My question is: 1) "Is it true that some objects may not be properly cleared from the memory even if they are manually set to nothing?" 2) "If so, then Is ADO object known to be a possible cause for such problem?" Thank you very much!

@rogerh2694 2 года назад

When you're getting data from a closed workbook, is it actually retrieving the data from SQL?

@Rice0987 2 года назад

And what is modUsingClass in project panel?😊

@nelseify 4 года назад

Great video series Paul, long been a fan or your website articles. Question for you I am trying to import from an Excel file to Ms Project tasks using ADO and this is working quite successfully for adding the entries. However how would you go about updating existing tasks based on the excel file. For example, I have a task in project, I want to search the ado recordset for each task and update one field on the task based on the excel file I've read about disconnected ado recordsets and the rs.find and rs.filter commands, is this the right way to go about such a task? I havent got it working yet as I'm still playing around with the correct use of the cursor? Just wanted to get your opinion Also I'm a fellow Irish man! :D

@Excelmacromastery 4 года назад

Glad you like it.

@atiqsarwar4222 4 года назад

I add the data grid view from additional options, but I am unable to program it, how I can preview excel worksheet data in that data grid view

@TravisFX 3 года назад

Thanks for doing these great videos. Just getting into ADO. Am consolidating data from 12 sales files. 1 per month. And like to do it without opening each file. I followed the logic referring to other workbooks. A little confused with Connection.Open in your examples. You are connecting to "ThisWorkbook.Fullname". It seems like you are connecting to... yourself? Since the code is in the same file? I did the sourcefile added connection line and it works but we still need the Connection.Open to this workbook? Doesn't work without it. I thought we would've had to "Connection.Open" to the source file.

@musthagmohamed 3 года назад

Thank you very much for the tutorial. I have been learning a lot from your tutorials. I am having a tiny issue. after opening your file, when I run VBA in the excel file, it gives an error Run time error with description "Method 'Open' of object '_Connection' failed". Please help me. I am using windows 10 and Office 2013.

@Excelmacromastery 3 года назад

Sounds like the connection string is not correct

@zoranstojanovski8407 5 месяцев назад

In this example we can read from excel sheet, I tried same to read from excel table, but till now didn't make to work. It would be great to put excel table in ADO.recordset, manipulate data and then get back into excel.

@rickkeev4919 4 года назад

Is there a way to Loop thru folders using this process in order to collect info from multiple closed workbooks?

@Excelmacromastery 4 года назад

It's possible but no easy to do.

@nitinpawar3745 10 месяцев назад

Please help me. I want to do this with the file saved on a SharePoint site. What should be the connection string in case the source file is saved on a SharePoint site?