Welcome to another TechHelp video brought to you by Access LearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about automating the importing of data from Excel to Access. Now, I've covered this before, but this is a specific situation where there might be some extra garbage in the spreadsheet. Sometimes people add stuff at the top, like extra rows with meaningless information, or summary rows at the bottom, which don't do you any good if you just want to get this data into Access and start working with it in your database.

In today's video, I'm going to show you how to automate that import and clean up any unnecessary rows that are in that spreadsheet. Today's question comes from Brent in Midwest City, Oklahoma, one of my gold members. Brent says, "I receive a daily quote report with multiple rows above the header and a total row at the bottom that I don't need. Currently, I manually clean up the sheet before importing it into Access, but I'd like to automate this process. I've watched the import tutorials, but I'm not sure how to handle the unnecessary rows in an automated setup. Any guidance would be appreciated."

So this was posted in the forums, and here is a sample of what Brent is dealing with. Here's his sheet right here, and you can see that there's some nonsense up at the top. I get stuff like this all the time, so he basically wants to delete these top rows up here that don't have normal data in them. Here's the rest of the data, and then at the bottom—it's not shown on here—but at the bottom, there are extra summary rows. So we want to get rid of all that stuff too.

I've put together a spreadsheet from the last Middle-Earth contact trade show where all the Middle-Earth people get together. I've got the contact date and time, customer notes, phone number, and so on. I've got some rows at the top here that might contain meaningless information, and then at the bottom, there's a count of how many records there are, and I don't need that stuff either. I just want the stuff in the middle. I just want the records because I want to pull this into my Access database and be able to work with it.

So what we're going to do is import this data into Access, as I've shown you previously, and I'll talk about that video in just a minute. Then we're going to delete everything up until and including that header row. I'll show you how to do that with a record set. We're going to loop through it. Then once that's done, we can look and make sure these are all valid records with valid dates, and as soon as we find one that's not, we'll delete everything after that point as well. So that's our algorithm.

Now, what do you need to be able to get to this point? Well, first, make sure you watch this video where I show you how to import a specific sheet from Microsoft Excel into your Access database. Watch the developer part of it because we're going to be using the same code today—that transfer spreadsheet code. This will, of course, require some knowledge of VBA. If you've never done any VBA programming before, go watch this video. It's about 20 minutes long and will teach you everything you need to know to get started. Also, go watch my video on record sets. A record set is a programming tool that allows you to loop through the records in a table. So after we import them, we can loop through and examine each record and decide what we want to do with it. We want to delete it, keep it, and so on.

These are all free videos available on my website and YouTube channel. Watch those first and then come back.

Now, before we get started, I want to mention that there are some things you could do manually. You could open up the sheet and delete these rows yourself. Or you could set up a named range and then just import that specific name range. But what Brent wants is not to have to mess with all this, and I get it. I've got some stuff myself that I have to import once in a while from other vendors, and it's a pain to open up the file, make changes, and save it. If I can save a minute a day by having something automated, that'll save me a couple of hours over the course of a year. So it's worth it if you can spend an hour or so to automate it, and then it's done. We're not going to do any manual manipulations here.

What we are going to assume, though, is that the first row here in the header has the phrase "Contact Date." If the person who controls this spreadsheet changes this, you may have to update your code. But in cases like this, most of the time, that doesn't happen often. So that's the only assumption we're going to make.

Let's start by getting the data into the database. This is the TechHelp free template. You can grab a free copy of this off my website if you want to, but this will work with just about any database. I already have a button right here called "Hello World," and we'll call this "Import Excel Data." I'm going to right-click, build event, that'll open up a code builder. Let me resize this to fit. Sometimes when I'm playing around off-camera, I resize this thing to make it easier to work with. Okay, Status "Hello World," let's go status "Importing Excel Sheet."

What's the command we did in the other video? Let's go over to get it real quick: docmd.transferspreadsheet. That guy. We are going to import, so acImport. Hit comma again. That'll ignore that version type. The table name I'm going to put it in is "ExcelImportT," comma. What's the file name? I'm going to go to the next line here, line continuation character. The file name—I'm just going to copy and paste it here because I've got it saved in my notes. I don't feel like going to get it again. It's right there. There's a file name: "Vmy_drive\\spreadsheet\\middle_earth_trade_show.xlsx" and next, most importantly, "HasFieldNames" is going to be set to false. That's it because it's going to have a header, and we're going to delete it when it comes in. Again, this is dependent on these columns not changing. Usually, when you get something from a vendor, for example, the layout of this doesn't change that often. It might; you might have to adjust this depending on any changes they make.

Let's test this to make sure it works. I'm going to hit save, come back out here, close it, open it, and hit the button. There we go, ExcelImportT. You can see now there's garbage in here. It automatically ignores any rows at the top that are completely blank, and it should also ignore any at the very bottom that are blank. So this one here is the last one that it imports.

Now we can see what we can do is loop through these records and delete everything up to and including that header row. We're going to look for "Contact Date."

The very next thing I want to do is either delete or clear this ImportT if it exists. If you just run a delete query and delete these fields, if they do change this in the future and add another column, you'll get an "F6" over here, and that data won't fit in this table. So I recommend just deleting the table, and then it'll recreate it when you run the import again.

