How To Import An Excel Spreadsheet With VBA In Access 2013 🎓

hello everyone and welcome to how to in Access 2013 my name is Steve Bishop and in this video I'm going to show you how to import an Excel spreadsheet using VBA code so before I begin showing you the code let me go ahead and show you where I got my sample excel data from and the first thing I did was I just did a search for Excel sample data and from there I found this tableau community post here where there is a sample super store sales excel spreadsheet and there's plenty of data in this Excel spreadsheet now you can use whatever Excel spreadsheet that you want just so long as it is an actual XLS or xlsx file so it can be a newer or an older version of Excel it cannot however be a CSV file or a tab delimited file it needs to be a full Excel spreadsheet so this sample superstore sales excel spreadsheet has plenty of data in it and I'm just going to show this you it's got eight thousand four hundred rows here so there's plenty of data and there's even some column headers here so row ID and sales and profit and Provence and all sorts of good stuff that you can certainly tinker around with and practice with so I'm going to first start off by focusing on the method that I'm going to create that's going to import the Excel spreadsheet to a table in our Access database so I'm going to go to database tools and Visual Basic and I'm going to right click on this import Excel and in case you're wondering where that came from that's the name of my Access database that I created so I'm just going to right click on this and I'm going to go to insert a new module in my project so in my module I'm going to go and create a public subroutine and I'm going to go ahead and call this import Excel spreadsheet and in this import Excel spreadsheet I'm going to need a couple of parameters that get passed into it these are a couple of unknowns at this point such as the file name which is the you know spreadsheet that we're going to import and then the table name what is the name of the table that we want to save the data to so we're going to do file name first as string so this is going to be string that's going to have the file name and then what is the table name going to be so table name also as a string and let's go ahead and end our subroutine there so the actual command that imports an excel spreadsheet to access database is found already implemented in Access and it comes from the do command object where there is a method called transfer spreadsheet and the transfer spreadsheet method accepts several different parameters the first one here which is kind of obscured by this hint here we can see the intellisense we can't really see but if we type a then we can actually see the intellisense and the first option in this enumerable is AC export so AC export would tell this transfer spreadsheet method that I want to take the data from a table and export it to an excel spreadsheet but that's not what we want to do we want to do the AC import which takes the data from an Excel spreadsheet and imports it as a table to access now this third option of AC link would actually leave the Excel spreadsheet alone and just create a link in your Access database to that excel spreadsheet so it sort of like a linked table would be where it creates a linked table name in your access that actually is just a pointer to the excel spreadsheet data and it leaves that Excel spreadsheet alone now that may seem like a really good idea for a lot of scenarios but I'm going to kind of tell you that it's really not as good as it may first seem that's because the Excel spreadsheet might get deleted for example and now your Access database doesn't have the data anymore or perhaps the Excel spreadsheet gets moved or maybe you move your Access database to another computer or to a server and once you make that move that Excel spreadsheet may not exist or it may not be in the same folder location so it really becomes very tricky if you try to link your Excel spreadsheets to an Access database but it is possible using this transfer spreadsheet method I'm going to go ahead and use the AC import and we're going to import the data from the Excel spreadsheet and we need to specify what type of Excel spreadsheet it is there are several different versions that are out there and we're going to use the latest one which is AC spreadsheet type excel 12 if you use this Excel 3 then you may run into problems importing the newer file types the xlsx so but if you use the newer version the Excel 12 then it won't have any trouble getting either type of files it works with both XLS and xlsx so we're just going to use that one now the table name parameter is something that we're passing into the import Excel spreadsheet method already so I can just pass along table name same thing goes for the file name we are passing it as a parameter to this import Excel spreadsheet method so we'll just pass it along once again to the transfer spreadsheet method now after the file name it's asking us has field names and this is a true or false boolean value that it's looking for has field names is referring to the first row in our spreadsheet does it contain the name or the headers of the column so for example column a has a header of row ID in this first row same thing for column F column F's first value is sales and so this first row is not data it doesn't contain any actual data it contains the column headers or the column names and as such this import utility needs to know is the first column or the first row of each column the name of the column or is it actual data and since this spreadsheet that I'm importing here does have a first row that is our column names I need to say that yes this is true now you may want to make this import Excel spreadsheet method accept this as another parameter so something like has has column headers and as a bullion value and then you could just simply pass that along but I just know that typically when I work with Excel spreadsheets I always like to make sure that those column headers are there anyway that way there's no confusion about what kind of data each column is so I'm just going to leave this true for now and the next parameter that comes after that is range now the range parameter is talking about the range of our Excel spreadsheet so is this a you know something like a 1 through m 32 so we would pass that along here as a string of something like a 1 through m 54 or something like that now you could pass also along the page name and that's really what this is for so that you can specify a particular page on an Excel spreadsheet and then get specific rows and columns on that page but I'm not really going to get into that I'm going to let you try to research that on your own about how to fill in that range I'm really not that concerned about it when it comes to this most particularly because once I do an import of everything I can use access to go through that table and pick out the columns and pieces of data that I need so I'm just going to go ahead and leave this blank this range parameter blank because I'm just going to grab everything from that main page the use o a is actually deprecated I'm not really even sure why this still exists on this transfer spreadsheet method there's no use for it more so you can go ahead and leave that blank as well and since the last two parameters of this transfer spreadsheet method call are blank I don't even need those extra commas at the end I could just leave off here on the true so our logic is in place to do the actual transfer of our Excel spreadsheet to a table now I need to create our user interface along with the code behind that user interface that's going to call this import Excel spreadsheet method now you could have your own user face already built and just use this import Excel spreadsheet maybe you watch this video just so that you could understand this transfer spreadsheet method but I'm going to go ahead and create my own user interface for people to follow along with in case you don't already have one so I'm going to go to create tab in form design and I'm going to just resize the form here to make it nice nice size for what we're going to do and I'm going to go ahead and add a new text box and this text box is going to contain the file name of the Excel spreadsheet we're going to import so we'll do file name is the label and let's just go to move this around get a little bit more room on our form and let's resize this text box so that we can view the full name of our file and along with this text box that contains the file name I also like to have a browse button so I'm going to add a button here and this browse button is going to allow the user to go out to their hard drive and select a particular file that they want to import so I'm going to go to my command button and I'm going to rename this from command to to BTN browse and I'll also go ahead and change the label here to browse and while I'm at it I should probably also go and change this text box name from text 0 to txt file name so we have a text box that's going to contain the file name we have a browse button that's going to go and allow us to go search for file on our hard drive and we should probably also then go ahead and add another button that's going to call the import commands that we wrote here the import Excel spreadsheet method of this subroutine so I'm going to make this button I'm going to say import spreadsheet and let's go ahead and resize the button by double clicking on the bottom right hand corner here and let's rename this from command 3 to button import spread sheet so now we've got the basics of our form it's a very simple form it's not too complicated again you could have your own form that already has you know maybe a list of Excel spreadsheets already in the database so maybe you have a table full of Excel spreadsheet names whatever the case is this is just again a small little example of how you can build a user interface to then call this import Excel spreadsheet method so on my browse button I'm going to go ahead and go to the event tab and go to the on click events ellipsis here do the code builder and I want to open up a new file dialog window so I'm going to create a dim and I'm just going to call this object diag as a new office and you can see office does not come up with any intellisense and that's because the office class library that I'm looking for is not currently referenced in a blank database so I need to go up to tools references and I'm going to scroll down here to Microsoft Office object library and this is going to be version 15 point O for me but if you're working from a different version of Microsoft Office or Microsoft Access then this is going to have a different number here but for me since I'm working out of 2013 my office version is actually 15 point O so I'm going to go and click OK and now you'll see that if I try to retype office we get the intellisense there we're going to do office dot file dialogue that's the class that I want to instantiate and speaking of instantiation I need to do set so I'm setting my diag object equal to and the file dialogue object actually exists in application dot file dialog and this is actually a method it's kind of goofy it shows it up as a property here I'm not really sure why it shows it up as a property it's actually a method that accepts a dialogue type of MSO so here we have an enumerable here that tells us the different types of file dialogs we can open and we're going to do file dialogue a file picker that's just the first one there and that will create a new file dialog and set it to our diag object here now on our diag object we need this a few properties to make the user experience a little bit better so to do this I'm going to do diag and I'm going to say the allow multi-select I don't want them to be able to do I want the user to be able to only pick one file at a time from this file dialog window so I'm going to set this equal to false I'm also going to change the title of the window to something like say please select an Excel spreadsheet and I'm also going to work with the filters of my file picker so I'm going to go to diag filters oops I spelled that wrong diag filters and the filters is a property of it's a collection property so that means that we can add and clear and delete different items so I'm actually going to go and clear what's in there just in case there's anything in there right now and then I'm going to do diag filters again and I'm going to go ahead and add a new filter and in this add method I can pass along a description as string so that would be Excel spreadsheets and then I need to also pass on a extension so the extensions as a string and that would be things like asterisk dot XLS comma asterisk dot xlsx so those are the two different types of file extensions that we want this user to be able to select from the file picker now I'm also going to do diag dot show and diag show actually returns a value a long value and when it does that it's basically telling you how many different items were selected by the user and if we wrap this in an if statement that's going to check to see was there anything that was selected if not then we wouldn't want to perform the code that's going to put the file name into the text box if there's been no file selected so wrapping this in a file or wrapping this dot show in an if statement checks to see if the user has selected any files at all and if they have then we'll do if diag show then I'll put my end if here so if they did select something we're actually going to need to access the diag dot selected items collection and this is a collection and as such since it's a collection we need to iterate through it using the for each loop so even though there's only going to be one item selected we still want to loop through the selected items list and whenever we're doing a for each loop we need to do we need to create some sort of object that each new iteration is going to fill with that object type so we're going to do for each item in diag dot selected items and I haven't defined item yet so it would actually error out if I left it this way so I need to go ahead and dim item as I'm just going to call this a variant which means variant of course as most of you guys know means I have no idea what type of object this is it's just going to figure it out when the code is is actually running during runtime so now for my for each loop I'm just going to delete this because I don't need it anymore I'm going to do next at the end of my for each loop and now we're going to iterate through each selected item on our file picker I can go ahead and now say on this form the me txt file name I'm just going to set it to item and because item is actually going to be a string of the file name that's been selected from the selected items list so I'm just filling in the txt file name with the item that has been selected so let's go ahead and save all of this now and let's name our form import Excel spreadsheets or spreadsheet and the module name let's do Excel import ok so now that everything is saved let's go ahead and debug it and compile it just to make sure we didn't fat-finger anything and let's just make sure this browse button works now so we'll do browse and we can see please select an Excel spreadsheet and our only options for filters here are Excel spreadsheets so we don't have the option to do all or anything like that that just makes the user experience a little bit nicer and they're only going to see the Excel spreadsheets and folders so I'm just going to go ahead and select my sample super store sales excel spreadsheet here and click OK and we can see that fills in our text box with the full path and file name to our Excel spreadsheet so our browse button works just fine now that we've got that down we can go ahead and focus on the import spreadsheet button so let's go ahead and go back into the design view I'm going to click on the import spreadsheet button and go to the event tab and select on click event and go to the ellipsis code builder and here we're going to add our logic that's going to actually call this method that we created at the beginning of the video to import the Excel spreadsheet now before we call that we should probably check few things for example what if the user doesn't put anything here in the file name or what if the user picks a file name and then accidentally mist types it so maybe you know puts an extra character in there or forgets the file name itself and just puts a path name we should probably check all of that before we actually pass along the values to the import Excel spreadsheet method so to do that let's go ahead and go to the import spreadsheet method again here or the the button on click event and from here we're going to use the file system object to go out and verify that the file that the user put into that textbox exists to do that I'm going to dim a new object and I'm going to call this FSO as file system and you can see that we're missing file system object so that's because just like what we had up here when we tried to access the office class library the file system object class is not currently available to us so we need to go up to tools references and here we're going to look for Microsoft scripting runtime and this is where the file system object class lives so I'm going to go and click OK and you'll see now if I do as file actually I'm going to do this as a new file system object we can see that we got the intellisense that we needed to go ahead and create that file system object now using this file system object I can go out and check to see if the file exists so we'll say if FSO file exists we're just going to pass along to the file exists method the text that's in the text box will do me txt file name and then and if so if the file does exist then we should be pretty good because obviously if we already know what the file name the file exists then we must have a valid file name there and we can pass that along then to our import Excel spreadsheet method that we had here so let's go ahead and call that will do I I'm just going to use the module name here to make things easier for the intellisense under Excel import dot and now you can see there's that method that we created import Excel spreadsheet and it accepts a file name as string so that would be me dot txt file name and now it also needs that table name remember we created that parameter that should be taking the file the table name as well and as of right now we don't really have any place that we're getting the table name and I could certainly pass this along as a string now so I could say like imported file would be the name of my table if I wanted to or you could in my case what I'm going to do is I'm actually going to use the name of the file itself and I'm just going to use the file system object once again to get this I'm going to go get file name and that way I'm passing in the actual file name and naming my table the name of the file that's not including the full path that's just the actual file name including the extension so that's this sample - superstore sales Excel XLS that's going to be the actual name of my table in my Access database so we'll do get file name from that me txt file name and that will return just the file name and that's it we've now successfully called the import Excel spreadsheet method but there's actually something else that's going to come up as a problem here and I'm going to show you here if I save this and debug it and compile it things may outwardly seem just fine so if we go view and we already have my excel spreadsheet in this text box so I could do import spreadsheet and here we go we're importing and our importing and we're importing there we go there is our sample there or there's our table of our Excel spreadsheet and I can double click on this and I can open it up and see that there are eight thousand three hundred and ninety nine rows of data and of course that again just case some of you guys are wondering there even though this shows 8400 rows here remember the first row is actually our column names so that's how we got 8,000 399 values now everything seems hunky-dory it looks like everything worked just fine right but what happens if I put an extra character in here so I put an extra space and now I do import well what happens nothing okay well maybe that's not the user experience you want maybe you know they think that they've reemployed it so if I delete this and import spreadsheet I get nothing right that's not really a great user experience so I'm going to go back into the design of this button and I'm actually going to go to check to make sure if that file doesn't exist that the user gets some sort of message back so I'm going to do an else statement on this if and I would say if the file doesn't exist then let's pop up a message so message box file not found okay simple enough so we'll do save and view this and now again I have that extra space I'll just add another one there just be sure import spreadsheet and oh file not found so that at least tells the user why there's no table that's been imported but what about if I leave this blank and then I hit import spreadsheet invalid use of null well that's really not good where'd that come from well it's because the file exists method expects that there's some sort of value that's going to be passed in here so what I need to do is I could do really one of two things I could say wrap this text box in an NZ function which will turn a null into some other character and I'm just going to say turn a null into a blank string okay so if we do that save that debug compile okay and we do import spreadsheet file not found okay great so files not found that's okay it still doesn't really tell the user exactly what the problem is they might be able to deduce it but you know what I'd like to be even a bit more explicit about this so what I'm going to do is I'm going to change this I'm going to say if NZ me dot txt file name returns a blank string whoops then we're going to pop up a message box will do a message box please select a file and then we need to exit out of the subroutine so that it doesn't continue on to these other functions here so we'll do please select the file exit the subroutine and we should be good to go so let's go ahead and save that debug this and compile and now if the user leaves it blank and they click on import spreadsheet they get please select a file so we get some better validation here now there's another scenario that you need to account for in this window and that is if I click on browse and let's just say I'm a real thrifty user and I know you know what I don't want an Excel spreadsheet I want to do star Todd star and this allows me to go look for oh look I can see other files let's hit this let's hit this access query best practices text file ok and now let's try to import it Oh external table is not any expected format so we need to verify that this is in fact a dot XLS file and that it's not some other format now I'm going to take kind of the cheaters way of doing this you could go and look at the file extension to see that whether or not this is a dot XLS or dot xlsx file but I'm going to do I'm going to use error handling to manage this instead so I'm going to go to my Excel import method here or my import Excel excuse me Excel import module or I have my import Excel spreadsheet method and what I'm going to do is I'm just going to add some error handling here so I'm gonna do on error go to we'll call it bad format and down at the bottom here I'm going to go ahead and call this bad format put a colon there and if if this do command performance six is successful we don't want it to move onto this bad format so I'm going to put down after this exit sub that way after it runs this command successfully it'll just exit the subroutine but if it doesn't execute successfully then this go to bad format will drop it down to this error handling note here so under bad format I'm just going to go ahead and do message box the file you tried to import was not an Excel spreadsheet okay so let's go and save that and now if I try to import the text file the file you tried to import was not an Excel spreadsheet so there you go and you could of course check to make sure that the error number is correct to match up to that that error handler otherwise maybe kick out a different error message but that's just kind of a nice little cheaters way I think of making sure that all of your bases are covered here and that somebody doesn't try to import the wrong type of file so there you have it there is from top to bottom from user interface to the actual method to the axis commands that do the actual import there's everything that you're looking for on how to import an Excel spreadsheet to an access table and then from there of course you can write your queries against this new table you can you know maybe copy it to another location or you can save it as your all-time favorite most you know your most favorite data that you've ever had and it's so special to you whatever you want to do to this table you are welcome to do they hope you guys enjoyed this video if you did please don't forget to Like favorite and subscribe and tell all your friends and co-workers that how wonderful and stupendous this video channel is and they should all subscribe as well that would be wonderful so thank you so much and I hope to see you guys in the next video

Channel: Programming Made EZ

Views: 97,115

Rating: 4.9386711 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, programming language, Excel, spreadsheet, import, export, data, how to

Id: WO7Js1Mh13c

Length: 31min 28sec (1888 seconds)

Published: Sun May 15 2016