Integrate Excel, VBA and SQL. Simplified and powerful

hey guys welcome back to my channel I'm loompa my and in this video I'll be showing you how to use a do library to work with excel in the past I published multiple series on a do to work with Access database SharePoint or sequel server and also a bit with Excel as well which you can find them from the playlist section well like so it's not a proper database but this functionality can become very useful because you know that Excel can be very difficult to work with when it's very heavy so guys before we begin please do not forget to subscribe if you have in and hit the bell icon for upcoming videos okay so here's the file that we'll be using for demonstration transactions on xlsx this first type clients in the transaction this is a dummy data of clients with all the attributes like address city are I mean phone number email address and so on and then this is are the transactions from 2017 oh through 2024 all these clients okay so we can do some example of how to you know I join these two tables and bring in attributes related to this client we can try to find out top 20 top 10 clients top 20 clients and then we can bring in the information or we can do like like a summary of this huge data set that we have around seventy seven thirty thousand records so if you have to even just open this file it's gonna take a lot of time if you do to a normal method like open the file activate the sheet copy the data and so on okay in this way we are not gonna open the excel file itself we're just gonna connect to the excel file using the connection a do connection and we're gonna you know like pull the information write the data to the table or update the records okay now just before we begin if you go to vbe to that YouTube or YouTube channel and the search for it's got a video section and search for a do active data objects you'll see a lot of examples that I've already done for SharePoint Server access database and all this how to update how to add new data how to look through the records so many examples ok so here once you know how to work with any of this database it's the same all across the way you'll be working with ok and I've just done a very high-level here so there are so much more you can explore it's so so powerful ok now if you go to my blog and search here for a do there are many sample codes that I use for all these videos have been uploaded here so you can directly copy and directly start using it for example here this must be updating the salary filled with 50,000 as a demonstration there are some sickles I'll also give you a bit of background Sequoia is actually just like a very simple sentence in a way so it's not difficult to learn people at all but it's very useful especially working with IDEO so all these are example course you can use ok now let's get into it first we need a connection string so let's go to connection string comm and from here you can select any of the database so server you need for now we just need a connection string for Excel so I'm gonna go in there and if you scroll down here so here it has string for XLS eggs for trading data as tags XLS B XLS some and all okay so for now since or our file that we are trying to use its XLS eggs I'm gonna copy the XLS extreme again and we'll start off with a new file let me just save this file is I'm gonna save this file here as a do demo so I have removed all my customization I'm doing some lesson for absolute beginners you must abstain if anyone is looking for word to learn new VBA they can come to the community tab here and use this link to sign up and I'll be sharing the lesson as soon as possible so if you have anyone who is interested please share the link with them that will be helpful so this is our connection string we need and looks like ok put it here so we have the connection string first step for us here will be to add a reference to a deal so Microsoft active data objects whatever is the highest you can check those ok again you might not use this often but this is gonna be very handy if you do not have much option with you only have XLS your option then it's gonna be very useful ok so this connection string we need this this is where our file part goes so let me get this and update it here okay so that's our file pod headers years this is I'm just gonna leave all this as default so first thing we'll need is two because we'll we are going to reuse the connection and again and again instead of setting up a connection for every process I'm gonna write like a create a public connection and then all these processes that will be creating can refer to this connection so first let's set up the connection so let's call this connection X as adodb dot connection and then we'll write our first function will be to open the connection or create a connection open connection something like this and here here we can set up this connection set this connection connection and then with this connection we can set up with dot connection string is equal to this and then here we can say open okay and we can say spool in if in case it errors and then we can okay and we need to close function function to close as well let's call this close connection maybe connection Excel will call this connection Excel and here we can check if the status is currently open and then we can close the connection after executing the process again so we'll need to see if this third state and then dot gonna be add state of them okay so we'll see if simple basically validate if these conditions are true if that's true then we can close the connection and I think we'll just return show give it errors we're not expecting it to ever but anyways let's keep it consistent [Music] true and false okay so now our connection is ready yes okay so if this is true clothes else don't do anything okay now let's give it a shot so you don't need to have this all open well first try maybe writing it to this this table so the sheets which you adhere will be considered as a table and the first one will be the header I'll show you like ways to refer to this okay now let's first write a code and then we'll close this and we'll try to see if it updates let's first write the update method let's call this update table let's make this not update let's first try the insert we'll add a new data here so let's try the sequel afterwards let's say insert 0 equal to a string sorry this is what happens when you call in multiple places so our insert sequel will be the syntax is very simple so insert in the table and then the fill names where you want to insert and then you specify the values what do you want to insert in those fields ok so insert into and then we want to insert into this transactions table so I'll copy this so one thing to note here is when you're working in shape when you have to specify these brackets square brackets and then when you're working with Excel you have to specify the tables with a dollar sign like this okay in the end and then here we specify the field names so we want to insert into these fields so let me just bring it here so this field as well let's put them in this format so these are the fields where we want to insert the data to okay and then now we can specify let's close this bracket values and then this is where we specify so in the same order that you specified here you can specify the values as well so for date client ID and the amount okay so let's say that we want to insert for client seven so values first will be to date okay so let's specify the date date we'll have to go in a proper date format so you can specify maybe today's date which is 12 July 2020 and the third one is going to be the amount okay so it can be any random amount all the amount will go without the already now this so let's say we want to like this is a number that you want to write okay without any double quotes okay so this is our C code basically now going quickly going through it again insert into this table okay insert into these fields and in this order okay this feels and then these are the values for this field so in a date will write this value incline we're gonna write this and then amount we're gonna write this okay now there are ways to like directly export or basically select everything from one table and then you can directly write it to that table as well they are examples like this as well in the channel so just check that out now what we need to do is we need to use this connection so first of all I have to open the connection so we'll this a connection open and the connection close okay so this will take care of opening the connection and closing the connection now with this connection so if this is set up correctly alright then we will have this connection active and then using that connection we can execute this sequel okay so this is a sequel and then you can also see the number of Records it affected so let's try this this is okay I'm not going to use anything else for now let me close this so now here we have 1230 client is 500 I'm gonna save this this file is quite heavy if you look here this is 79 be at the moment yeah I'm gonna close this and let's see if it works first I'm gonna just press f8 and go through it let me quickly check one thing so this has to end with this okay semicolon and 10 so let's see if it opens the connection the file part is fine this okay okay the the connection open so the property is now set to true it's gonna execute this particular string SQL and then once it's done it will go into this line just give it a moment now we can close the connection okay so that was good I just need a exit function here all right so now let's try one more for the same date let's try for Klein eight and let's see the amount is this no I'm gonna straight forward run it and then we'll open the file and verify it okay so it's done let's open up this file okay so you can see here we have added two records for this date for client seven and eight and this is the amount okay so that's a very simple way to add using SQL come on now for all this sequel I find let's say left join something like this you'll lend to W trees cool calm not all of them will be relevant for this particular Axl but there are very good examples that you can use I find it very easy to follow their tutorials so I kind of recommend them this side for example insert insert into sequel okay here insert into this table these are the columns and then if you have more columns you could specify more columns values and then this and then the semicolon okay so this is how you can do it and there are examples you're gonna actually practice it here yourself if you want to gain some expertise around this okay but it's very simple sigil is very simple so it's just give it a try and then it should not be a problem now let's try one more method of adding the data maybe this method I'll just copy this we're not going to reduce all this but so that we don't have to rewrite and I'll quickly talk you through what is it so here we already have we don't need a set of connection so here is what we need to use this connection to open our database I'm gonna remove all the loops instead of this if you okay let me just first set it up so open the connection [Music] close the connection here the reason for me showing this is so that you can directly go and copy this and reuse most of them we're not gonna add any handler so here we are this is we are declaring RS basically this is the table stands for a corset and then here we are setting up what is our record set and then we are opening this record set now the table we want to open is transactions and not done so here you can specify like this as well select all star represents all from this table okay you can also specify like this or you can specify a circle here and then pass the circle here so open this table and then using this statement and then using this connection and this is gonna be the cursor type and a long time okay so here you can see cursor time this is the log type so if you want to edit then you have to open up in this argument so yeah so in this part is saying in this table add new using add new method and then for these fields we are assigning these values so if you if we try in this similar fashion so for the date I don't remember trying this out in Excel using this method but let's give it a shot if it works good that so first is date let's say again I will put it for the same date for a different client maybe this time line and a mark will disturb it let's say the man is like this now you can leave this open as well there should not be a problem okay gonna give it a shot syntax error in from close just give me one second this method seems to be causing a bit of problem with the sequel let's try like it is in the zone here we'll just specify the table like this source and the connection and the cursor time they this has to be the client ID the field name this can be return written also as in this format dot field and here you can specify the file name for example client ID you can also specify in this fashion okay but if the file name is straightforward like this without any gap then you can use this format so let's say we want to try for 10 always longer as compared to the subsequent ones let's see if it rot any value here okay so that seems to be the value we try to write for 58 point end and then the date okay so this method also works but I don't know why this part is not able to make it work but in order database and you know servers it should work okay so this is two ways to update in case you have to look through and update many records based on the table then you use the update batch method okay instead of this and you don't have to update every time after you write all the values you can update a batch as so on here okay so if you look here it's looping true two to the last row and then adding the records for example so you use this method if any one of you fine see this error there is you know a way to handle this as well so what sort for that now let's try update method and then we'll go to how to import all the data let's call this will just replicate this but instead of insert we'll use update update records so here the updates you call if we just want to not just give you a demo update sequel so update table name the column the value okay and then call them to really if you want to update more and then we're condition okay so I'm just gonna keep it here X is equal to this and then put it like this for now and we want to update this table and let's say that we want to update the amount field and we'll update the value something like this oh this remote this for now and then it's no space now let's say we want to update this value so our condition let's say that because there are many klein klein 1 repeating here let's say we want to update this particular report so our condition will be wear date is equal to this and then the client is equal to this ok so let's try this they do circle to this again this date has to go in this format and like we do in any other VA we'll just put in the condition I'm sorry this has to be like this and you called it this okay so update this table set the value this to amount where the condition did is this and the client ID is this let's give it a try now instead of instead of update we could just call it s Q alone that's fine now we don't make this update we don't need their insert so open the connection using the connection execute and then let's say we want to know how many records are impacted we can declare every variable as long and here we can specify so here is the command and records affected okay we can call this request experient oops sorry this is called a dis and maybe we can just give out dot print records impact account let's say just leave this open for now it's the same though so I sprinted here one record is impacted and if we go here this has been updated here okay so that is working you can follow this similar method as well to update for the sake of time I'm not gonna go through all of them but here for example instead of adding new you can just remote this and you can loop true and then you can update records okay so there are two you know multiple ways to update as well so you can use this Eco command or you can use by going through the records by the way you can open specific view by passing your filters like this and then you can update those records as well there are I can please refer to the blog there are many examples how to look through all the records and all this okay now I'll demonstrate a few examples how to import data first let's replicate yeah I think this process let's call this import data let's say that we want to import all the data from this clients table first give it simple so we'll call this team SQL as string to call is equal to so like again this star is basically all select a strip from this table and this instead of the fixed name we'll specify this we need to close the table so opening the table with the source connection again here we are opening the connection and then we're setting up the record record set which is basically a table and we're opening that instead of opening you know like the entire table you can also specify to open a specific filter data right so that it's faster in that way and then these are the log type and the culture type I'm just gonna leave that as it is this part's close the table clears the memory and close the connection you know now I think we'll be good idea as well for us to clear so that it does not create a memories yeah um okay and here so basically we are opening this entire client table and then from here we can use a metrical copy from record set and first we need to specify where we want to import the data to so we want to import it to sheet 1 so and maybe for now sorry we'll import it to 1 ok copy from and then RS so this is what we are copying to this particular cell ok whatever is the result of this Tikal ok so let's try to run this yes oh that was pretty fast let me close this and try to run it one more time so the best part about this I do here is though how fast it is especially now we don't even have to open the workbook you can directly import the data okay by just connecting to the Excel workbook using the connection so here if you see here it's just bringing us the data without the headers so in case you need to write the headers as well we need to implement that let's see that so let's say Phil Phil index something like that as long and then before we print the data we can print the headers first so for this is equal to the index of the field start from 0 to then our s dot will count based on the cursor type and all this this might not be available as well so watch out for that you need to set up correctly okay now here we want to now write it two second first row will be the headers okay and then here we can say so she'd one got this wrote this and it's gonna be the column we want to write to not value is equal to rst god this field and this will become the index dot name this should give us the name of the field okay so here it is gonna look through all the columns within the whatever is your selection if you specify to bring in only two fields which I'll demonstrate in a bit it will print that headers only okay so let's give it a try okay so here are all the headers and all your data set now let's say we want to import only the data let's say we want to bring in only few fields Cline line ID client first name state ok so let's say we want to bring in only these fields then you can specify that here so like instead of all we can specify this first let me show you this method this first name as well and the state and then next we can put in a criteria now we can clear out the data before we write it that cells clear well clear up everything so let's try ok so now we limited it to just bring in only three fills this all you can do it now if you want to filter on specific state let's say we want to bring in only the data from NS we can specify here where state is equal to NS no it was only bringing only this data for NS or we can also say or w-what was that the POA is it okay yeah so okay now this same code can be rewritten as I'm in the sequel sometimes this also should work unless there is specific restriction for EXO okay so this also works for the fields I did not put in the brackets but it still works now let's say that we want to bring in the data from two tables let's try that Before we jump into that let me give you a quick by the way you can go here and always check it out about how to connect two tables basically those are called joints now let's import data from this table for a specific criteria and then maybe we can get the attributes as well from here maybe we'll try to get the state along with this okay for a specific month or so so before I talk to you regarding joins I have this small table to demonstrate this so we will try inner join left and right join okay this name might be not familiar but it's very simple in inner join so for example let's say that we are joining these two tables using this in our joint it is basically gonna return let's say that we are linking these two tables when you are using inner join then it's gonna return only the records that matches in both the tables so let's say that this is our key for this our key and we're trying to link it okay so the records which are matching are Apple grapes and then I pull on grapes I think that's it okay we don't have been another side okay so it's just gonna return this to table okay so let's say that this is a map table and this is transaction table clients table transaction table now if you want the attributes probably the way you'll maintain is that you always update your client table first and then the transaction table will always be able to look up but then you have more options like left join and then the right join okay in the left sign is going to return all the table all the data from this table and the ones that are matching from the second table in the right joint is vice-versa so it's going to return all the data from this table and the ones that are matching from this table okay so we can acquit have this and then if you you have to find out what are the records which are not matching in those these two tables there are ways to tackle that as well so I'll quickly demonstrate that so let's say that Scrolls call to select and let's specify the fields we want to select first we want to bring in these fields and let's say we want to bring in first name let's say the state and then from we wanted to bring this from clients now we can like or not the end we can specify alias as well instead of for example let's say that client ID is in two tables so if you want to specify from which table we are supposed to fetch we can specifically say this client ID okay so instead of using this long convention all the time we can specify this as C or something like that so instead of this we can substitute it like this okay so for the client ID we'll take it from client table and then let's say we'll take this we'll set up an alias in just a moment so transaction table date transition table amount client first name and client table dot state and then this will be from this IC and then here we will set inner join we will join wait transaction table st okay so this is now in place okay and here so we have done the inner join with this transaction table and we need to specify the key okay and then we'll specify on now we'll say that C dot wine ID is called T dot client ID so we're saying link these two tables using the client ID this and this so it's like you're doing a vlookup using this client ID against this okay and then again the inner join will return in your result it's gonna return only the match matching records so let's say you are doing this in the vlookup if it returns na this sequel is not gonna bring in anything okay it's not gonna do any vlookup by the way but that's just an example okay and here you can specify your you know condition as well for example let's say we don't want to bring in for all the states but we want to bring in for specific state you can specify let's say again W a only the W is our clients you can't supposed to find this question so let's give it a try so quickly again select this field date client ID amount first name state from clients table C thus alias and then we are using inner join to join these two tables so it's gonna bring in only matching records you know joint two transaction table st and then on this the way this our link is client stable client ID with transaction table client ID and these are where condition okay now let's try this so there's more data now it's going to take a bit more time as compared to fetching the data just from one table okay like one here too or well so bringing the amount the clients first name and the state the date and the client ID as well okay so it's I think it's pretty simple if you don't get it at first just destroy it multiple times you'll just get familiar with this in just within no time okay let's just give it a try now a couple of more examples and we went out for this short series I would say now let's say that we want to bring in okay it doesn't make sense now to do the left join and the right and all this because we are not doing any comparison as such but just imagine that you have two tables like this I'll just create two tables here let's call this t1 and then I'm gonna create another table here and let's say this is t2 table one and two okay this is just to demonstrate how to use the left and right this kind of good one you have to do with a comparison between two tables so let's say that these two records the bottom two records are not all the scattered of it so the difference now is just this record here okay now I'm going to join these two tables using Quine ID again using the same don't the client ID the fields are consistent in both so now it really doesn't matter now one more example for inner joint transaction so instead of this we're gonna use t1 and then this gonna be t1 actually the order does not matter there's gonna be t2 or Table two and then just gonna call this as t1 only or a and B okay and the first table and second table okay and then that means we have to rewrite this part it's not much s a and then inner join table 2 as B and then we'll say a client ID would be client ID and we do not have where yeah okay now let's try this now it's supposed to bring in all the in front 16 records because this is not matching so this should be skipped okay well importing so let's give it a try ok so we have all these group items here it'll be good to sum this instead of dropping it and here we have till 15 only right so if you go back here second table has still 17 but it brought in only the matching record so that is inner join so now let's say we want to bring in I'll add more fuels here in table 1 so this are the fields which will not be there in second table here okay this from 20 to 22 okay so now let's try left join first and then we'll the right giant so it's gonna be same syntax this just replicate this just that instead of inner giant will try yeah left join first okay there's a cuddly so okay so now it returned all the data from the first table 18 records if I do this okay and then or it returned all the matching ones from the second table now if we do the right joint it's gonna do a vice-versa it will return all the records from second table and only the event matching ones from the second table so if we have like additional attribute here you'll be able to you know I visualized better but I could not set it up for the time saying I'm not gonna go about doing that now and now let's try to write join me do some pool okay I'm just gonna run this now nineteen records second table has what I think there was some caching issue earlier with there when we will try to write joint so I just deleted all the data and make the sample Lester so these are all the data here in t2 and here's in p1 now if I run this the same Seco that we create in okay this one right if I run this so it will just return only the older records from second table and all the ones that are matching in this table okay so you can see the results here okay guys just gonna be all for this video I hope you found it informative and useful if so do not forget to leave a like and comment if you do have any questions thank you so much for watching have a good day bye bye [Music] [Music] [Music] you [Music] [Music]

Channel: VBA A2Z

Views: 8,889

Rating: 4.9382238 out of 5

Keywords: ado vba excel, vba, ado, libraries, excel, sql, insert sql, update sql, delete sql, import sql, excel sql, ado lib, vba ado, select sql, sql with where, joins, left join, right join, inner join, excel vba, microsoft excel, visual basic for applications, excel vba ado, vba worksheet, how to use vba ado, activex data objects

Id: hGlsd9Nzig0

Length: 61min 23sec (3683 seconds)

Published: Sun Jul 12 2020