Let me know what you think of this video in the comments below. Make sure to download the code examples from the description.

@thuthaonguyen8424

3 жыл бұрын

Thank u so much for your helpful video. I have a question which i will be very greatful if getting ur answer for. Could ADO connects Bigquery (data source in Google cloud) and excel worksheet? And if yes, please walk me through the codes 🙏🙏🙏

@eeius986

3 жыл бұрын

Amazing once again Paul, Like ALL your videos! Now using it on my project :). What about an Userform to take pictures from the webcam and once image is approved it uploads to a specific cell in Excel? Just an idea for your next video ;)

@TheAjayiscool

Жыл бұрын

Great resource. I am your biggest fan Paul. Facing a bottleneck in my project. Details are in my post. Appreciate any guidance

I already knew most of these stuff, but man, what a brilliant explanation!!!!!! Marvelous job!!

@Excelmacromastery

3 жыл бұрын

Thanks Daniel

Great tutorial, Paul. And perfectly timed - just started a project to extract Access data to Excel workbooks for daily & on-demand status reporting & analysis. Thank you!

@Excelmacromastery

3 жыл бұрын

Thanks Jim. Glad it was timely for you.

Perfect! Thank you very much, Paul.

Excellent video, thank you very much for it

@Excelmacromastery

5 ай бұрын

You're welcome.

Really valuable ! I've been searching on the internet for a solution with no chances at all since now ! Many thanks, Late binding seems to work with other computers :)

As always, Excellent video !

This was awesome! Thanks! I really don't understand how anyone could downvote content like this

@Excelmacromastery

3 жыл бұрын

Thanks Jillian

Great Video!!! Congrats!

Great video. Thank you Paul!!!

@Excelmacromastery

3 жыл бұрын

Glad you liked it Michal!

Thanks a lot, learned a very simple way to solve complex data, keep posting...

I haven't yet used this for actual databases, only for Excel worksheet. Need to try with that. Good video :)

This is fantastic! Thank you!

@Excelmacromastery

3 жыл бұрын

You're very welcome Kurtis!

Thanks for the video. You cleared many doubts I had.

Brilliant stuff! Thanks

@Excelmacromastery

3 жыл бұрын

Glad you enjoyed it John!

Thank you Paul, it has been a long time ⌛ clear explanation on the error handling 👍

@Excelmacromastery

3 жыл бұрын

Happy to help Frik.

me sorprende cuanto conocimiento sobre este lenguaje VBA, Saludos cordiales maestro, estoy aprendiendo mucho de usted, a pesar de la barrera del idioma...

Great job. Thanks.

@Excelmacromastery

3 жыл бұрын

You're welcome Carlos.

Very hard for me to understand all of this code, but it's definitely useful thing in job. Will return to it later and make it usable in my everyday work) Thank you Paul for your videos!

Easy to understand thanks for sharing

@Excelmacromastery

3 жыл бұрын

Glad it was helpful!

Neat n clean explanations 😀

Thanks Paul!

@Excelmacromastery

3 жыл бұрын

You're welcome Oliver.

Hi Paul. Thanks a lot for this video. Although the best part of this tutorial is "How to close the connection correctly" which is very important for big professional projects. Cleaning the objects before the cursor leaves procedure or a function, is a very very delicate and yet complex thing to understand and I think every professional should have an idea of that. it would be great if you can create a tutorial just explaining the different ways to cleaning the objects like acrobat objects, excel objects, word objects, so objects, etc, etc.

Thank you Paul

@Excelmacromastery

3 жыл бұрын

You're welcome

Hello Paul, you make great vodeos. Very good to understand. However, I would also be very happy about a database query from a MySQL database.

Mind blowing👍👍. Thanks Paul

@Excelmacromastery

Жыл бұрын

Glad you enjoyed it

Thanks Paul.

@Excelmacromastery

3 жыл бұрын

You're welcome Vincent.

Paul, awesome video. Thanks a lot for you tips. But Could you do video of what is faster: ADO+VBA or PowerQuerry?

