Make in our application here I wanted to update on our phones as well excel in the self doesn't work very well on our phone because it doesn't allow macros but we've got some amazing applications they can help us and I'm gonna teach you how to create your own brand new application right on your phone and sync it with excel today so I can't wait to get started on that also this is could be a continuing series so I'm really gonna ask for your feedback your ideas your comments on this training there's.
So much more we can do but I don't know what direction you want to go in let me know in the comments I can add on to this inventory manager I can create additional applications and lots of other things so there's really so many directions I can go in but I'm gonna use your advice your comments your feedback to know which way I can go in alright before we get started let's take care of some business if of course you have not yet subscribed please do so I create these brand new training videos each and every Tuesday just for you so I want you.
To be notified right away when I create those to do that go ahead and subscribe to our Channel and make sure you click the notifications belle-isle got some amazing really incredible products and courses in fact the advanced Excel dashboard master class is extremely popular so many freelancers are taking this course and getting great jobs freelance jobs from it or creating their own products so if you have not yet enrolled I'm currently offering $50 off coop and I don't know how long.
That's going to be available so make sure you join us over at the Excel for freelancers dashboard masterclass if you do also want to become an expert on Excel there is no better way to do that than with our ultimate excel resource guide I've got over 1,000 resources including 100 PDF downloads 100 Excel applications utilities 100 Excel Facebook groups and tons and tons more also you can download any of these workbooks for free using the links in.
Sheet it's an amazing it is free to try in fact up to 10 users let's take a look at app sheet this is called app sheets called the intelligent no code platform it's a great platform it allows you to create applications on your phone I have personally barely touched the service of this application there's a lot I don't know I'm just learning this but we're gonna start out with a sample and get started and I'll show you the basics on that but I urge you to check it out it's a pretty incredible application so if.
You look at the pricing the pricing is per user but up to 10 users for absolutely free so for our purposes here we're gonna we can use it for free so the first thing you want to do is get an account and you'll want to log in I've already logged into this account but what you'll do is you'll want to log in and once you do you want to connect your Dropbox account let's take a look at that I'm gonna sign out here and then I'm gonna sign back in and I'm gonna use my Dropbox account so when you're home here so when you click on the app and you want to start for free or login start free make.
Sure you click on the Dropbox right because we're gonna be syncing with Dropbox now you can use any of these they're all gonna work you can use as long as you have the shared folder we need to sync with the shared folder so I think Dropbox onedrive would work Google sheets would work box would work sales probably work but we do use Dropbox for our purse it's one of the easiest so we're gonna sign on with Dropbox that I already have it's gonna connect to my account I've already logged in so since I've already logged in we're gonna go start with a sample app that's easiest.
And I'm gonna call it sample inventory because I want to create an inventory and there's so many different kinds of applications you should really check this out we're gonna start with inventory manager and what we'll do is we'll scroll down here and we'll try this inventory management application and as soon as we do that it's gonna go and set it up and once it's done setting up we're gonna see that we automatically have a brand new app just like on our phone and here's the sample hook here.
Right here this is what it would look like on your phone to add a new one you would click the plus and you'll be able to add brand new ones I inventories we see information about inventory there's lots of views so here we have our app a lotion we have some sample products here now what I want to do is I want to get that into Excel to get it into Excel we're gonna use our Dropbox and that's what we've signed up with now since I've already connected my Dropbox account a folder has automatically been created so.
Posts Related:
If we look into my file explorer here we see that I have Dropbox app sheet app
Sheets automatically been created as a folder and then I have a data and the inside this data folder I have what's called sample inventory app that is the one that's just been created inside that folder you're gonna see all the pictures of the products that I have and you can also see an inventory list file if we open this excel file up and that's gonna open in 2016 I've got multiple versions.We'll see it's just a very simple table that consists of name category image of time check and basically some data in a very normal-looking table the data starts on Row two and ends on row 21 so that's our inventory list and it includes an image and this is the name of the image or picture file that's gonna be important of course that also matches the name here for each image it's matched we also have some information under content and this has helped for a report this is gonna be a body of a report I haven't gotten into.
Detail much detail but it's super powerful there's so much we can do with that so we've got this is a low stock I think you can use this for an email and it automatically sends out have not even got into this but it can be really really powerful I'm super impressed by this application okay so that's what that is so now you see into a Dropbox so now what I want to do is I want to connect my application with this list and these pictures so that we can sync it so back into our application here's the.
Application I build I'm gonna show you every step everything I did but real quickly what I want to do is I want to connect the inventory list to our application I want to browse for it and I want to know exactly where this list is of course it's right in here we're gonna browse for it so I'm gonna copy that that's where I want to browse for it because I want that inventory list in our application I need to know where it's located so when we click browse we're just going to actually it's.
Already here all I'm gonna do is connect that inventory list here connected and I also do want to do one more step I want to know where the entire application folder is and that's also pretty simple that is right here into sample inventory app we want to browse for that because I want to know where all those pictures are stored that's all we need to do and if you'll notice here there's no there's a drop down list there's nothing available here there's inventory data there's nothing here but I'm gonna I've.
Written a macro already and all we need to do is click sync application data it's gonna sync automatically and once we've synced all the app all we do is get out of the new screen and we see that every picture is now in here all the data is now in here it's that simple let me show you exactly how we did that and of course if we want to update the item it works both ways so let's say we want to add a new item just put in a brand new item name like test new item and we can give.
It a category as like just say grocery and 10 available we'll leave some of the other information let's say 12 o'clock and barcode is fine and we'll set a unit price of $5 value of 10 reorder point 10 okay so that's enough and won't go ahead and add a picture just clicking on the Browse we'll be able to add a picture this baby crying should be fine and then all we needs to do is save the item and now the items been saved and updated we just have to go back to our application.
Here refresh the screen and we're gonna see that that brand new product has been added on our mobile phones so we'll let it refresh here scroll down and we will see that our baby here test new item is right here inside our mobile phone application so it's gonna be great let's see we see everything our groceries category here we are available barcode everything is here in our mobile phone and I can't wait let me show you exactly how we did that all right it's actually.
Relatively simple it's not difficult it's just to database all we're going to be doing is taking our data in here in our current local file and syncing it with our database right here in inventory once this data is updated in this inventory list this is the item we just added test new it's also going to be synced so for example if I'm gonna change that right here to let's say 15 and let's say I changed the type to let's say baby milk changes here inside.
This we're gonna save it and close it automatically changes here are going to be reflected inside our app so
Understand that as soon as we make a change and then of course refresh it I believe there's probably a better way to refresh that here those changes are going to take effect in our applications so the idea is to get the information in to that particular app so we can see here now the reorder points been 15 and we see that the category is now baby milk so the idea is to make sure that we update that local file soon as that's updated.It is automatically updated in our application on your phone as soon as you download this and put it on installed on your phone it's gonna be updated there as well so it's a sync both directions both from the app to excel from itself to the app that's what we're going to show you so the idea is we always want to make sure that everything is synced this table inventory list is always synced with this table right here so that's the idea so when we add something when I add a new item I'm gonna take the.
Information from here and put it and push it into the inventory list when in information has been inside the new information is added to the inventory list it's got to be pushed back in here let's do the opposite on our on our application here we've got our application and let's add a new item from here so we can see how it works both ways sticking with our baby let's say we have baby shirt and we're gonna put again and we can do let's say baby milk will use.
The same categories although it's not baby milk let's choose an image for that we'll take a look at the images here and I've got something you know let's just put that anything will be a little bit more okay so we've given an image I think there's some required here's our image and we do have some required fields in here so we're gonna tab through it add some information here add a reorder point and the time of day just put some numbers in here because these are all required to date it's not required type chick and we're gonna save.
That app now as soon as we save it now that we had let's confirm that it is here we'll take a look scroll up to see where it is here it is right here baby shirt here so we've got it in our app but I want to put it back in Excel now how do we do that inventory control screen what I'm gonna do is sync updater we're gonna run a macro I'm gonna go through that with you real quick in a moment so now that we have it let's take a look at here our baby shirt here it's here automatically in Excel pretty cool.
Right sync both direct let me show you how we did that all right back in to this application we're gonna get into the developers and go for all the macros that take this particular application and give it all the power and control you've seen something similar to this but we've never synced it with a mobile application so I'm gonna do that for you right now into the developers tab let's focus on a few different macros first the first thing you'll notice we had a few browse for file Mac so we're gonna focus on this.
Module here there's a relatively simple we've have three browse I'm gonna browse for the inventory list this is a file I'm looking for an excel file when I click there I'm looking for a specific excel file you see excel file I want to look for that that is what we're looking on so when we look into this sample let's pull this up here under Dropbox remember app sheet data and then sample inventory we're looking for this excel files only that's the one I want to browse to so we're gonna put a filter of excel file so let's take a look at that one in it if we right click this button.
Click sign macro we will see that the macro that's been assigned is called browse for in Tori list browse for inventory so that's the macro that we're gonna be focused on back inside the module we'll see that we have a macro in our module called browse file max here we have a macro called browse inventory list we're gonna dimension inventory files the file dialog file dialog is our that little pop-up that's gonna ask us whether we want to browse for a specific file or a specific folder in this case I.
Want to browse for a specific file so we're gonna set the inventory file to application file dialog and then this is gonna be a file picker file picker in another way and sometimes we you only want to folder right if we only want to browse for folder that would be something like folder picker but this case it's file picker so with sheet 1 we're gonna focus just on sheet 1 for this particular macro and with inventory with the inventory file that's the variable that I've assigned is the file dog I'm gonna set a few things the.
Title we're gonna set it to select inventory list and then filters I only want one type of filter and that's xlsx so that's the file it's gonna be an excel file and that's the only one that I want if the user has not selected an item we need to tell it to skip this step so if they've not selected which would result in the negative one I'm gonna go to no selection we're just gonna skip this step and go right here assuming that of course they have selected it I want to put that entire selection the entire path of what.
They've selected into k3 that's gonna put us the entire path that is this right here so if we look in the formula bar here we'll see that the entire path of that file is right there that's what I want now when it comes to the folder I only want the location of the folder that's our application so when we right click this browsed and we go assigned Macker we'll see a different macros been assigned to this one in this case it's called browse app location browser application let's go through that macro although it's very similar if you follow.
My videos this is very similar we're gonna we're going again this time it's gonna be app folder as the file dialog we're gonna set the app folder as application file dialog MSL felt like folder picker this one's a folder all I want to know is the folder also like any specific file there's no filters on this because we're just selecting a folder so we're gonna give it a title called select the app folder just like the app folder again if the user has not made a selection we're gonna skip this step but assuming that they have selected an.
Appropriate folder I want to put the entire file path of that folder in kay for now keep that in mind k4 is where it's going to go so that's all that we have k4 if we look back into it k4 is right here and that entire path of the folder is gonna be right here that's gonna tell Excel where all those pictures are stored because when we add a picture or when we pull a picture I need to know where it's coming from so we need to know where bread is whereas.
The picture of bread it's gonna come from that folder coming from this folder right here this is the folder we need to know to tell Excel where to look for this picture of the bed we have the name of it because the name is located inside the database under name we know the name but I need to know the folder so adding that it's gonna tell us exactly where and of course any pictures that we add we need to know what folder to add them into now notice that the when we added it through the app and added it to a different folder that's okay because the name of it consisted in.
The inventory the name is going to include the folder how is that possible there's two different folders let me show you how so also when we click inventory data we see now the test new item the one that we've added for the baby it included and folder called inventory images it included the folder so the full file path includes the embedded folder that's in so that'll work also as long as we have the folder that it's in part of the name it's fine so that's gonna work great for us okay.
So back into that so we know how we browse for the inventory list and we browse for the application location we've covered that one more browser I want to know we do browse for the picture when we set the browse for the picture I need to know if we're gonna change a picture or do something else we need to update that accordingly so for example if we take our inventory and we update the photo for this and I want to update it we are going to browse for that and then we'll change it up and we can select on a different image add the.
Picture files here so we have different we want to give it a different type of icon or image we can so that's going to change and make sure that we of course update the item and that's going to update it both in our local and the remote database so now when we go back into our we see we have not refreshed yet it's bread but we haven't refreshed yet once we refresh it we're going to see the updated picture for that bread as well you can see it's gonna change that picture to bread so the update works both ways so you see now bread has.
A 100 so it works both ways so I'm gonna show that to you because it syncs with both of those tables okay so how do we get this macro if we take a look at this macro cancel out of that right click click assign macro and we see the macros called browse item picture that's the macro we're gonna show you right now right here browse item pick first thing I want to do is dimension the pic file is a file dialog file dialog we're focused on that because we're focused on a file and then the application I need.
To know where to store those pictures we're gonna put those in the app location and that's going to be a string cuz we're gonna at the full file path of that we also want to make sure that k4 is not empty k4 is where our folder is located that is the folder where all of our applications we can't make sure that's not empty because we need to know where to put the picture so we need to make sure that that is the full file path of where we're going to put the picture exists of k4 is empty that's gonna be a problem so we gotta have it if K 4 is empty of course we're gonna.
Give them message boxing please set an application location before adding an item picture then what I'm gonna do is I'm gonna instruct the user to run this Mac or called browse application location that's right up here just gonna run the macro that lets them know to select a specific folder or the application is going then if we're gonna give them one more chance if K 4 is still empty then exit the sub we've given them a chance we're gonna exit out if it's still empty assuming it's not.
Empty we can continue on with the particular macro we can set the application to whatever is in k4 that's gonna sign the application location to a variable that variable is going to come in handy now we can set the picture file now we're ready to set the picture file to an application file da like file picture we're looking for a particular file a picture file and we can set a title when we say with the picture file we'll set a title select an item picture.
And then the filters add all picture files that's the name of it right so when we click that macro it's gonna have a name if we click that you'll see there's a name right here select an item picture see that title right here that name is where we signed it in VBA we if we changed it it's gonna change it up here also if you see these specific filters all picture files and then we've given all picture files that also comes in the macro right here all pics your files and and all those pictures so we can see how the code that we write transfers.
Automatically into that pop-up and then the next line of code again if the user has not made any selection we're gonna skip everything and go all the way down to here so we don't want anything to happen if they haven't made an installation next up I want to put that file name into K cell but I only want the file name I don't want the full file path and I want to put it right here I want to put that file name so if we have a full file path but we only want the file name how do we extract that well we can use the directory command in VBA and.
That's just what I've done so the selected item this is the full file path here right but I don't want the full file path I only want the file name how do we get that we use the directory the command the directory command will extract just the file name from a full file path and so I can take that file name just the file name and put it into k6 under sheet 1 why did i rek you if I have sheet 1 up here why did I notate sheet 1 down here because this is with.
Picture file so we're inside another width so I need to denote that and make sure that we've specified sheet 1 because here this with focuses on the picture file so we need to specify sheet 1 because we're inside another with statement next thing I want to do is I'm gonna copy the picture I don't want to I want to copy the picture from its original location which is selected items this is the full file path of the original location I want to place that picture copy that into the application.
File or in I want to take it from remember if we look if we remember correctly that original location was right here in two images let's view that so we can see the thumbnails on here we'll see that that image location was right here the original location was in my images but I wanted to paste it right inside here our application location right here I want to paste it so I want to copy that that icon and put it into our location how do we copy it from one location to another well we do that with just one line of code and that line of.
Code is called file copy final copy and all all that is is simply the ability that is the original location comma the new location that's how you copy a file so what is the new location well of course it's our application location plus we are going to add a slash to that Mac's last to that and the name of the file the name of the file while only one again I only want the name so I'm going to use directory once again to extract only the.
Name so when we combine all three of those we get a full file path for the destination of where we're going to copy it so again the file copy is the original location then the comma the destination that's how that's a simple line how you copy one file from one location into another location that's how and then all we're gonna do is run a macro called display thumb and go over that for a minute that is going to give us a display our thumbnail right here so because I want to displayed right here.
At a specific size and I want it displayed right here so that's how we do that once we browse for it we display it so if we were to do it again browse for it change it to a different icon I want that macro displayed when we click update it's gonna update both the local and the remote database automatically okay great so we've got that down we know how we're gonna be browsed for these photos let's go into some of the macros that run this application how do we update how do we add new well have created some buttons as I did I've done this so many times.