Table and then you have to slice and dice the data of that sort so let's take this example and let's say that we have to import this data which is um for example like the file name has been set up in that convention that d represents the transaction and then the date in the format of you know like uh mm dd and then yyyy right so and then this is basic uh data set which is the date uh the same data is the file has and then the amount okay so let's go.
Back to our first lesson i'm gonna just take this um i'm just gonna take this ui that we created and i'm gonna paste it here in the first fourth lesson i'll first paste the width and i'll paste the data and then the format i'll just remove this maybe for this example we'll just rename this as admin admin control something of that sort.
It's up to you and then we'll just say v1.0 and so this one can be the folder from where it the data will be imported so this will be the folder name for now i'll just base the path of daily jobs like this and this can be longer like this.
Okay and next will be the file file name so using this real example i'll show you all about handling uh how to you know like control things that you put for example so the file name can be automatically calculated let's just take a sample of this file let's say the user enters um not the file name let's say the input the date let's say this is 831 2020.
With the file extension xlsx like this so once we have this you can cut this and concatenate with the file path so normally this is how you can construct this uh pods give me one second equal to and this okay so this will be our file name uh so now i think we can just rename this to file name this will be based on the date that the user will select so i think it's makes sense if we just move this down.
Somewhere here something like this and for now we'll just get rid of additional things just get rid of that so i'm not gonna spend time to do this part but this is this basic ui you can build to uh instead of just letting the user go to the view micros and random micros i think you should build at least some basic ui uh to guide them okay so.
Let's do this the brighter one will be at the bottom here and there so this will give like text look and feel but okay all right so let's say that um last execution state desk can be here it's okay to have that and maybe you will just grab that button as well.
You'll save us some time and let's say this is import data that will be our first step let's say this is a home or something like this put in a first step um so whenever you have to uh work within excel if anything can be recorded quickly recorded now that you're already familiar with um how to deal with objective properties members uh methods associated with that so um don't waste your time typing out all the.
Codes if possible you save out all the codes that i'm also about to talk in a bit just try to uh save the code that you might use frequently so that you you don't have to type those things again okay all right so now let's go ahead and uh record a small micro and then i'll tell you how to edit that um so let's close project one for now.
- Website Showcase Mockup Bundle Download In PSD Files Sheri Sk Photoshop Tutorial
- 1200 Pro Banners Templates Free Download In PSD Files Sheri Sk PSD Banners Office
- How To Set A Fixed Position To Elements In Your Website HTML & CSS BlogSpot
And so my ui this editor will look a bit different - ID Card Make
Again like uh in the channel there are so many resources how you can customize this basically i've dragged all this top version here in the corner and then i've put in the um explorer this site okay so um yep nothing have changed just the look and feel so if you want a customization like this there's already a video i'll you know like walk you through additional resource.At the later stage so now i'm gonna have this here and this one here so we can see side by side uh what is going on with the code record and then now we want to get this file for example so shift right click uh okay save right click copy as spot okay so the recording has started everything you do i if you're new everything you do is going to be recorded here now so ctrl o or you can go here and then.
Open and then browse put in the file name okay or you browse to this spot and then select this file and then open it as read only it is advisable so that you do not you know like by some error or something you change the actual file it's a good practice to do that okay so now you'll see the recording here okay so this is the recording that is going on now so as you can see this part is basically bring.
Navigating it to the directory and then opening up this okay so so far it does just open the file and you know like we can make sure that it's activating always remember that you have to be very specific in your code okay and then yes of course you can dynamically change all this uh as well but you have to be specific okay open the book and then from which book you have to activate which sheet and all this so on and so forth so let's keep it simple for now i'm just gonna select everything so as you can see here uh it's uh has.
Selected the range it's just uh resize the application with okay so now i'm just gonna copy this selection copy and coming to lesson four workbook so far there is no uh data tab so i'll just name a data tab and then i'm gonna paste special and then i'm gonna paste it this values and number formats okay and then now i'm just gonna press escape from my keyboard and then you'll see that application cut copy mod.
False this is basically clearing the clipboard and now i'm gonna go back to this file you'll see here's read only so that confirms that it opened read only and i've closed the file and then now i can stop the recording okay so pretty much that is how the data import is going to be done without any filtering of any sort okay so we'll go further more advanced from here okay so this will be our base.
Now from here let's say that uh instead of writing everything just do a recording like this and then you quickly clean them up okay so from here what you have to do is uh this part opens the workbook okay so i'm gonna get rid of this we do not need this i'm gonna change the name of the code to import import data and then i'll say uh we'll declare uh wbs workbook and we'll say anytime you're.
Using uh excel objects or you know like objects you have to set okay you cannot just say uh wb is equal to you already know this but just a reminder you cannot if you do this it will give you an error so you have to use the word set okay you have to set the object variable so once you do this it will give you an error because once you're using this method you have to open the bracket like this and close it like this so now we have set up the workbook variable and then we have uh you know assign it.
Assign it to you know like the workbook that we want to open if you don't use this method also it's okay but uh if you use this you can you know like further you can see this workbook.activate just to make sure that uh during the process there was no certain other interruptions and then this one is going to come from obviously the file which is here okay based on the date so let's say that this range name is file name okay so i put a name there and let's.
Declare uh dim file name as oops file name as string
Okay now some of you asked me uh when i put this dollar sign here like this it's the same thing okay if i put file name as dollar right prefix it with um i mean suffix it with uh dollar sign that means this basically just represents a string okay it's the same as writing it in this fashion so uh again you have to spend time.Yourself you have to uh play around so that you can be more familiar if you just practice the one that i'm showing you then you cannot you cannot do the real uh projects okay it's going to be very difficult for you it will take lots of time as well so the more you spend time to play around no things then you can build a better more efficient application as well so anytime you're confused with something uh you can shoot an email to me as well or you can google it obviously and then or you can press f1 for example.
By putting your um you know like cursor on the this data type once you press f1 it will navigate you to official side where there is a documentation online documentation which is updated all the time okay so we are debating a bit but this these things are very important that i have to tell you so once you go here um keywords so let's say uh string.
Variable so once you come here i press r hold on data types and then once you go here this is the one that i was talking about in the beginning oops sorry we are going to check the string okay so once i click on string you'll see that it has explanation about you know like uh the limitations and how much string you can be stored characters and so on and here you'll see this as well it is.
Mentioned here that it can be declared as a dollar sign as well okay so similarly for long it is uh uh n percent and then um for double it is hash hashtag for example so you can you know like uh uh take time to review all these things okay i'm just gonna close this so now we have set up the workbook if you want more attributes i mean to pass more parameters once you put a comma you'll see where you are now this is read only it's true and then if you want you can um here um.
One important thing is update links so for example you don't want to update the links let's say the workbook is updated links so you can just write update links and then you can see false or true okay something like that otherwise it's gonna prompt you um you know so there are more tips around that i'll come back to that so we've opened the workbook we've activated the workflow just to make sure and here we're gonna select data selecting and activating if you select it will you know like navigate to that.
Particular type tab from this file but if you just activate it is not going to navigate there as such if it is not already there but your code will be able to refer to it without even you know like going and selecting that okay so you can also activate this or select whatever uh is depending on the scenario it's important that you need to be specific because if there are two files and let's say that uh someone has messed around with the file and they put another tab here.
Something like review review or something like nodes or something so your code is going to try to pick the data from the first time once it opens up it's gonna cause issues so you need to watch out for that okay so here we are activating that particular sheet and then um i'm just gonna get rid of all this selection which is not required from this tab we want to copy this data right from from a1 i think we do not want to copy the header so i'm just going to get this away and then do this.
And we'll change this okay and just in this a bit now after this we're going to come back to um this workbook again remember this workbook is the book that where you're writing your code okay this workbook.activate so we're coming back to workbook so open the workbook go into this workbook activate the sheet copy the data come back to this workbook and then here we want to select this uh.
Tab and then we want to paste the data obviously for now we'll just put a range a1 but we do not want to paste in a1 os we want to find the last row with the data and then we have to paste it there okay and then paste it here clear the clipboard and that's it okay so here instead of active workbook we'll see wb which is this you know external workflow we can i think just rename this as external book the variable will make more easier to.
Relate to okay so there's one tip there so now i just change the variable the thing that i always try to make sure is to compile my code so alt d okay and then i enter hit enter so it's going to basically run this so it will check if there is any issues within the code and then it will tell you okay variable not found that was just because we have renamed this so now i'm just going to rename this as well compile it again so this is just a practice you'll always observe in my other tutorials as well that.
I normally just do this okay so that's one way to compile it so far there is no error handling here okay we are just saying that open this file go to this workbook copy the data bring in the data paste the data close to workbook and then we'll say set this with this clear the memory okay okay so now file name uh instead of hard coding this will say that file name will come from home tab okay and then it's gonna come from here.
Filename so we can say um sheets and then sheets home and then dot range dot value okay so if we use the immediate window again you already know about this control g you can validate the information like this so that you know this syntax is working so this is another way of debugging your code or uh checking your code okay so now.
Instead of this full bot i'll just replace this with the file name okay and then compile again so you observe that once i compile this this uh space was gone okay or once you you know like go to the next line it goes away so this is more better uh from the recording so you don't have to type everything you can record it and you can easily change this so let's try to run this once uh now we already have the file set up we want to paste in a2 okay now it comes without the header so.
Let's try to run it so f 8 to go line by line okay so now it has opened the workbook and let's say that there is another sheet here and then i say select instead of activate it's going to go there yeah and then it's going to copy that drains all the way from a2 to you know like 2000 something and then it's going to activate this workbook is where the code is okay and then.
Activating this particular sheet okay now let's say that you are here and then we you want to make sure that your code is pasting there you can just drag this line up here okay and then rerun that part okay like and then now if i do this it's gonna activate this so that way i am sure that okay this line works and then now it's gonna paste in e2 this works perfect and then click clear the clipboard close the workbook now it asks me do you want to save it no i do not want to save it and then this so here if you don't want to get a.
Prompt you can just say false okay so the next time you run it it did not ask me okay or if you want to quickly run again and stop here you can either put a breakpoint by clicking here or uh i can use f9 right now because of the recording conflict keys so you can press f9 it will put a breakpoint here or you can go to debug and then uh f9 okay total breakpoint then if you run this now okay so it stop here or the other way to do that will be uh click again to remove it if you want.
To run till here without putting a breakpoint here's a shortcut for that as well uh control f8 okay run to cursor so wherever your cursor is in the line not in a blank line but in the actual code line so if i hit control and f8 okay so it ran till here and then it stopped so you can also uh re quickly rerun and test your code in that fashion okay so i'm gonna mix things up here okay i'm not gonna like explain all about debugging or error handling in one shot so this way you know like you know how to um.
Use this practically okay so that's pretty much now that you know how to import the data again we already wrote um you know like for example how to set up the last row for example from after activating this sheet we can say um dim now i'm gonna use this sign okay this represents long some people will use that so i'm just gonna use that so that you can know it now this is uh external workbook and then i'll just.
Say this workbook and then lr lr represents the last row okay i do in this fashion so you can say like this also okay so an external workbook after activating this sheet we will find the last row so for this since we are already in this tab you do not have to specify the sheet name again because it's already there okay so i'll say um cells and then here you can see rows.com that will be entire row and then.
Let's say uh the the column to be used to determine uh the last row maybe first column and then from here we'll do um excel up dot row okay just give me one second dot and from n so if i for example just test it out i'll just do question mark and run this line so it's gonna bring in here so whatever.
The active uh sheet is is gonna find the last row now if i run this again we'll expect it to be this particular row number okay so 2504 so now instead of um this 2501 i'll just get rid of this and then i'm gonna concatenate to construct the dynamic range okay so whatever is the last row is gonna copy the data from there now and then coming here i'll just copy the same line.
After coming to this workbook now to remote it i think because both the source and the here at the this workload the file of the sheet name is data i'll just change this to let's say master data something like that um and then here as well let me see master data and this workbook lr is gonna be again based on a column and now we don't want to paste in the last row but if we want to paste in the.
Next row so either you can just put plus one or you can just say dot offset you can offset away from this particular range one row down same column dot draw okay and that way also it's okay now instead of a2 we want to face in the last row so that the prior data is not you know wiped out so that will be idle way to write importing the data code now what we can do we can hook this code.
By right click assign macro you can either select or just paste the name of the code click on ok ok so once this is done we can use the message box this will be a good time to just use a message box so um ctrl j message box and then data imported something like that and here once you put a comma you'll see various type of message box you can just say information vb information or if there.
Is error maybe you can use critical something like that information and then this will be maybe name of your app so let's declare a constant variable uh a string equal to listen for app something like that okay so if i run this now.
Okay they imported the data everything's perfectly fine now the issues will start coming up when someone change the name of the tab or uh the order you know like the file name is not there all these issues will start coming up that's where you you know like specify uh error handling so most common way of error handling is uh you assign a label for example where do you want the error to happen when the error happens what do you want to do with it so the most common way is.
You set a point to like this a label for example let's say import data error handler something like that oops and then you put a semicolon like this okay and then if error happens let's say we want to point to this location and we don't want to run till there if you know everything works runs okay here we just want to stop here exit stop means you know like it will just exit the this process okay um.
So let's see that on error on error go to this location if any error happens just exit here okay so let's say message box um we can say if error happen we will just maybe trap here error.descript and then it will basically just notify the user this is the editor uh description and then error code maybe error number and.