very usefull

Many thanks

@Excelmacromastery

3 жыл бұрын

You are welcome

It might be a good idea listing a site or page where the standard SQL syntax code could be explored. For people who are new to this to understand the power of these connections, it would be good to know the potential of SQL code. When you pass parameters from user's input (they could be cells in a sheet) these type of code can be used to create very useful and flexible dashboards.

good skill

There should be a heart ❤ button in addition to 👍 button.. thank you !!

My unending work project has to be modded to use Access/SQL Server at some point so this is very timely, Paul. Thanks. Will you be doing a write changes back to the DB and how to 'delete' data in future videos?

Спасибо

Awesome video! I’ve used the knowledge I gained from this video multiple times. It has been a real game changer for me! Is it possible to use an ADO connection to connect to a SharePoint online list? SharePoint online seems to be being used more and more. Thanks!!

Thanks for sharing, nice video, do you have an example of the conexion string for a oracle database??

Great video. Thanks for posting. In addition to looking up standard connection strings, I like to use the DataLinks object as follows: Dim objDL As Object Dim vntConnection As Variant On Error Resume Next Set objDL = CreateObject("DataLinks") vntConnection = objDL.PromptNew() If Err 0 Then Err.Clear Exit Sub End If The PromptNew() method will display the Data Link Properties dialog in which the user can specify the Access database, SQL Server database, Excel workbook etc and return the connection string, which does not have to be edited.

@Excelmacromastery

3 жыл бұрын

Thanks for the feeback. I haven't used the DataLink.

Hi Paul, I was wondering if it is possible to pass a sharepoint list directly to the Named Manager, instead of printing it to a worksheet with ListObject? Thanks in advance

Interesting, but next level...

@Excelmacromastery

3 жыл бұрын

Glad you like it.

Very Nice! Can you work on a video to work on doing API calls using VBA?

@Excelmacromastery

3 жыл бұрын

It's been on my list for a while now but I hope to do it soon.

Thanks, I'd like to know how to execute a procedure and how to close the connection.

If I’m connecting connecting to an Excel workbook with ADO is it possible to edit data in that workbook in addition to querying data from that workbook?

Thank you sir ... how i can find last purchase price from table ( date - product - price - purchase or sales )

Awesome video Paul. I have implemented late binding and close connection the way you explained in a project with MS Access as backend. Back end is deployed in a shared drive to which my users have read/ write permission. There are action queries in the project using SQL INSERT and UPDATE also SELECT for reading data into a recordset. So far so good. Tool works like charm! However on a stress test with 40 + users simultaneously using tool, saw error saying "The database has been placed in a state by user 'Admin' on Machine that prevents it from being opened or locked" This some times keeps the backend in exclusive mode and at times corrupts the database. Paul, where am going wrong. Any help will be greatly appreciated Ajay

Please do a video on how to store the RS into an array and to operate the data from inside the vector. (index match into the array).

@phpvbacoder9000

3 жыл бұрын

Write to array with rs.getrows

How to import to a template with discrete columns and rows or mapping the data to specific filed

Hi Paul, could this be achieved by using Office Scripts? I like VBA, but was wondering if Office Scripts offered similar methods. Cheers Oliver

@Excelmacromastery

3 жыл бұрын

Good question Oliver. At the moment Office Scripts interacts with everything through Power Automate. There may be the possibility to use external libraries in the future but for the moment it's no possible.

Is it possible to create a crystal report from excel workbook? If yes then I think it's an important topic to talk about Teacher.

Brilliant explanation! With late binding is posible to use ADO with Excel for Mac?

@nealonions7654

3 жыл бұрын

No unfortunately not. The only (sensible/practical) way I know for Mac is to use ODBC and then use a QueryTable. Then assign the result to a variant. May also need an ODBC drive like the commercial ones from Actual Technologies.

@averagebodybuilder

3 жыл бұрын

I actually use late binding for excel.

@nealonions7654

3 жыл бұрын

@@averagebodybuilder What on a Mac not running windows??? As far as I know there is no library to link to and then late bind that runs on OSX?

