Excel VBA Introduction Part 58.30 - SQL for Excel Files - Updating Existing Data

[@thwbn3993]

This is by far the most excellent SQL (for Excel VBA) series on the most reliable and didactical usefull VBA channel! Thanks a ton!

[@WiseOwlTutorials]

Many thanks for the kind words and support!

[@daves4026]

Simply nothing better than this. Thank you

[@kartickchakraborty9135]

There is no Question that, our Beloved Andrew Sir is the best teacher to learn VBA from. After VBA, our request to you to start teaching the most demanding language "Python". God bless you Sir!

[@WiseOwlTutorials]

Thank you Kartick! You'll be pleased to hear that one of my colleagues is working on a Python tutorial as we speak!

[@kartickchakraborty9135]

@@WiseOwlTutorials Thank you for your information Sir. I hope, he is as good as you in terms of teaching. Frankly speaking, I'm comfortable to learn something new only from you. There are many channels on KZbin to learn something from. But, I've only found 2 channels, that is perfect for me. ExcelIsFun and Your channel. Both of you are fabulous in your respective areas.

[@WiseOwlTutorials]

@@kartickchakraborty9135 Don't worry, he's an excellent teacher! He's responsible for the SSIS and Excel PowerPivot tutorials that we produced some years ago!

[@kartickchakraborty9135]

@@WiseOwlTutorials Thank you Sir. When you are saying then He must be a good teacher. Would you kindly give me his channel link so that I can subscribe his channel.

[@WiseOwlTutorials]

@@kartickchakraborty9135 Hi Kartick! His videos will appear on this channel in a new playlist, hopefully within the next few weeks!

[@laxmru]

Thank you Andrew for sharing this valuable knowledge and your efforts. I came across your videos in March 2023. I started using it for my work. Gradually, I could automate things which used to take hours. For example, I could make entire salary journal entry using SQL queries which you showed in your videos. There are other time consuming tasks which I could automate and save time of myself and my subordinate. Also this not only saves time but ensures accuracy in data. I am thinking of making some standard tools (macro) using ActiveX controls which can be used in place of regulary used methods like vloook. But there are some limitations like Update systax works within one workbook and it cannot get data from other workbook. It looks like I am going to save some time to pack up the work little early. Thank you very much

[@WiseOwlTutorials]

Happy to hear that the videos have helped save you some time! Thanks for sharing and for watching!

[@RohithKK-uh7pp]

Thank you for putting lot of efforts to teach others. Excellent video.

[@WiseOwlTutorials]

Thank you for all your support Rohith!

[@tejamarneni]

Thank you Andrew for such a great video. I forgot to tell you I didn't make it to the final round onto Amazon interview. I thought I did well this time but unfortunately I didn't get it. The worst part is they wouldn't give feedback. I am not too worried about it. I will keep learning and stay positive.

[@WiseOwlTutorials]

Ahh Ravi I'm sorry to hear that. It's unfortunate that you don't have any feedback but you still have the right attitude. Keep working hard, it will happen.

[@tejamarneni]

@@WiseOwlTutorials Thank you Andrew for your constant support.

[@frikduplessis8849]

Andrew what a refreshing lesson, and as always great humor 😄 I was waiting for it and 🙌 Twilight bottom of the log 😀, thank you again for a great tutorial and brilliant techniques and tool

[@WiseOwlTutorials]

My pleasure as always Frik! Thank you for the comments and support, it's all very much appreciated!

[@tuannghiatran8936]

Thanks for your nice video! I need your help, i have a data contain numberic column, value >10.000 billion, when i run code, the file has error, number change string and value changed

[@donasolliro6644]

Can we update excel file at same time by multi user using this sql statement without error? Please answer.

[@grb10988]

Andrew, I have a question about character limits used in the update statements. I'm trying to update a table from values in a different table in the same workbook. I have join set up correctly because every field is getting mapped perfectly to the fields that I need it to. However, one of the fields is a "notes" type field and has a large number of characters. When I run the update statement, that field only returns a maximum of 255 characters, which makes sense given 256 bit limits that often occur. However, is there something that I can adjust to try to capture all of the characters from that field? Maybe something in the connection string?

[@WiseOwlTutorials]

