Let me know what you think of this video in the comments below. Make sure to download the code examples from the description.
@thuthaonguyen84243 жыл бұрын
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 🙏🙏🙏
@eeius9863 жыл бұрын
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
@danielmadariagavaldes8933 жыл бұрын
I already knew most of these stuff, but man, what a brilliant explanation!!!!!! Marvelous job!!
@Excelmacromastery3 жыл бұрын
Thanks Daniel
@philipmcdonnell71683 жыл бұрын
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?
@efraintrejogomez6185 Жыл бұрын
me sorprende cuanto conocimiento sobre este lenguaje VBA, Saludos cordiales maestro, estoy aprendiendo mucho de usted, a pesar de la barrera del idioma...
@musabashraf Жыл бұрын
Thanks for the video. You cleared many doubts I had.
@ascarrunz3 жыл бұрын
Paul, awesome video. Thanks a lot for you tips. But Could you do video of what is faster: ADO+VBA or PowerQuerry?
@javiermolinaperez16712 жыл бұрын
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 :)
@officetricks63032 жыл бұрын
Thanks a lot, learned a very simple way to solve complex data, keep posting...
@persaq2 жыл бұрын
Perfect! Thank you very much, Paul.
@SimpleExcelVBA3 жыл бұрын
I haven't yet used this for actual databases, only for Excel worksheet. Need to try with that. Good video :)
@tigranhayrapetyan83572 жыл бұрын
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!
@jimfitch3 жыл бұрын
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!
@Excelmacromastery3 жыл бұрын
Thanks Jim. Glad it was timely for you.
@lalitthapa0073 жыл бұрын
There should be a heart ❤ button in addition to 👍 button.. thank you !!
@jillianonthehudson17393 жыл бұрын
This was awesome! Thanks! I really don't understand how anyone could downvote content like this
@Excelmacromastery3 жыл бұрын
Thanks Jillian
@kebincui Жыл бұрын
Mind blowing👍👍. Thanks Paul
@Excelmacromastery Жыл бұрын
Glad you enjoyed it
@hichamhadj96403 жыл бұрын
As always, Excellent video !
@big1975E2 жыл бұрын
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!!
@victorgarmin23118 ай бұрын
Thanks for sharing, nice video, do you have an example of the conexion string for a oracle database??
@granadosvm3 жыл бұрын
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.
@MrKhaldonalali8 ай бұрын
Excellent video, thank you very much for it
@Excelmacromastery8 ай бұрын
You're welcome.
@frikduplessis88493 жыл бұрын
Thank you Paul, it has been a long time ⌛ clear explanation on the error handling 👍
@Excelmacromastery3 жыл бұрын
Happy to help Frik.
@mike_case3 жыл бұрын
Great video. Thank you Paul!!!
@Excelmacromastery3 жыл бұрын
Glad you liked it Michal!
@kuaatrichard58062 жыл бұрын
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.
@kamalam293 жыл бұрын
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.
@VS.R3LeVant3 жыл бұрын
Easy to understand thanks for sharing
@Excelmacromastery3 жыл бұрын
Glad it was helpful!
@kurtisfickle3 жыл бұрын
This is fantastic! Thank you!
@Excelmacromastery3 жыл бұрын
You're very welcome Kurtis!
@Ronaldog22073 жыл бұрын
Great Video!!! Congrats!
@vincentlee79953 жыл бұрын
Thanks Paul.
@Excelmacromastery3 жыл бұрын
You're welcome Vincent.
@Gg-oh2zy2 жыл бұрын
Neat n clean explanations 😀
@johnbutler27503 жыл бұрын
Brilliant stuff! Thanks
@Excelmacromastery3 жыл бұрын
Glad you enjoyed it John!
@leeblack21033 жыл бұрын
Very Nice! Can you work on a video to work on doing API calls using VBA?
@Excelmacromastery3 жыл бұрын
It's been on my list for a while now but I hope to do it soon.
@carlosfinholdt41903 жыл бұрын
Great job. Thanks.
@Excelmacromastery3 жыл бұрын
You're welcome Carlos.
@oliveroshea57653 жыл бұрын
Thanks Paul!
@Excelmacromastery3 жыл бұрын
You're welcome Oliver.
@lqc1014 Жыл бұрын
good skill
@engrvarsi37743 жыл бұрын
Many thanks
@Excelmacromastery3 жыл бұрын
You are welcome
@JStruthers892 жыл бұрын
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
@TheAjayiscool Жыл бұрын
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
@govindkumarsodani3290 Жыл бұрын
very usefull
@zakimoustapha72962 жыл бұрын
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.
@danilklimov32332 жыл бұрын
Спасибо
@azul3k3 жыл бұрын
Thanks, I'd like to know how to execute a procedure and how to close the connection.
@akdhunt3 жыл бұрын
Brilliant explanation! With late binding is posible to use ADO with Excel for Mac?
@nealonions76543 жыл бұрын
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.
@averagebodybuilder3 жыл бұрын
I actually use late binding for excel.
@nealonions76543 жыл бұрын
@@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?
@averagebodybuilder3 жыл бұрын
Oops I didn't read the part where he wrote Mac
@serdip3 жыл бұрын
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.
@Excelmacromastery3 жыл бұрын
Thanks for the feeback. I haven't used the DataLink.
@big1975E3 жыл бұрын
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?
@AlexB_o73 жыл бұрын
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).
@phpvbacoder90003 жыл бұрын
Write to array with rs.getrows
@muhannad_youssef3 жыл бұрын
Thank you sir ... how i can find last purchase price from table ( date - product - price - purchase or sales )
@oliveroshea57653 жыл бұрын
Hi Paul, could this be achieved by using Office Scripts? I like VBA, but was wondering if Office Scripts offered similar methods. Cheers Oliver
@Excelmacromastery3 жыл бұрын
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.
@kuldar.3 жыл бұрын
Interesting, but next level...
@Excelmacromastery3 жыл бұрын
Glad you like it.
@rajsurendra7040 Жыл бұрын
How to import to a template with discrete columns and rows or mapping the data to specific filed
@gavdownes1003 жыл бұрын
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
@nealonions76543 жыл бұрын
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
@gavdownes1003 жыл бұрын
@@nealonions7654 hmmm. Thank you. Will look at that very closely soon
@johnwayne80593 жыл бұрын
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!😉
@Excelmacromastery3 жыл бұрын
Error Handling is tricky when unwinding the code. Check out this video kzbin.info/www/bejne/ooOYlmudrqZpb7c
@johnwayne80593 жыл бұрын
@@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!😇
@karvannansa8045 Жыл бұрын
How to add custom column with sequence? Sir?
@AS-ym2bp3 жыл бұрын
Can you make a video on how to read and write data from and to an SQL database through VBA?
@Excelmacromastery3 жыл бұрын
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.
@3Starsgamer343 жыл бұрын
Is ADO still relevant post power query?
@Excelmacromastery3 жыл бұрын
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.
@TheDinga1977 Жыл бұрын
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.
@alwilson64713 жыл бұрын
Unable to download the code. The "get the code" option just hangs.....
@Excelmacromastery3 жыл бұрын
Turn off any popup blockers and it should work.
@alihamiad78493 жыл бұрын
I never get the source code; Am I doing something wrong?
@Excelmacromastery3 жыл бұрын
Send me your email address and I will check it. Paul@ExcelMacroMastery
@briandennehy63803 жыл бұрын
Thank god for Power Query I say 🤣🤣🤣
@Excelmacromastery3 жыл бұрын
Hi Brian, do you think the code is too complex?
@briandennehy63803 жыл бұрын
@@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
@oliveroshea57653 жыл бұрын
@@briandennehy6380 it’s no more complex than the M code in the advanced editor in Power Query
@briandennehy63803 жыл бұрын
@@oliveroshea5765 True but with Power Query you can use the GUI to connect to a database without the need to know VBA.
@partymaschine923 жыл бұрын
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.