How to Create a MULTI-USER, macro enabled, Excel Workbook WITHOUT Using 'Share Workbook'

hello this is Randy with Excel for freelancers and in today's training video we're going to be creating a contact manager that can be shared and used simultaneously by users around the world without using excels shared workbook feature it's going to be a fantastic training okay let's get started I'm really excited to show you this one because there are so many advantages to be able to share a workbook without using the shared workbook feature and let's just go over for those of you that or don't know or need a little refresher course the shared feature workbook is here and basically when you select this option it it will tell you it allows multiple people to share a workbook at the same time and that's the that is what we're trying to achieve but we want to achieve that without using this feature and and why do we want to do that well as it says here the workbook should be saved to a network location well I want to share it with people anywhere in the world assuming that we have some sort of a shared folder and there's so many great ways to get shared folders and we'll go over a few of those like Dropbox Google Drive onedrive and there's a few other great but that's that's easy and free the shared the shared folder is the easy part so I don't want to use a network right that that means you know a hard network would be like computers in your office and also workbooks containing tables cannot be shared in tables is something that we use there's also lots and lots and lots of other limitations with the shared workbook feature with this shared work feature let's go over some of those limitations because that's gonna move right into why we want to do this I made a list here you cannot create an Excel table insert a delete these are things that you cannot do with the standard Excel shared workbook merge cells or split merge cells and we want to do that protect unprotect inside the code in fact one of my favorites you can't use advanced filters inside a shared workbook which is quite a shame you cannot use text to columns group outline data insert automatic sub tons and tons and tons of these of these features that are really really cord and very important to excel simply we cannot do inside a shared workbook well today we're gonna create a solution I'm going to show you how to create a solution that allows you to share your workbook data with anybody in the world with as many people as you want at the same time with this unique and incredible feature that I'm gonna show you and it's not too difficult to achieve so follow along and I'm gonna show you exactly how to achieve that and one of the things that we're going to be doing is we're gonna create a contact manager this is just a simple program and basically what it's going to allow us to do is create new contacts and save those contacts inside our lists always new save it and we can put address and then they're automatically saved down at the bottom of this list we can select on a contact here and update the address and then we can click update and it'll update the contact here we have the ability to refresh the contacts delete we can delete a contact so if we want to click on here we can delete the contact and it will delete it automatically now now I'm gonna go over that with you but we're gonna go over that quick the idea is basically that in this particular mini application that I've created we can create new contacts we can update contacts and we can delete contacts so the program is very very basic but that the idea behind this is that the data here gets saved and can be shared so that there could be 100 people using this workbook or actually a copy of its and all doing the same thing at the same time and sharing their data and then their data will also appear on this list so that is what we want to achieve now the important thing that we understand is you know Excel is is it is a spreadsheet program so we kind of have to get that out of our mind and think of it more as a application and any true application has what we call a front end and a back end and the front end is just what you're looking at here it's basically the a bill to enter data update data or delete data and it's what the user sees and that's called a front-end and what we have to do is we have to get out of our mind at the front end and the back end are the same right so what you're looking at here is not the back end okay the back end is this here and that's just basically a table right and this is actually this is actually a copy of the back end the true back end is simply a table and I'll show you what that backend looks like let's go ahead and into our file folder here and we'll go ahead and browse into into and pull that up so we can see our specific folder that we're looking at and that would be here under the shared workbook okay so we have a we have a customer data this is our database so we've separated it and what does that look like it looks just like it looks just like a table right it's nothing more than a table and so the idea is here that we have to separate the front and in the back end and the idea is that that back end everybody will we have to link to that back end so let's go ahead and close this all that this back end is is a simple table when we close that and when we go back into context and we add a new contact let's go ahead and add a new contact we'll call this person Jasmine Jasmine Smith let's see Smith okay and we give them a address 1 2 3 4 5 Main Street ok and a city Los Angeles and now we want to save that right and we'll see it save down here but what we're really doing is I'm saving to the database and then I'm pulling it and I'm putting it back up here so if we go ahead and we pull that database up one more time it's going to the recent look into that customer and you'll see down here is our new so every new one gets added to this separate excel sheet and that's important because it is this database that the other users will link into and I'm going to show you how that is done of course and then what we want to do is we want to actually bring the data back in here so there's actually three tables there's three tables so let's go over those three tables one is the list now this is not your original data and the reason that this is not is because I want to give you the ability to sort to filter data and I want you to do that here right sometimes you know in my prior videos we we have one clicks or we can click on a heading and sort it we have the ability to filter so I want to give you that ability and we can do that if you look at my older videos you know one-click sorting of inline filtering and things like that when as long as this isn't the original data we can do that so I want to put the data separate I want to separate the data and this is your database so the idea is to separate the front-end in the backend we've done that here so that means when you sort it or you filter it we can have that the idea is that I want to keep this table hid probably hidden right and out of the way so the original data is never touched right because when we filter it we don't want that touch and we also I want to make sure that this is always synced up I want to make sure this is synced up with the database in our folder that is the folder that is that that is that folder okay so I want to make sure that this customer data is always synced up and let's go ahead and delete this and I'm gonna create it again so let's go ahead and show you how that would look go ahead and delete that everywhere that's a new Dropbox feature so let's go ahead and create a copy this and I'll show you how that would work so what we'll do is we'll take this and then I'm gonna save it and then what I'll do is I'm going to also save it as so we're gonna create a new workbook multiple workbook and we'll save it at this just called shared workbook too and then we want to differentiate between this one and the old one so let's just put a put it two here so we can clearly see which is which now let's go ahead and you know what let me freeze this column two because I want to be able to scroll up and so we can do that that's going to be very very helpful so let's go ahead and freeze that and now we can scroll up and let's go ahead and pull up the one the previous one which is you know without the to our original which is right here okay so now we have to open and now let's go ahead and view them side by side so we'll go ahead and arrange them and let's call it vertically and we don't need this one open anymore let's close that and let's go ahead and review these again view them vertical okay okay so now we've got to open now we see there our original is here and our second one is here okay and to do that what we will be have already done and I'll show you how that was done is I need to link that I need to know where the database is for this right so we've browsed it and we know where it is and all we've done is selected here is the database okay so we know that's important and that's already done for both files so that is the only thing you would require your users to do is make sure you link up now if you have a larger program you will want to have a separate database for each and it's just because it's faster you don't need to in other words let's say you have contacts you have invoices you have accounting or you have I would put a separate excel file for each of them because the smaller that excel file is the quicker it opens the quicker it updates and the quicker it saves and you and speed is an issue here so I would have separate ones right and then you would just link them separately so let's go back into our original which is here workbook you know without the two let's go ahead and add a new contact and let's just call this person okay Smith we don't we can do without the additional and we'll save it alright so now we've saved it in this one and we have the contact save message now I want to go back into this one and remember we have the database linked and let's look down here there's nothing here right jasmine Sabbath Kay Smith and this one right is 19 right so now what I want to do is I want to refresh the data and you know you can put this on a timer so I'm gonna click refresh contacts and now all we do is look down here and we see it's here so and this will of course would happen on separate computers around the world right so the idea is that it just moved all the way from here to there and now let's go ahead and update Kate Smith let's go update on this one okay let's pick two and let's update now let's say this user has added a toast let's say we have Los Angeles as a city right and then we click our update contact on workbook 2 now we move back to workbook 1 and we do the same thing and we refresh the data refresh context and we go back down right and here okay Smith Los Angeles so we scroll up here so it's you see how it works it works both ways now now picture this all around the world you have a program you build a program that acts as a powerful powerful database front-end and back-end and users can use your program around the world you could have 10 users in different cities and they could all be working on the same application at the same time well he's not really the same application it's a basically a duplicate of it but it's not actually the same excel file now that is the difference with shared workbooks they're actually working on the same file in excel was never designed for that that is the reason there's so many issues with shared work words it was never designed with that and and even with all the limitations you get crashes you get Network failures you get there are so many issues with this and now the important thing is that ok if you look in this file I'm in a Dropbox ok that's important you want to be in a shared file so if you have a Dropbox and you have a shared folder it's critical you do need a shared folder of course we need to be able to share this data but what you can do here let's say you you don't want I have it in that let's go ahead and go back where we had the original folder which is here so you could only see this file this is the only file that needs to be shared we don't need all of these other files we only need to share the database so only the database files need to be in that shared folder and that shared folder should be shared with all the users I'm using Dropbox Google Drive would work too megha I have you know one drive could work as well as long as it's a shared folder among your users you can use it and and has to be sync in other words in other words when I make an update you know that that sync has to be automatic so it should become automatic and and it works really really fantastic let's go ahead and show you how we did that let's go ahead and close out workbook 2 and we'll just work with one you've seen how it how it handles so now we can go ahead and get on to the hood and see how we actually have done this we have a two hidden columns as we usually do in most of the most of shared workbooks because that's where we keep our code from so let's go ahead and show you what we have there not too much just a few a few things so let's go ahead and go over that weird when we select a row we use code and we just as we've done nine many other videos which you can see where the selected row will always appear and be one so when we change that it's gonna go so that we use with our conditional formatting to highlight that and we've been over that many times before but let's go ahead and show you under the manage rules well we've got a lot of once we don't need here but basically just some old we can delete those we have just two really two rules here we've got the alternate rows which we use in the table and we have the highlighted row which basically says you know if b1 is the row and that's the one we used so that is the one we that allows us to highlight the selected row we also have the selected contact ID and all this does is it can use put some code that tells us what ID what is the ID of the contact that we're currently working with this and this is gonna help us when we need to update that we'll need to know what row we have used a max to figure out what the next contact ID and I'll go over that with you and we need to know what the row is of the current selected so if we update that if I update that I need to know what update remember this row below is not our database right our database has launched sheet 2 he so we need to know so for example if we're working with Freight Frank Fred erst I'd be number five it's telling us our database Row is row six six of the database so when we go into customer database we will see row six is Frank so that's the one we want that's the one we want to work with so that when we make when we make a change to that if we make the change to the notes some all change and we click update we need to know what row we updating that's gonna be row six when we click update we know automatically that that that that's going to be right here it's going to change right here and then what I want to do is I want to take all this data and I want to bring it back and put it right here right so then so basically this is simply a copy of the data right I want to do that as well so we're gonna go ahead and do that let's go into the VBA in a moment let me just show you real quick okay so I used match here to get that contact I use a match and all we did was match the contact ID now the contact ID is a named range in that when we look under the formulas go into the name manager we just have one named range and we use offset because this is going to grow this is going to grow so we use offset it will count the number of cells with text and it will go so as we add as we add to this it will grow and grow and grow so the contact ID and the reason we also add one this starts on Row two so we want to know once we find once we've locate that which once we locate the ID right here we using this ID the selected ID we add one to that because we we start in row two right our first contact starts in Row two so we want the row number so the first one is in row number two the second one is in row number three that is why when we when we use match we add one I mean use master we had one and we're just gonna wrap that in an affair if there's an heir to that formula for some reason it's not found we just want to make sure that we put an empty so that vba doesn't run into any issues we can just test if it's blank that's all we need to do if it's blank then we know there's an error when it comes to VBA I also want to know if it's a new contact or not okay that's an important so when we click new contact this goes to true I want to know if it's a new contact because when I save it I need to know if I should have signed a new contact ID or not right so if it's a new contact right when we cancel that this goes back to false for example when we make a change test right I need to know we use the same button and the same macro for both update and save and I'll show you how we do that it's very cool but out so I need to know if it's on if it's an existing contact use D the contact database row in b9 if it's a new contact if this is true we need to we need to create a new row and I'll show you that how we did that in the code so when we update that on existing it's automatically brought in here so that is how we do that let's go in to the VBA and show you what we have there we'll go over quickly the contact macros because I really want to get into the sharing and that's the what that is the really the main focal point of this training but we'll go over quickly into the developers tab if you don't have a developers tab you can reach that from the file into the options and then just go ahead and click on the customize ribbon and you'll find the developers tab make sure that is selected alt f11 will get you into developers as well or Visual Basic by clicking here here is our VBA we have a few on sheet macros when we click on the Contacts we can see just a few we have a tab automated tab and we're not going to go into this because that is but if you want to see how we created these automatic tabs automatic tabs allows us to go from one to the other all right automatically that's a great feature I've already put that into training just look under Auto no autosave load tab Auto tab load save I'll find you the link it was under one of those and where we covered a lot of that so we're not going to cover that because we have so much to cover but basically the idea is you go in here and you pull down and then you just put the range here whatever the range of the cells and the order and that's what your tab is going to be so it's very very simple and then we just turn it on and turn it off turning on the tab when the worksheet is active when we deactivate the worksheet we turn it off so that's all that that the tab is we only have a little bit of macros on the sheet itself basically when a user makes a selection change to the dis where this is our main table inside that contact sheet and we're just gonna put the row b1 for our conditional formatting and then we're gonna load the contact we're gonna run a macro called load contact that is it for the on sheet macros let's go into the contact macros and I have all the cockers associated with contacts here we have new contact we have save update remember the same macro either saving a new contact or updating existing one we're using the same macro so that's a really great convenient we have to cancel new if they've if they've selected a new contact now they decide to cancel it right where that's a macro in itself but it's just a one line of macro we also have delete contact right we have load contact so load contact basically it loads the information when you select it loads all the contact information in these six cells above as well as the selected contact Ani so 7 min total we also have refresh contact table refresh contact table basically allows us to take information from the database from the database and bring it into the context so that's all that that macro does and what else do we have we have contact save message this is just a fade out message we've been through fade out messages before but basically when we update a contact and then we have a message that will appear contact save you see that little fade-out message right there that's a fade-out message we there's a training on fade-out messages as well so you can go over that but it just lets us know that a context didn't save or less user note without one of those annoying pop-ups that a user has to press ok on that is all the macros you see we can do all that save new delete with just a little bit of code I've refined this code over the years onto just the minimum just what's needed we're gonna go over a little bit quickly on this because I really want to get to the sink data but I do want to go over that with you the new contact all we're doing is clearing out the existing fields right we need to clear it out if we're gonna put a new one in and let me pull that up for you if we want to want to clear that up we want to make sure we clear out the fields so we clear that out and then we also want to make sure we clear out the selected roads like so we just clear those clear those cells out using clear contents we want to change the button sets right if you'll notice there's a different button set this group this group this group right here new update and delete are all existing contacts we use those for existing contacts right but if we click new we use a different group we just have two buttons we have save and cancel so this is a new contact group new contact group right so I've given a name to this group of buttons and that's allows us to show and hide buttons very easily so new and council two groups five buttons total into two groups so those two lines of code right here allows us to show and hide those accordingly so for new contact we are going to hide the existing group and we're going to show the new ones we're gonna set b10 remember b10 lets us know is it a new contact or not we want to set that to true I want to make sure that new contact so that we know whether it's a new contact and then f5 we're going to select and that just basically allows us to enter the name first it gives the user a little bit of convenience so that when we were here and we want a new contact the first thing they do is enter the name so and it's required so we've selected f5 automatically to give these so the user just has to start typing in that is why we have decided select so that's it for new content very simple now the next is save and update context saving update so we're gonna use the same macro for both but we still have to make sure that we know whether worse we are it's a new contact or existing and we'll put a test on that in just a moment the first thing we want to make sure is f5 is empty I want to make sure that there's at least a name so if f5 which is the name if that's empty we're gonna throw up a message box saying please enter a contact name and then we're gonna exit ok we're gonna stop the calculations and if you remember in our old videos stopping the calculation helps us to read re make it faster all we're doing is taking turning off the calculations and turning off screen updating we just have to make sure before the macro ends that we reset the calculation which turns all the calculation back to automatic and turns on the screen updating so that's what those does so you'll see that often in my code stop calculation now we need to run our tests we have an if and if right here we run a test we need to know if this is a new contact if B 10 is true then it's a new contact so the contact data base row is going to be the first available row in our database the first available row which is right here so that we're gonna we're gonna run a test the last one the last row with the text is 20 and so the plus 1 is 21 so it's gonna tell us 21 is the first available row so our database row is 21 if it's new so we do that if it's new and also we need to know sheet a we need to know what the con the new ideas what is the new ID now we want a unique contact ID for everyone we need to make sure how can we assure that the next ID is new we can use the Macs and I'll show you how to do that so our next ID is located in b8 let's take a look in b8 and see what isn't there that is the max that is the reason of max hello there and into the context we go and under b8 what we're saying is the max of the context IDs plus one so all it's gonna say is the maximum what is the maximum of all of these in this case it's 19 and then plus one is 20 so that's gonna give us our next contact ID so we know we have a unique contact ID for each and every one of those that's gonna be very helpful moving forward you always want to have a unique ID and especially when you have a database like if you tend to change customer names if you have invoices or things like that you use the contact ID because the contact ID won't change so that you always have the right contact along that way you can have the ability to change the name and then records associated like that like invoices like payments like scheduling are always going to be with the same contact even when you change the name so it's really a good idea to use a unique ID that's hidden that's that the user cannot change and that's so it's really really important so that's why we do that there okay next up so if the existing is new if the existing is new we're going to do the new the new contact row the first available and we're getting assign it a new contact 8:00 else if it's existing then we do two things then first if we want to make sure that b9 if it's empty if there's no row associated with that remember that b9 is our contact row I just want to exit that sub actually look if we exit the sub we have stop let's do one more thing we should we should actually reset the calculation remember before you exit the sub reset we have two choices we can do reset calculation here right so we can do that or we can bring stop calculation past here right we can do that too if we bring if we don't stop it then we can we don't need to reset it let's do that so we have two choices because I like less less code so because there's not much going on here there's not much to slow down so we can exit the sub without resetting it and then we move stop calculation which is R which is a calculation to manual and screen updating so we'll bring that down here then we don't need to reset it it's just kind of nice so if if we know if it's an existing contact then the contact database ro we already know is in b9 remember b9 is our contact ro here b9 that's our database ro so we know that if it's an existing one we know that based on using the match so that so we're gonna determine our contact database ro is either if it's new we're gonna give it the first range if it's an existing we already have it that's all we need to do now the rest it doesn't matter whether it's a new contact or whether it's an existing contact everything else is the same and that's really helpful so all we're going to do is we're gonna run for next and we've mapped out these fields and I'll show you how that works and we've showed you before but I'll show you again because it can get a little bit confusing we've done data mapping the idea is this ID is in column 1 customer name in to address in 3 so let's go ahead and Center these it looks a little bit better but it doesn't really matter it's a database maybe nobody will see it so the idea is that one two three four so we know the column numbers that this is that's very important so if our customer name is in column number two and the address is call number three I need some know I need to map that I need to know that this is column number two and I know that this is column number three in the database so we've mapped it right here database column mapping customer name is two addresses three all right so we know that so if we if we are here and this is column equals column number six this is column number six right this is column number six and we know if we look exactly ten columns ahead that it's to remember this is calm this is calm 16 equals column right so that means let's go back here so that means if we go the the existing column right column column 6 plus 10 is is 2 right calling the customer name is to the address is 3 the city is 4 so this maps out which column in our database and basically this is just a way a quick way of writing less code a little bit more work on the front end but much much less code because what I want to do is this I want to run through each column from I want to go from column 6 to column let's say seven eight nine from comb six tonight and I want to go from row five to seven to nine so I want to basically look through these six fields I want to look through them I want for each one I want to get the I want to get the column of the database I know the row of the database right the rows either it's going to either be this this on a new or it's going to be if it's existing it's going to be here right so we know we've already defined the row we need to column of the database and the columns right here so all we're doing and I've done this in a few trainings before all we're gonna do is loop let me use two loops one for columns from column six to calm nine and one loop from caught from row five to row seven to row nine so remember six we're going to go from six to nine but we're gonna we're gonna step three because there's nothing here and there's nothing here so six to nine step three from five to seven to nine step two so I need to basically I just need two loops to go real quickly through that and I've done that right here for the row equals five to nine step 2 that means skip that means go from five to seven to nine we want to skip that we also want to go through the columns from column six to nine and step three that means only six and nine basically it's just going to go from six to nine very easily so then we only have to write one line of code that means does sheet to the contact database row we know that row and the column what is the column the call is the contact ro whatever ro were on plus the contact column plus ten remembered ten aways is where we map them what value is this what is the value of this the value of this is is right here the value of that is right here right here right it's either two or it's three or it's four or five or six or seven those are the columns in our database right two three four five six seven right so those are the columns so that now we know what the column is we've mapped out the columns we have the row we know exactly where to put those values so we have this is the column we have the row we have the column and we know what the value is right so this is where it's going to be located and what do the cells plus the contact row at the contact column so we're gonna look through that and just basically all that's gonna do is put the data in the database that's all its gonna do and what's going to go to loops now that's it we're done so it's very quick very simple very easy when we do that kind of data mapping it lowers it's a little bit more work on the cell on the cells but it's it's much fun it runs much faster and it's much less to code b10 we're gonna set the new clients no longer a new contact because we've just created it so we want to make sure to set that to false and we also want to set the time this is going to be handy I need to know that we just made a change so we just made a change so we're gonna put the time and I'll show you how that's going to help in the in the future but that's important the last change we just made a change so we want to put the current date and the current time right here so that's important so we're gonna put that right there into b12 and next up we want to make sure that we change our toolbars if it's a new contact we want to make sure the existing group of buttons are displayed and if it was a new context for sure we just created it so it's no longer gonna be a new contact so we want to make sure the new contact group is no longer visible and then we're gonna run a macro called sync to database and then we're gonna run a macro called refresh contact table and then reset the calculator and then we're gonna display the contact saved messages so we have three macros to run reset calculator you saw that already that's just basically turning on automatic calculations and turning on screen updating so we know it what's in there we haven't showed you what sync to database is yet and refresh contact table I did show you that briefly and all of that is is all we're doing is we're gonna take we're gonna clear out whatever's in d13 through j99 and we're gonna take whatever is in we're gonna determine the last row of the database and then we're gonna copy everything over and I'll show you that what we're doing and then I'll show you again in the code we are going to clear out let's go ahead and hide this for now and we're going to clear out everything in here remember this is not our data so we can clear it out right I can I can easily clear it out it's it's it doesn't matter I can just refresh it and then it all comes back so it's no problem there so we can clear that so the first part of the code just simply clears all the data out and then what I want to do is I want to determine the last row here and I want to take all the data here and bring it over into there and I don't want to use copy and paste I want to use values because we're just copying the values and it's also quicker so we're gonna bring this value over we know we're starting on row 2 here and we're going to row 20 here we are starting on row 14 so excuse me row 13 so if we start on row 13 here we need to make sure that we are adding 1111 because we're starting much higher than we are starting on here 2 and 13 so we're going to do just that within the code here so we're gonna clear out the table just like I did there we're gonna determine the last row of the data in the database in sheet 2 then we're gonna do is we're gonna take d 13 plus the last row plus 11 right plus 11 because we're starting much higher and that equals a 2 through G and the last row of the data so all this does is copy over the data so when we refresh this and I've assigned this macro a refresh contact table to this button here right click right click not duplicate it right click and then inside assai Macra and you'll see whe this is sync from database but that does that that includes that as well sync from database and we'll go and that also includes the Refresh contact table so we'll go home go over that so that brings the data right back in so that is refresh database so all we want to do is make sure that when we that when we make a change here we want to make sure that any change that we make is also brought back into this table here so that is why we use refresh contact table after we after we save or update a contact we need to make sure that those changes are better so we're also gonna run sync to database and let's go ahead and go over that and we'll go over that amendment let's continue with the the contact macros cancel new all we does all we do is select ether teen because when we select ether teen automatically we have a macro that runs and so if we're in a new contact and we click in new 13 all we it's just the same thing new contact cancel new is the same exact thing as it would be selecting an existing so all I need to do is write a macro that does just that ether teen select so I've done just that ether teen solo ether teen select deleting contact well we want to that's very easy also we need to make sure that there is actually a contact that we have an exact row b9 is tells us our contact row which is right here under b9 remember that was a that tells us our contact database row right here so that's our match b9 tells us row so we want if there's an air it's gonna be blank so we want to do is first run a test to make sure that it's not blank and if it's not then we can go ahead and delete it you may want to put in a message box anything like are you sure you want to delete this contact I didn't do that but you very well may want to do that as I've done in the past so we determine the contact database of row and b9 and then all we do is cheat - we delete that row contact database row and contact image or delete and then we update the last local change right we updated we've made it made a change in database so we want to update that time we need to know when that was updated that is important for syncing and I'll show you that in a minute but there's a reason we're showing the current time in b12 b13 a select that means we just select last we sync to the database and we refresh the contact and seeing today based I haven't gone over that yet loading the contact we clear the current contact information out we update the button set we pull in whatever contact row that we have we we determine the contactors in b1 and then we load the contact row and I'll show you how this done but we use data mapping once again but a different type of data mapping let me show you that as well that is in 11 this is a hidden row and you'll see here I've mapped out these are all mapped out this tells me customer name is located in we can delete the column number we don't need that anymore f5 is the contact name I 5 is the address and so on f7 is the city so I've mapped out here under hidden row all of the cells so all I need to do is when when I'm loading a contact I just need to go from here we're gonna put I'm gonna put the ID right right in a let's be contact that goes in b2 contact ID goes in b2 everything else goes up there let's make this small we don't need to hide them just make them small okay so we know where the contact de goes and everything else all others six other fields go in here and we've map those out so we need to know 11 so I'm gonna run a loop we're gonna run a loop from column 5 through column 10 5 through 10 we're gonna run a loop through that we're gonna end each one we're gonna say ok in row 11 in column 5 put whatever is here whatever in our selector line put that in f5 write whatever is in our selected row put it in I 5 so and so on and so forth so we run a loop using mapping we've done just that in the code run I've run a for next loop from contact column from five to ten then we're gonna say range that columns 11 the row is 11 that's where our mapping contains and we're going to say the contact column what is the value what is this value cells 11 contact column what is the value that value is going to be an address have five right or i7 whatever that is is going to be here right we're gonna put that that is the range and so that range equals the current contact row in the current cells so basically all that does is what I said is put whatever it's gonna run a loop right it's gonna run a loop from column five to comb ten from five to ten it's gonna whatever's in our selected row it's going to take that and it's going to determine where it goes and it's gonna put it right here or here or here or here here so that is exactly what that does you can follow it along I know I'm moving quickly but there's more important things that we need to get to and we're already good thirty minutes into this training so it's gonna be a long one get your coffee don't fall asleep all right continuing on b2 we want to make sure we put in our contact ID so we know what the contact is that's going to go into B - that's important and B ten we want to make sure that we set it's no longer in if we've selected an existing contact it is no longer a new contact just in case we were adding a contact so we want to make sure to set B ten is false no longer new contact next we've been with we've done contact refresh and we've done contact save messages just so that is it for all the contact macros and that is basically how we go through our contacts whether it's new contact update delete and refresh so that is how we do all of that let's get into the most important part which is sync and that's done relatively easily in just two basic macros we have two basic markers we have sync to database and sync from database the idea is is this that we want to that we want to either take this data and get into our database our database is this remember our database is that excel file that had custom data XLS that we've browsed for we've browse for it we know we know we want to make sure that everything gets in disk custom data access we want to put the data there okay that's sync to database now sync from database is I want to take whatever is in that excel file whatever's in that excel file and put it right here sync from database takes that excel file and puts it there so those are the two macros that we need to do we either need to put information into the database or we need to pull information so we're either pushing data into the database or we're pulling it from the database so let's go ahead and go over to sync to database and this is how we've done it now before we do we've defined some objects for the file system if you follow any of the last four videos under the file system object if you have not it's a great training on file systems where we created a file manager and that was really amazing so make sure you check that as well we did cover the file system a lot and so we're gonna do a little bit on that today just because we need to get an update date from the file we're gonna create some connections we need to create a data connection so we're gonna we've dimensioned the connection and the record set as objects and then we've two dates we need to compare some two dates I need to know what our last database update was and our last local change that means the change our last local change is right here remember we've been we've been updating that and I told you the last local change is right here so every time we make a change whether we're updating a contact or creating a new contact or deleting a contact those three types of changes every time we do that I put the now time in right here into b12 is the now so that tells us okay we just made a change so that's an important we need to know when that change was made so that's done so the change is made and then so that's why we need last local change and the last database update so if another user has updated their excel file I need to know when they made that change as well I need to know if there was a change I need to know and where is that that is right here let's go ahead and do the shared workbooks I need to know this right here if somebody in our shared folder made a change here let's say 610 345 and let's say their change right let's say they made a change five minutes ago and we made a change 15 minutes ago well I need to make sure their change takes precedence so I need to say okay before we delete before we do anything bring in take in their changes and bring it in our database then make our changes so we need to know this date modified that's critical right so it's important to get that time that time right so we need to know what time there is so that we're gonna use that as the last modified date of the database that when the database was last modified so we need the last database update we're going to get that date now when we sync to the database we need to we need to first of all we need to get the database file and that's an m5 remember that's the entire file path of our database so that's always important so every user will need to do that we need to know fm5 m5 what is the entire file path of that database so that's important without that we can't if you when you do this for your work but make sure you run a check here I didn't get into that make sure you run a check make sure it's valid and make sure it's not blank you'll want to put some checks on that so go ahead and do that so we've the database file is that I need to there we go so the lat the database file is m5 the last local change I just showed you that b12 remember that's the last time we located and now what we're gonna do is we're gonna set that object filesystem this just creates a scripting object it basically allows us to get access to our folders because I need to know what the change date was we're gonna set the object file to our database file this is our database right and we need to access that I need to know this then I want to run a check once I have this information object files database that is this right here all I've done is said okay this is it get this I need because I'm gonna need information from this so we've just told a DBA T that we want to work with this file right here what do we want from that file I want the last modified date and I want to know something about it if the object file right date last modified date is less is less than the local change that means if it's older that means if are it that means if ours is newer right if I our if our file is newer and and and we have a newer change in them then we can go ahead and we can delete it and make sure we replace it with a newer one right so what we're gonna do is we're gonna say okay there's no new changes that means there's no new changes on our database file there's there's no make that a little bit smaller there's no new changes here so that means we can take this data this data right here create a new database file delete the old one and create a new one and put it in place so we can do just that so that's all that that code does kill it deletes the current database version then it's gonna take this workbook sheets customer database and it's gonna copy it's gonna take this sheet only this sheet we're gonna create a copy of it into a new workbook and then we're gonna put that workbook and we're gonna say the file format here is it means 51 is XLS X and then we're going to save that and we're gonna close that file so that's all it does right so we're gonna copy this workbook we're gonna save the file name save as the file name the same name as before remember we need to delete it first we can't we cannot copy and place it in the folder if it's if there's an active copy so we need to make sure we kill or in other words delete the current database version before we create a new one so we're gonna copy the sheet then we're gonna create then we're gonna take the current not the current workbook the current sheet right this worked with just the sheet only that one sheet and we're gonna save that sheet as the same name database file and then we're gonna give it a file format a 51 which is xlsx we don't need macros in this workbook it's just a simply cell we wanted to open quickly we want it to save quickly and we don't want any we just want a very very simple database so we're gonna give it xlsx and then we're gonna close that we're going to make sure that closes so when we do that all when you do is run it we're just going to click that and it runs really quick and that's it and that's only if and that's only if that our file is is newer than the old file okay but what if it's not what if the old file is newer right what if the what if the object last modified is newer greater than the last local change then what I need to do is I need to take the information that is in that database I need to take the information that is in here and bring it right here okay let's make a little change let's just do 20 right and this is in our database right and okay so let's save that that's just the straight database change now we've made an update to that right so now when we run that code I need to know okay if it's now we just made a we just made a change so we know that right now 6:10 4:16 p.m. that's that's new right that's newer and what about when's the last change in our when's the last change in our the last change is six nine right 3:45 so that's much older right so that's gonna tell us hey we need to before we like before we make any changes here we need to pull in the newer data first the newer data first must come in so we're gonna do just that so what's gonna say if the last modified date is greater than the last local date which it is then we need to do that so when we run that so when we run it let's go ahead and pull up the database right here and so when we run this we just click run and then that database is going to be added right here you see how that was because we need to know so I just ran this code so let's go ahead and go over this code so that is the reason for that the last local change again we're gonna define it in b12 we will set the database file which is m5 same as the other ones again we're gonna we're gonna get the scripting object because we need these two lines when to get that to get that last modified date of the database and again we're gonna run our if statement but this is the opposite if statement if the last modify date is greater then then run it then what we don't what do we want to do we create a connection this is called an I do a do database connection and it's basically what we're doing is we're going in here we're going into data and we're creating and then a connection write a new connection right we're gonna create this connection we've created this is what we're doing but we're doing it through VBA we're gonna connect the database so that is what we're doing but we're doing it through V be a so once we create and this allows us to pull in the data from the database without actually opening up the file which is actually really fast and really convenient so to do that we need to set our object connection and then which connects to the actual file which will connect to the file and we need to create a record set so we need to create these two connections first and then on the connection we need to open it and this tells us what provider this is the newest version and this would cover Excel 2007 and above I think you would use 8.0 and something if you're using less than 2007 so if you have an older version older than Excel 2007 well maybe you should update it but if you're not updating it like using 2003 you will have to make a change here I believe you'll have to use 8.0 and maybe something different than AC II but this will cover any Excel version in the last ten years and I've tested it on on newer versions too as well as 64-bit should work just fine on that as well so what is our data source our data source is that excel file in m5 that is the source and also we're gonna say extended properties this is basically we said yes our database includes headers right so we've done that and then I'm X equals zero not too important but just included then the record said okay so now we've connected the database we know but what records do we can include so we're going to select from sheet the sheet is customer database that's from the sheet there is no specific range because we're going to just do the whole sheet although you could specify a specific range as well although we've kept it pretty simple just from the sheet and then we're gonna set the object connection we don't need these so we'll get rid of those and those are those are optional but for our purposes we don't need them and then what we're gonna do is we're going to say okay with this record set I want to copy them I'm going to copy them and so we're gonna say sheet 2 a to copy so we're going to take we're going to take going to a 2 right here a 2 and we're gonna copy that from the record set so all we're doing is copying the data from our database into into our main file here so we're doing that right here from the records as all that does is copy and then we're going to close the connections we want to make sure we close the connections those are important and then what we're gonna do is we're gonna refresh the contract table contacts table all right so we've got the information here but I want it also here because we probably may end up hiding this sheet but I want it here I want it here so that's how we do it so that's how we get all the information in we have to we have do so that that's easily so when we make a comment we make a sheet write new contact update contact all of the information will run into this and then it will update the database and this allows us to use multiple users that's it but just those two simple macros that is how we can create a shared workbook with any type of file or any type of folder and that gives us extreme amount you should add more protections in here you will need in your file you would like if you're gonna release an application you will want to you will definitely want to make protections protections on the on the sheet protections on the database so I've given you you know the ability to do that but if you're gonna release an application you will do you only a lot more security right because obviously users cannot maybe access this or cannot delete this you will need to protect lots of forms and files you calmed columns so the protections will be critical you'll want to do that as well but that is basically it we've gone through every macro browse for folder this is just a simple macro that allows us to pull in our file into m5 so that I've been before that's pretty basic and so that is it a one other thing on workbook open right you may want to sync like every time this workbook opens in your file you're gonna want to sync from the database you want to pull in any new data that's been recently I've commented this out but if you have a running application when you open the workbook go ahead and uncomment this out so that you you're at your database is automatically updated as soon as you open the workbook that's kind of important so but I'm gonna comment it out I don't want this to run when you open your file so it's not-- there's no need for it to run when you're when you're just learning but eventually it's something that you'll want to do and that is it this dis my car just sets the tabbing this is for tabbing when the workbook is activated and only on the contact sheet that way no other context sheet and that is pretty much it so this is probably one of the most powerful trainings if you can see the power of this you can design extremely powerful extremely professional workbooks that rival any major software out there because you now have the tools to use one of excels biggest limitations but you now have the power to create massive applications that users can share all over the world with the same worksheet and a linked database it's extremely powerful so I'm really really glad you're able to see this I just ask all I ask is that you do share this training whether you're on YouTube or Facebook please share it please like it and I always appreciate your comments below and thank you so much for joining us today

Channel: Excel For Freelancers

Views: 148,440

Rating: 4.9308038 out of 5

Keywords: Excel Shared Workbooks, Sharing Excel Workbooks, How To Share Workbooks In Excel, Excel Shared Workbooks Alternative, An Alternative To Sharing Excel Workbooks, How To Create Excel Multiple Users, Simultaneous Users In Excel, Sharing an Excel File

Id: WI-uhkrnk2I

Length: 59min 32sec (3572 seconds)

Published: Tue Jun 12 2018