How to use ADO and VBA to Read from Worksheets
HTML-код
- Опубликовано: 15 сен 2024
- 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(TheExcelVBAHan...)
Webinar Archives - 60+ Hours of VBA training(excelmacromast...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromast...)
Related Links
Connection Strings (www.connection...)
VBA Class Modules - The Ultimate Guide (excelmacromast...)
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).
![Eminem - Like My Shit [Official Audio]](http://i.ytimg.com/vi/vx_IMvFkrfA/mqdefault.jpg)
I am learning VBA alongside your videos and you are an enormous help.
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.
Why is this fantastic Excel resource soo overlooked??
OMG, we can SQL data Worksheets!!! Even from a closed workbook!!
Thanks Sergio
Paul, your RUclips videos and blog articles are excellent. The best out there IMHO!
Thanks Michael
Awesome video. ADO explained very simply.
Greetings from Germany
Fantastic. This is great. I will be able to some a data migration headache with this method.
Glad it was helpful!
For all the VIDEOS, and continued efforts that you have made in my education, I will always be thankful.
Thanks😀
Excellent video, and thanks for providing the Excel file to download, this really helps .
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. :)
I like the difference sources handling at 8:54. New technique learnt, thanks.
Most helpful VBA content on internet so far!
Thanks Ben
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.
Thanks
Great video (as all ones from you). You expand my VBA knowledge every time!
Thank you Paul... I have successfully used my first ADO from your tuts....Thanks ...a lot from Nepal......
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.. 🌟 🌟 🌟 🌟
Thanks Salim
Your videos are awesome for learning VBA. Thanks for sharing your knowledge.
Great! I loved this solution, I will implement it is several crucial process in my job
Regards from Mexico 🇲🇽
Me too, I will do the same, in Brazil!!
You are the best, thanks!!
Interested to see how to interact with Excel Tables with ADO, hopefully that gets covered.
Amazing sir 👌. Very easy to learn 👍
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!🙏
Thank you very much! This is clearly explained, simple and powerful!
Good job! Very professional video. Pls continue in this direction!
Thank you! Glad you like it.
Another jewel in my collection
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.
thank you sir, its very helpful
thank you very much for the information.
Glad it was helpful!
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.
Thanks a lot for sharing, very useful!
I might replace Power Query solution with this , in one specific case - needs more testing.
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!
I would love to hear that as well :)
Superb.... It's really helpful
much-awaited,thanks!
Thanks a lot for your valuable information on ADO. Just a quick question, how can we write data back to closed workbook using ADO??
Brilliant; very lovely.
Hi Master
Can you make a video show us how we can download OLE object from access using ADODB object ?
You are amazing just hopping for free complete serious that would help us all with amazing work you do
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.
There is a link to the files in the description
Thanks a lot for your video, you are a master!!!
Glad you liked it!
omg this was so simple and they never told me! now everything is clear and simple 8l
Great video, one problem for using ADO is that you have to select the relevant VBA Projects under Tools/Reference menu.
Amazing video. Is it possible to populate the mutiple columnn recordset into userform listbox instead of worksheet?
You could read the recordset into an array with
arr = rs.GetRows
and then load this array into the listbox.
Hope that helps :)
Very nice lesson, thank you!
amazing video! thanks so much for sharing it!
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.
Thanks Jeff
Thanks in advance Paul.
Waiting impatiently :-)
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.
Thanks for this! Can you should how to retrieve specific named range cell values from different worksheets in a closed workbook?
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.
Exciting!
Very good tutorial, something new to learn. Can you upload the correct source code file please? The one I have downloaded is different! Thanks
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?
I am waiting for your released video
Fantastic, thanks for posting!
Glad you like it Adam
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? ,
You, Sir, are amazing!!
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?
This stunning when combine insert and select statement.
I wonder if we can combine SQL update and select statement?
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.
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?
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.
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?
You're awesome. Thanks a lot for awesome videos
Thanks Yasser.
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.
Thanks Mikhail
Thanks Paul.
You're welcome.
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.
You can supply the password in the connection string.
When you're getting data from a closed workbook, is it actually retrieving the data from SQL?
Thanks a lot!
Can you use this technique to create a reccordset from an excel workbook stored on an external website?
Hi paul,
Thanks a ton for this short & beautiful content.
Is there a way to include multiple excel files in zip folder using vba.
Hi - I love this solution! However a common issue that I run into, for example, is that Excel users will leave rows 1-4 blank and the headers(field names) start on row 5. So once I connect to the recordset the array Ubound = 4 when in fact there are thousands of rows in the file that are not being counted.
Is there a workaround to skip the first four empty/null rows and the get the actual row count? rst.getrows seems to return the same count.
Posting this in case it helps others. I found this is a reliable method to get an accurate count of row data and then store it in an object i.e rowCount:
Dim rowCount as Variant
With rs
.MoveLast
.MoveFirst
rowCount = .GetRows(.RecordCount)
End With
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.
Great work.
Thanks Daniel.
Great video! Is it possible to join several sources to one table by using the sql syntax?
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?
Nice video please make video on web scrapping
Does ADO help with concurrency where multiple users will access the same data source at the same time?
I have this exact question as well. Imagine the possibilities IF ADO helped with concurrency.
If it's read only data source (so you don't want people writing back to it) and no one actually accesses/opens the data source directly then yes. I don't know the answer otherwise.
No. Concurrency is not available in Excel. You need to use a database if this is a requirement.
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.
Is it possible to change data on a closed Excel workbook, using VBA? Thanks!
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?
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.
Is it Quicker to read from a workbook using ADO (since it can be closed) or is it quicker to open the workbook and read using other methods?
I can't wait,
Sir, Can i add rows ,columns and format cell to a closed excel file with ADO?
Hi Paul! Thanks for this great video. Can we retrieve data from a closed csv file likewise?
Yes. As long as it is in record format.
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...
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?
I’ve got this I think. I’ve added in -
rs.movefirst
Str = rs.fields(0).value
Also top👍🏻 But also one question😉 For the last example with the query FROM a closed workbook in my workbook I get always the Runtime error: "Cannot update. Database or object is read-only." in the row connection.execute query 🙁
I had the same problem, the worbook was in OneDrive folder and moved to another folder on my disk C and it works well.
And what is modUsingClass in project panel?😊
Is it more efficient to use ADO to copy from a closed workbook or to open it in vba and use advancedfilter?
Hello, it is possible to connect to an excel spreadsheet in onedrive personal using ADO.
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!
thanks a lot wished you included this work file
In the description.
Hi! Only the Delete statement doesn't work when the Recordset is a Worksheet.
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
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.
Hello sir, hope you doing well, can you tell me how to data grid view programming
In your vba code you have hardcoded name of the table. How I can obtain the table name first using ado? I only now the worksheet name from other workbook where the table is.
When you added WHERE > 60 and SUM...GROUP BY, it looks like you were getting numeric values back, not strings. What happened?
It's an issue with ado. It puts an apostrophe in. The way around is to have a dummy record or remove apostrophe after the records are copied.
@@Excelmacromastery You had a dummy record in there but didn't show it in the video?
@@jbinmd I didn't use a dummy record that's why the numeric values had apostrophes.
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.
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.
Hi. I want to do the same but Select from an predefined Table "MyTable" in a sheet. Whatever I do I get an Run Time error that it cant find object MyTable.
Select * from MyTable ; OR [MyTable] OR [MyTable$] or [Sheet1$MyTable] . So can you do this from an pre-defined Range or table and how do you reference it.
hey, could you solve it?
I tried to use a query: Delete * From table where field=data, and received an error saying: Deleting data in a linked table is not supported by this ISAM.