Zip pack those are 150 of my best workbooks i've got it right now for just 56 that's only 37 cents per workbook it's a great deal it includes a complete library so you can single click to access any single workbook or single click to access the tr associated training video that was with that so it's a great investment hundreds of people are really enjoying that so i think you will too all right let's get started right away so basically what i want to do i've got a completely blank workbook there's no code nothing on here other than titling the sheets and making a macro enabled xlsm workbook that's it that's all i have nothing this one we're going to focus on nothing pretty oh no no formatting no fonts nothing we're just going to keep it real basic in the workbook because i want to focus on the core and that is getting data from excel into google sheets.
And from google sheets back into excel once you learn this training once we go over once i show you this technique you can use it for anything in fact it'll be great to use google sheets as a database because it syncs almost immediately both directions and it's not a lot of code to write so i really want to get started so we're going to jump right into it the first thing we want to do is let google sheets know when we make a change so when i make a change in any cell here we've got to tell google sheets now we're going to use a third party it's hard to get authentication for google but there's so many great third-party products that allow us to authenticate google so we're going to do that so what we want to do is we want to write a little bit of code and send information.
To google sheets or to a third party and that third party is going to send it to google sheets for us then we're going to bring it back so we're going to go over every single step from the start so the first thing what we want to do is want to change effect that means when we make a change to any cell let's say on just to say on this sheet or any sheet i want that change to be sent basically to that third party and from that third party into google so we're going to go over that right now so how do we do that well that is a change event it's called worksheet change event we can get that into vba if you don't have the developers tab here to get into vba you can just go into the options and then just make sure you select the customize ribbon and make sure developers is selected there you can also use a shortcut alt f11 or use visual basic what we're going to be doing is we're going.
To focus on the code on sheet so we're going to start writing some code right on the sheet now this what we want to do is we want something to happen when we make a change to the sheet so that's change event so when you go in here in worksheet and then what you want to look at is not selection change but worksheet change event you're looking for change so this is what i want to do i want to write some code here so that when we make a change something happens so how do we do that well first of all i'm going to write if not so what that's going to do is i'm using auto hotkey i covered that last week if you want to see last week's training autohotkey to help me automate my typing that's great you can check that out okay so what i want to do is let's just go over from.
Defining that as an object as of now and later on in the code we will define them more so that we need that as an object and we also want to dimension a bunch of strings because i need to know the sheet name i need to know the uh row the row number and the column and all that so let's dimension also the url we're going to be sending something to that and it's going to be in the form of the url so dim url as a string and also in the format we're going to be setting it as a json format so let's dimension json as a string i also need to know the column letter what is the column right is it column a b or c so we're going to dimension that as a string so column letter as.
String and what else i want to know the cell value what is the value what is the text inside the cell that was changed that's going to be a string as well regardless of whether it's a number or anything it's going to be considered a string at this point cell value as a string a few more when we need to know the sheet name what sheet name are we sending it from that's important sheet name as a string okay so we got that what else and now i want i want to know the row number that we're sending it from dimension the row number as long okay that's it for the dimension let's go.
Ahead and determine the call letter now the target that's the change that was made that's the cell so target address would be the cell address and so on and so forth so let's get that column letter so the column letter we're going to use split for that equals split basically what i want to do is i want to get the address and i want to split it i just want to extract only the column letter a b c whatever so split is what is the cells and it's one we can use any row it doesn't really matter any row because we just want to determine the column and so what is the column it's the target target.column now the target column is a number if i wanted to keep the number that would be fine.
I would just use that number but i want the letter so i want to get the address of that so that's going to that's going to give us the address a1 including the dollar sign so it's going to be absolute so it's going to be absolute and the cells address so what else do i need so what i want is i need now we need the delimiter what is the delimiter i'm going to use the dollar sign because since it's absolute we're going to use the dollar sign there and also in this case i want one just one in this case so that is going to get us our column letter great what about the row number that's a lot easier row number it's going to be equal because it's a number target dot row okay that's going to give us a row number let's write in this row number and.
Then i'm going to write the column letter column letter here okay so we know what's going on there so the sheet name is going to be equal to just a name that's easy okay so now what we want to do now what i want to do is i also want to define the cell value what is the cell value the cell value is equal to the target value that's the text that the user just made the change whatever change was named so let's put this sheet name and then the cell value obvious that's kind of common.
Posts Related:
- Visão Geral Menu RIBBON Planilha Login Excel VBA
- Custom Variables
- Make Data Iterator & Array Aggregator Combining and Separating Bundles
Sense there okay so what else the level we're going to do is i want to set the object remember we've only defined it here's an object here so we really want to set it so let's go ahead and
Set that object i want to create an object so the set is going to equal the object h ttp is going to equal we're creating an object what kind of object are we creating well i want to create a server http request so let's type that in it's going to be m msxml2 that's the type msxml2.serverxmlhttp okay that's the request that we want to make so create object.Ms xml2 dot server xml http and now now that we've defined that what we want to do is we want to set the url so we're going to set the url is going to be equal to now i'm just not going to put it in right away i'm just going to put in some brackets i'm going to put that url in just a moment we have to get that url from somewhere so we'll we'll focus on getting that url but and then we're going to put the question mark in there so the url will go in place of that in just a moment so what else do we need now now i need all the i want to put all the information that we just have one put the sheet name the row number the cell value and everything else in there so let's do that right.
Now let's add that in there so what's the first thing let's put in the sheet name we can put any words in here sheet name equals and right so this is the name so this is the name that we're signing and this is the variable this is the actual data the actual sheet name which is here sheet name okay so we have that and what else we need to add more we need to add column letters so let's do that right now the column we can put in and sign right because it's joined that's the next column.
Equals and quotation marks and then we want to put the variable so column letter in this case okay that's the variable that contains our actual column letter and what else and then another and sign we have to do more and this time i want the row number wanna put the row number in here because we also need the column then the row and row number equals end quotation and again now the variable row number okay so another variable and then we need one more which is the servable so.
And again then again and again and inside the quotation marks and then we'll kind of call this cell value equals quotation marks and again cell value okay that's it that's all we have to do that builds our url so the only other thing that we need to do shortly once we obtain that url is placed in here and replace this so this won't stay here we're going to replace it but we have to get that url from somewhere and i'm going to go over that but we need to do everything in order okay so now that we've built the url we need to send that we need to send that information here so let's do that so we're going to again we're going to focus on the object http dot i want to open it and this.
Time open it and i want to write in the patch that's with the type we're going to use a patch and then url and then false okay now what we want to do is we've built that up and now let's go ahead and set the request header so the object http dot set request header and what is it it's going to be content type content type that's the type of content that we're setting and then it's going to be how what type of sentence can application json.
So that's the type that we're sending okay and then one more then we actually need to send it so that just builds it up then we send it so the object http dot send and then of course the send type is again in parentheses json and now what that's going to do is to send the information that's it that's all we have to type in all we need to do is build that url and that's what we're going to focus on right now okay so let's just go over this real quick because i want to double check my typing as well dimension the object http as an object we're going to bind a little.
Bit later dimension the url string json is string the column letter string cell value and the sheet name is all strings the row number is long that's the only long variable we have column letter we're going to split that we're going to take the cell address the absolute cell address break it down and determine just the first part of it which is the a or whatever column that was selected that's going to extract our column letter from the cell address the target the row number is pretty simple it's the row number the sheet name is the name of the sheet cell value is the target value so everything's pretty straightforward here again we're going to create this object now we're ready to create it and we're going to bind it now to ms xml we went over that three times url we're going.
To build it we just need to paste in that that's all we need to do we're going to get that now patchouli is false object hb send request the header content type application json send json okay that's all the code we need that's it to send everything to google but now what we do is we need to build this url where are we going to get that url well i've discovered a really really cool zapier alternative you know i found i found some obstacles in in xavier especially with the free version they don't we need multiple steps and zapier the free version only allows two steps so i found a very very cool alternative and we're going to call it it's called integra matte interest card.
Pronounced for me integra matte i guess that's how you call it and so what this does is it's pretty similar to zapier but i really like it's got a really nice ui it's got a you know larger
Free service meaning that you can do more with the free version before it goes into the pricing so there's a lot like let's go into the pricing just so you can kind of see it right now so the free version which we're using allows a thousand operations uh zapier allows 100 100 megabytes of data transfer that's not a minimum intervals 15 minutes that's not going to be an issue for us the only issue is would be 1 000 but we're going to cover that plenty so it's really amazing so what i've already signed up just sign in i'm going to sign in right here and.Then i'm going to go ahead and sign in so you'll want to sign up you can sign up using anything and now we have a so we can get out of the pricing we're going to go to the main screen here okay and what we're going to want to do is like in zapier you want to create scenarios or they call them scripts or whatever you want to call them so we're going to create a scenario basically what i want to do is we're going to start out with a web hook now we've used those before and that's just what we've built in excel this is this is called a web hook the only difference is we don't have the hook yet we're going to get that web hook directly from integra mat so i'm going to show you exactly how to do that so once we do that we're going to create a brand new scenario so i'm going to click create so we're into the home there's nothing in our dashboard now.
I don't have any scripts created they'll be here this is just from some tests that i ran so we can create a new scenario that's what you want to do once you get all signed in create a new scenario and you'll have to connect two things you'll have to connect dropbox of course you want to make sure you have a dropbox account we're going to use dropbox for a few different versions so dropbox is great but right now what i want to do is i basically want to send from webhook to dropbox is going to help us bring the information back from google sheets to excel so you want that to make sure you have a dropbox account of course you want to have a google a google sheets account google you know make sure you have google docs account that's important okay so let's do so what are the things that we need for this all i need is two things for this i need web hook so i'm going.
To type in web hook and that's right here i'm going to check that i need one more thing what else do i need i need to have a google sheets google sheets so we need that so google sheets so i'm going to select that so we have those two selected that's all we need for this that's for sending from excel to google sheets so that's all we need so we're going to continue now in the next steps in this step what we're going to do is we're now going to build that what they call scenario so when you select google sheets it's going to ask you to sign up it's going to ask you it's going to create a pop-up and you're going to have to link your own google account with that i've already done that but that's the step that you'll need to take so here's what i want to do the first thing what i want to do is i want to do a web hook that's the first step because.
From excel it's going to listen for a web book so we're going to create a web hook and we want triggers when the web hook receives data so that's what we're going to focus on click on there and let's give it a name okay we'll just call it excel sync okay any name is fine we're going to save that as soon as we save it we're going to get this link right here that's the link we're looking for all i need to do is copy that to the clipboard and then we're going to do is we're going to go in and you see where this url is i'm going to take out including the brackets and i'm going to paste it right in here okay and then of course we got to save our work let's go ahead and save it okay now what i'm going to do is i'm going to run this code now the way this code runs is any change all i need to do is.
Is make a change to any cell between a1 and z100 so let's just do that on let's just say a1 test i'm going to run test okay now theoretically that should have run the hook i didn't see any bug so that means it ran so let's go back into integral now we're going to check it notice it says it says successfully determined that means it read the hook so we're going to click that means yes everything worked good that means the information got sent so let's click ok and now what we want to do is i want to add google sheets so we're going to click add and i'm going to click google sheets here and what i want to do i want to update a cell updated cells what we're focused on so let's click.
Update the cell now what it's going to do is going to pull all that information in so what we're going to do is we're going to excel test we can just add a new name let's just call it sync to sheets that's fine any names fine continue so again let's go ahead and now it's going to want to know but now it's looking for a sheet so what i need to do is create a brand new sheet so let's do that i'm going to go into my sheet and i'm going to create a brand new sheet into google sheets and then we're going to call it it's called excel google think okay that's fine that's enough for a title okay so that's all i need to do so now what we're going to do it's waiting for this let's go back into that pop-up because it's going to need to know which account i'm using this account okay.
So it's going to set up it's going to allow us so now i'm just going to click allow that's going to allow make sure that it's a now it's found so now it's going to load all the spreadsheets in now we need to find the spreadsheet we just created so let's scroll down here and we'll call we're looking for it's an excel google sync that's the one i'm looking for that's the sheet i just created so what is the sheet now the sheets dynamic right because we just want to make sure that notice that our sheet name sheet1 we're just going to focus on sheet1 the name is important the name of this excel google sheet must be the name of the same excel sheet okay that's really important because we're going to use dynamic make sure your sheets are the same so ours are the same so that's fine okay so next up what do we need to do next up let's go back into here.
And set that as dynamic so what is the sheet it is in this case i want to map it right so what do i want to map it to this notice these four things those are the same four things that came in from our web hook did you notice that look those are the same names sheet name column column letter right and let's bring this we don't need this here column letter row number and cell value so that means all the information was sent there so this is the same information that's why we ran this code first so that it could get all the information now we all we need to do is map it so the sheet name we're just going to drag this over sheet name what is the cell the cell is going to be equal to what two things the column in the row the column.
And the row right column in the row so that's the cell and what is the value super simple right cell value okay that's it that's all i need to do and click ok that's it so now we're good now let's now let's run this once and we're going to run it now let's make a change and as soon as we make that change let's go ahead and test two and let's take a look at that and let's go back into there and i'm gonna i'm gonna show both of these things one issue let's take a look at sheet 1b1 there's a space there should not be a space that was a mistake that i made so all we need to do is click here it's going to load it all we need to do is we just can't have a space between these it'll add the space automatically but we really don't want a space right column letter okay click ok.
And now we're good i think let's just take a look go back here run once again let's make sure that test is again let's go ahead and make the change in excel call this let's you go here test three and let's just take a look at that now because i think we certainly didn't need that space okay that looks good the request was separated waiting for data take a look in here test three awesome now we're syncing now let me shrink this down a little bit i want you to be able to see both i'm gonna move this over here and now let's take a look and i'm gonna scroll this up here i'm gonna bring this over here now we can see it okay that worked out really well so before we do that let's just run once so we have to actually we have to actually make it run.
All the time so we're good i want it exactly as i don't need any changes so we can zoom that out so let's turn it on right let's turn it on actually i need to bring out this screen a little bit more here what i want to do is turn it on so now it's off and run and activate so now we're going to activate it so now it's running now if we go into our scenarios here we can see that it's on okay so go back into excel now let's go ahead and now what we're going to do is just what i'm going to do is i'm going to double click on this double click on this and make sure that those updates come now it came automatically to google you see that let's just run that test type in hello boom.
Now you can see comes right awesome see how easy that is now let's write something else now i want any change that i make in google sheets i wanted to come back to excel we're going to use the same type of integration but a little bit different and this one's going to involve just a little bit of code and dropbox so first thing what i want to do is i want to create a brand new scenario here and what i want to do is i want to take it this time from google sheets and i want to put it into dropbox and then i want to have my application look excel i want to write a little bit of code that's going to check a specific folder see if there's any changes in that folder take that.
Ever so every cell change that we make is going to go into that folder and then i'm going to have it constantly look in that folder if there's been any change i want to update that folder with the changes and put that in here i'm going to put it in small text files so basically any change that i make here in google sheets i wanted to send to dropbox in a small text file and i'm going to use again i want to use the sheet name and i want to use the cell address as the text file name why do i want to do that why do i want to use the same name because this way any change i make again any change i make again to the same cell on the same sheet i want her to replace that text file automatically replace so how do we do that again let's use let's expand this and let's.
Create another new scenario so i'm going to create a new scenario here and this time we're going to focus on google sheets so let's create that and again let's go again let's search for google sheets and click that and this time we're going to use dropbox to help us so let's also find dropbox and it's right here okay so just those two those are the only two we're going to use now we're going to continue and so what's the first step the first step is google sheets what do i want to do i want to watch changes i'm going to look for when triggers when a cell is updated watch only changes made in google sheets app notice sheets add-on required sheets add-on required okay that's a.
Little add-on for google sheets simple free quick no worries we got that covered so i'm going to click that that's the first thing we want to do now i want to write a web hook this is a web hook so what is it let's create a web hook on this and we'll call it sync to excel okay we're syncing to any name is fine it doesn't matter now it's going to create a web hook now again let's copy this address to the clipboard now we're going to go into google sheets and i actually have this add-on before but notice it says let's click it's intermittent but all you have to do is click get add-ons right and type in the words integra matter into integramat so you just type in into gramat right here click that i already have it and then all you do notice it's installed already for me but for you you'll just install it so good all you need to do is click here to install it.
Okay so now that we're done so let's close that out so you have it then what you want to do is go into add-ons intramats click on the settings and it's going to pop out this little slide panel here all you need to do is that url that you pasted just going to paste that right in here integra map so then we're going to save that okay save and successfully okay great so let's just write in another test right here let's call in test four okay so that what that's going to do is going to trigger that automatically now when we go back into it we can click ok and then we're going to click add another rule so let's click add what i want to do is i want to add dropbox so we're going to click dropbox now what do i want to do i want to create a text file a small text file create overwrite now overwrites important because if they make the same change to the same sheet the same cells we do want it overwritten so i'm going to click on that now what do i want to do i want.
To select create in this case i want to overwrite in case it's not and i want to select a path i want to do a map a file path and what is that path now we have all this information from that it got from google sheets so that's part of it what do we want well the first thing i want the sheet name i want to put that in here and this is the actually this is the file path so this is the name so we want the sheet name then i'm going to put it dash and then i'm going to put the a1 notation that.
Of course is the actual range so so for example it'll be a1 for example so then i want to do dot txt that is the file name so the file name is going to be whatever the sheet name is and then dash and then whatever the range that they've selected a1 b1 c3 or whatever it is text so this is the name of our file now what's in our file all i want to do is put the what's in our file is the value so i'm going to drag that over here the value sheet name dash range so that's it that click okay that's all i need to do click ok and now let's go ahead and take.
A look at that now our dropbox is connected notice but you'll have to connect yours it'll just give you a pop-up and it'll connect and just say okay on that okay so let's just take a quick look now now let's write in test five and i want to write in that now let's going to take a look at that so where is it going to be mapped but now i want it mapped to single there's one more step that i actually forgot to do i have i created a specific folder i want to do it in a specific folder let's go back into the dropbox here and just want to i want to map it to a specific folder because it's easier so let's write in that just as they say and then i've created another folder let's take a look here i've created a folder in my dropbox specific for this so that's going to help us out it's called excel sync called excel sync so this is what i want all i need is excel.
Sync so we're going to paste that in here i'm just going to paste it right here excel sync that's the one that i want it's actually a forward slash that's what i want make sure the slashes are right we're looking forward slashes not backslashes okay excel sync the sheet name so that's where i want it to go and i'll click okay that's important step okay so that's it so now we're good to go bring it back down here and click run once so we're just going to run it once to check and look at it then actually now we can make now we can make another test test six here.
And now go back into here let's watch it let's see what's going on perfect so you see one and one now if we take a look at that we'll see everything else let's go to google sheets first see what that is so we have the output the spreadsheet the sheet name sheet1 a1 notation d2 value test 6. notice that okay this is the the range is d2 the test is 6. that's the way i want that looks good what about this part let's take a look at what happened here bundle content file path excel sync sheet1 d2 that's what i want it's created the file path is right we're overwriting it we're.
Mapping it the path is sheet1 d2 text looks good the content everything looks good now let's take a look inside our dropbox and we see this little text file here if we open it up it's going to be exactly the contents perfect so notice how it send it to our dropbox now all we need to do is tell excel look at sheet 1 d2 and whatever's in put that put test 6 inside it that's it that's all i need to do so let's go ahead and write that code everything looks good i'm going to turn this on run it immediately activate that now we have our two scenarios double check them they're both on.
They're both running we're done with internet everything else is based in excel so let's go ahead and open excel and just write a little bit of code that's going to help us out so we'll create a module for that insert module and then we'll give it a name and the properties and we'll just call it sync from google sheets okay now we'll create a sub to close this out sub sync from google okay so now all we know we need to mention a few things because we need to pull it from the dropbox place it inside our whatever sheet whatever cell and then what we need to do is delete the file so we're going to mention the file name as a string and then the folder path.
As a string and then file path i was into the file path the full file path as string a cell value is going to also be a string cell value as string okay and what else i also need the cell address that's important cell add as string and the sheet name the sheet name as string okay good now we're going to set up let's set the folder path i want to set that already folder path is going to be equal to okay and so here's my folder here's my folder i'm.
Just going to copy that path i'm going to put it right in here and then quotation marks so we're going to make sure you put the last backslash okay that's important and then there we go there we go now we've got the folder path now let's get the file name the file name we need to set that it's going to be nothing initially is going to be the directory of the folder path and it could be any particular file that ends in txt and asterisk because we're looking for anything the wild.
Across just dot txt because we're gonna look for anything like that okay so that's the directory now what we want to do is i want to write a loop because i want to do i want to do this for every single file that matches the txt in that specific folder so how do we do that do while the length of the file name is greater than zero and that's the start of the loop okay so inside that loop we're going to check so the file path is equal to the folder path and the file name okay that's the full file path so we want to do is we want to open it extract the value and.
Then close it so we can do that with just this line of code open file path for input as number one just the one line that's all i need to know because there's just one line in it so line input it's going to be one line of code input number one we're going to assign that called the cell value that's going to be this the line the only line that exists and then we're done so we've assigned that cell value to the line input cell value and now we just need to close it so we're closing.
Number one that's it that's all we need to do the rest now what let's define the sheet name remember what is the sheet name right here is our file here sheet our sheet is sheet1 what i need to do is notice that this sheet one is separated by a dash so i need to extract from the sheet name i need to pull the sheet name out of that and i need to pull the sheet cell reference d2 sheeted the cell address d2 out of that so let's do that let's parse that let's extract that information from the name of the file we do that with just a few lines of code so let's do that the sheet name is going to be equal right i want the left i want the left of that whatever's on the left or in the left the file name then all we need to do is determine how many characters we're going to use in string to determine how many characters of the file name and what are.