Let's go back to the code editor. Before we import, we're going to come up top here and say, "Status: Deleting previous sheet import table." How do we do that? DoCmd.DeleteObject. What is the object type? It's acTable. What's the name? "ExcelImportT." Save it, and now if you run it, it'll delete that table and then import a fresh copy. Otherwise, it's going to keep importing on the bottom of the current one if the table already exists.

One problem that will happen is if you manually delete it because you know it's a temporary table and try to run your code, it will say, "I can't find it." So we need a little bit of error handling. Just say, "Hey, this next line might throw an error. If it does, just ignore it." We don't need robust error handling here; "On Error Resume Next" will work just fine. That says if this guy errors out, ignore it. But make sure to turn that off when you're done because you want to know if there's an error in any lines after that one. So here we'll say, "On Error GoTo 0," which turns the error handling back on.

Of course, if you want to learn more about error handling, there's a video for you.

Now we've got that spreadsheet data in my temporary table. I want to look at this—not the wrong table—I want to look (I deleted the table) at this again. There it is. I want to look at this and start from the first record and go down until I find that "Contact Date" header. Does this one have it? Nope. Move to the next record. Does this one have it? Nope. Move to the next record, and so on. Along the way, if that's not it, we'll delete that row. Then we'll have the first record of the data we want.

That's our algorithm. How do we code that?

We'll need some variables. Let's use "rs" for our record set. We'll also need to indicate whether or not we're done, so "done" as a Boolean. I like to put the values I'm reading and evaluating in a variable too, like "s" as a string. Now we'll do "Status: Deleting header rows" and set "rs" to "CurrentDb.OpenRecordset(ExcelImportT)."

I always put the shell of it in here. I say, "While Not rs.EOF" and then "rs.MoveNext," and then "Wend" and then "rs.Close" and "Set rs = Nothing." I always put that stuff in when I'm working with record sets—it's the basic shell of the loop. Don't forget to move next and don't forget to close things up at the end and clear your variables. But now I'm not just looping until the end of the record set; I'm looping until there are no conditions where I'm done—I'm done when I hit that "Contact Date" field.

First, let's initialize "done" as false, and we'll say, "While Not rs.EOF and Not done," which means both of those conditions have to be true. You have to be inside the record set loop, not at the end yet, and not done. Once done, exit.

We'll read that first field, which will be "f1." Those are just field names the import assigns. We're looking for "f1," which equals "Contact Date."

I'll say "s = rs!f1." The problem is if that's null, it'll give me an error because "s" is a string, and you can't easily put nulls into strings like that. So we'll use "Nz" and say if that is a null value, put an empty string in there. That just gets rid of the "Invalid use of Null" problem.

Now we'll say if "s = 'Contact Date'," then "done = True." I'm done. This will be the last row I have to delete.

We'll say "rs.Delete" at the current record, "rs.MoveNext" is already there, and then it loops.

With the algorithm here, let's take a look at the records. Let me try to get these sideways. We'll start with "done" as false. I'm sitting on this record now, and, "if rs.EOF—nope, that's not true—are we done? Nope, not done." "S" equals that first record; it's "Middle Earth," so it's not "Contact Date." We'll delete that row and move to the next record. Deleting records with a record set loop is a little different than deleting records in tables, queries, and forms. This record will stay there; it'll just say "Deleted." You still have to move the cursor to the next record. So delete that record and advance the cursor.

Move to the next record. Now it's "null" or "empty string," so continue on. Delete it, move to the next record, and continue. Now we're on "Contact Date." So now s is "Contact Date," yes. So "done" is true. We've set "done" to true, but we're still going to delete and move next. So delete this, move next. I'm sitting here, but now loops; "done" is true, and it exits. This should have the effect of deleting all at the top of the table.

Ready to see it in action?

Everything looks good. Debug, compile once in a while, and hit it. Now, let's take a peek. There we go. Let's double-check: is Bilbo Baggins the first record? Yes, Bilbo, Frodo, Golem, araGrad. We're good. We got the correct records in there.

Next up, we have to go down this list and find out where the last valid record is and delete everything after that. It's similar to what we just did with a slight twist, and we'll talk about that in tomorrow's video. Tune in tomorrow, the same time, the same channel. Or, if you're a member, you can watch it right now because that's one of the benefits of being a member.But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.

A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, Manufacturing Experts, specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.

Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist and he not only offers access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at ShamaConsultancy.com.

TOPICS:
Automating data import from Excel to Access  
Handling extra rows above the header  
Removing summary rows at the bottom  
Looping through records with record sets  
Checking for valid data before import  
Using DoCmd.TransferSpreadsheet for import  
Error handling in VBA code  
Deleting existing tables before import  
Setting up variables for record sets  
Identifying and removing header rows  
Deleting unwanted rows with record sets

COMMERCIAL:
In today's video, we're going to learn how to automate importing data from Excel to Access, even when the spreadsheet comes with extra garbage like unwanted rows at the top or summary rows at the bottom. You will discover how to clean up these spreadsheets for streamlined data entry into your Access database. Whether you're dealing with reports full of nonsense or need to automate repetitive tasks, we're here to make it easier. Plus, we'll take a look at deleting unwanted header rows and ensuring only valid data makes it into your database. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.