@averagebodybuilder

3 жыл бұрын

Oops I didn't read the part where he wrote Mac

Can you make a video on how to read and write data from and to an SQL database through VBA?

@Excelmacromastery

3 жыл бұрын

The code for reading from an SQL database is almost the same as the code used here to read from an Access database. The only difference is the connection string. I'll be doing a video, on writing to a database from a worksheet, soon.

Very good video Paul!👍👍👍 Btw: When I use "exit sub" in sub (e.g. "DoSomething") and then use "DoSomething" in an other sub (e.g. "Main"), I often had the problem that Main also got stopped! Do you've some experience with these "syndrome"? I decided to apply a "goto" to jump over the error handling! Do you think that's a good way? B|R from Germany 🤟😎 and thanx in advance!😉

@Excelmacromastery

3 жыл бұрын

Error Handling is tricky when unwinding the code. Check out this video kzread.info/dash/bejne/noZpx5qgqaScabg.html

@johnwayne8059

3 жыл бұрын

@@Excelmacromastery okay, thank you, that's also a good explanation! But how can I manage that the Code don't stop. Because I call a sub which is not neccesary for following actions, it's just a nice-to-have in relation to its function! I hope that's a better description what I'msearching for!😇

This works great, thanks. Question: How do you use JOIN/ INNER JOIN with regards to Junction tables? I cannot find any tutorials online. To clarify what I am trying to learn, my junction table has a client_ID and product_ID linking to tables Clients and Products. How do I retrieve the Junction table as Clients.name and Products.name

@nealonions7654

3 жыл бұрын

First give your ‘junction table a name, say tblClientProduct. Along with that let’s have tblClient and tblProduct Could try SELECT p.product_name , c.customer_name FROM tblClientProduct AS cp INNER JOIN tblClient AS c ON cp.client_id = c.client_id INNER JOIN tblProduct AS p ON cp.product_id = p.product_id

@gavdownes100

3 жыл бұрын

@@nealonions7654 hmmm. Thank you. Will look at that very closely soon

How to add custom column with sequence? Sir?

Is ADO still relevant post power query?

@Excelmacromastery

3 жыл бұрын

Of course. There are countless scenarios where PQ wouldn't be suitable. For example, an application that retrieves data to a UserForm based on real-time user selections.

I want to use ADO to get data from an access database that is password protected and the database is stored on a network drive with a specific userid and password instead of the active windows userid. I have searched the web and tried many connection strings…can someone point me in the right direction?

@TheDinga1977

Жыл бұрын

I also want the data to be put in an array to be used in the vba not put into a sheet.

Unable to download the code. The "get the code" option just hangs.....

@Excelmacromastery

3 жыл бұрын

Turn off any popup blockers and it should work.

I never get the source code; Am I doing something wrong?

@Excelmacromastery

3 жыл бұрын

Send me your email address and I will check it. Paul@ExcelMacroMastery

Thank god for Power Query I say 🤣🤣🤣

@Excelmacromastery

3 жыл бұрын

Hi Brian, do you think the code is too complex?

@briandennehy6380

3 жыл бұрын

@@Excelmacromastery Hi Paul, for me as someone who is learing VBA, it is a bit advanced. I just love the ease of Power Query to do this sort of thing but it is very well explained in the video

@oliveroshea5765

3 жыл бұрын

@@briandennehy6380 it’s no more complex than the M code in the advanced editor in Power Query

@briandennehy6380

3 жыл бұрын

@@oliveroshea5765 True but with Power Query you can use the GUI to connect to a database without the need to know VBA.

@partymaschine92

3 жыл бұрын

Again another great video of you, but I have to admit that I must totally aggree with Brian. Could you explain the advantage to crawl data via vba? In the video it seems to be faster, but I am not sure. I am comfortable with both kind of codes VBA and M and would love to give both versions a chance, however I love to use PQ in Excel. It is so much easier to pass on the file to colleagues who are not able to run macros due to companies restrictions.