How to use ADO and VBA to Read from Worksheets

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.

Excel VBA Training

Free Excel VBA Resources

Related Links

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).

[darkfelony]

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

[houstonvanhoy]

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.

[jimfitch]

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!

[sammir]

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?

[richardrossouw]

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.

[charlesdawnbeltran]

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

[Djblois]

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.

[TheUnicleo]

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!🙏

[guitngood]

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.

[rogerh]

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

[TheHellis]

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?

[ilyastrojnov]

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.

[duongsamvanna]

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

[jaimec]

hello i see in you video you use insert into

like this

Insert Into [InvoiceFacts$] ([Create Datetime], [CustomerID]) Values (#2024-08-07 00:07:20#, 2);

the result will be in his respective fields

'8/6/2024 11:35:05 PM '2

how can i fix in the sheets store right
could you guide me

[boomer]

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

[ProtegeBlackMamba]

Does ADO help with concurrency where multiple users will access the same data source at the same time?

[Mandelbrot]

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

[jithinrg]

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?

[jbinmd]

When you added WHERE > 60 and SUM...GROUP BY, it looks like you were getting numeric values back, not strings. What happened?

[andywawa]

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