Hi! Indeed, this is common. You could try the solutions offered at these links: stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider stackoverflow.com/questions/926453/parsing-an-excel-file-in-c-the-cells-seem-to-get-cut-off-at-255-characters#answer-3806372 I hope one of them helps!

[@grb10988]

@@WiseOwlTutorials I really appreciate your response! Unfortunately, I have not been able to resolve this issue. I am not able to get to the part in the registry editor that is recommended to change the parameter. At this point, I may have to do a non-SQL work-around to get the solution that I'm looking for. Cheers!

[@WiseOwlTutorials]

@@grb10988 I hope you find a solution!

[@ousmanetall1286]

You have not mentioned the rows affected topic, which is very important to test if any change have been made. if only it could be covered, thank a lot!

[@MrMallesh1]

this is awsome series, you have made it so simple now. appreciate your effort. I love this series no one has touched such depth. also can you make video on Regular expression Pattern with VBA. Thanks.

[@WiseOwlTutorials]

Thank you so much! I have Regular Expressions on my list of videos to make, along with many others. I hope to do this at some point but I don't know when it will be.

[@sahajagrawal1169]

Hi Andrew. Thanks for the Great Series. You are really awesome. I have a question about the Update SQL statement. Can we run the update command on an array or any other efficient data structure? Running an update command on an open Excel file takes a lot of time. If I need to Update data on the Excel file where my VBA code resides then the only efficient way I can think of is - copying the data to another workbook, closing it, and then performing the update operation through ADO on that closed workbook. If there is a way to run the Update SQL command on arrays or any other in-memory data structure would be a huge improvement in speed for open Excel files.

[@WiseOwlTutorials]

Hi! Happy to hear that you're enjoying the series! That's an interesting idea but I'm not aware of any way to use ADO to connect to an in-memory data structure, sorry!

[@bondniko]

Hi, Andrew! Please tell us what to do if there is a column with dates (dd.mm.yyyy), some of which are recognized as dates and some aren't. Select statement returns only those records where cell values are actually dates. Iif to check if the cell is text or date won't help. Same applies to numbers. Thanks!

[@WiseOwlTutorials]

Hi there, I think that setting the IMEX property in your connection string to treat all data as text might help. I may have posted these links to you previously but just in case I imagined it, here they are: stackoverflow.com/questions/35291171/imex-1-seems-to-have-no-effect www.etl-tools.com/imex1.html I hope it helps!

[@The_Code_Father]

This is just wonderful series of working with database in excel vba Mr Andrew I'm now in lesson four and I got this question if you don't mind If I have an excel file with some vba change event in it and I'm inserting data via another excel file using getopenfilename method but when I watched working with database series I found it's possible to import and export data to a closed excel file which is applicable in my case but what about vba event in this closed excel file would it still run even if I used activex data object library without opening it ? Or should I continue using getopenfilename method ? BTW I insert some data into mentioned excel file and then after running the event I extract the resulted values ( it's an inventory evaluating and pricing file )

[@WiseOwlTutorials]

Hi there! No, the event procedure won't run if the workbook is closed.

[@The_Code_Father]

@@WiseOwlTutorials aha ok then should keep using current method I'm using Anyway I'll finish this whole series because it's very useful for me Thanks again Mr Andrew for this info and tutorials

[@WiseOwlTutorials]

@@The_Code_Father My pleasure! I'm happy that you're enjoying the series and thank you for watching!

[@chindupriyapavan5044]

Hi Sir, Sorry I'm asking question here about SSRS report builder.. Q) How to use where clause for creating calculated fields? Eg. Count(ticketnum) where (mttr)

[@WiseOwlTutorials]

Hi! I'm just curious but why did you post this question on a VBA video instead of one of our Report Builder videos? kzbin.info/aero/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU Anyway, you can nest an IIf function inside a Sum function like this: =Sum( IIf( Fields!OscarWins.Value > 0, Fields!RunTimeMinutes.Value, 0 ) ) This will sum the run time of films which have at least 1 Oscar. You can do the same thing with other aggregate functions such as Count and Average but you'll need to use Nothing rather than 0 =Count( IIf( Fields!OscarWins.Value > 0, Fields!RunTimeMinutes.Value, Nothing ) ) I hope it helps!

[@chindupriyapavan5044]

@@WiseOwlTutorials Thanks alot sir💐. It's working 🔥