Import Specific Sheet
By Richard Rost
2 years ago
Import Specific Excel Cells into Microsoft Access
In this Microsoft Access tutorial, I will show you how to import a specific range of cells from a particular Excel sheet into your Access database. We will explore both an expert method using a step-by-step wizard and an advanced approach utilizing VBA and the transfer spreadsheet command to automate the process.
Jennifer from Marysville, Washington (a Gold Member) asks: How can I import a specific range of cells from a particular sheet in my Excel workbook into Microsoft Access?
Members
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, Excel to Access, specific cell range import, Excel sheet import, Access database import, VBA transfer spreadsheet, named range import, importing English 101 sheet, Access data import wizard, Access VBA tutorial, Excel range in Access, TechHelp import tutorial
Intro In this video, we will learn how to import a specific range of cells from a particular sheet in a Microsoft Excel workbook into your Microsoft Access database. I will show you two methods: first, using the External Data Wizard in Access without any programming, and then an automated approach using VBA and the DoCmd.TransferSpreadsheet command. You will also see how to specify worksheets, set data types during import, add primary keys, save your steps, and work with named ranges and temporary tables for Excel imports.Transcript Today we're going to see how to import a specific range of cells from a particular Excel sheet in a particular workbook into your Microsoft Access database. Today's question comes from Jennifer in Marysville, Washington, one of my gold members. Jennifer says, how can I import a specific range of cells from a particular sheet in my Excel workbook into Microsoft Access? That's what I just said. That's what we're going to do.
Now, this will be a two-part lesson. First, we're going to do the expert method. It's not quite beginner importing data. There's something I cover in my expert classes because there's some nuance to it. But you don't have to know any programming for the expert version. I kind of put expert between beginner and developer. Developer is where we use some VBA. So we're going to do both methods. First, I'm going to show you how to do it without any programming. We'll use the step-by-step wizard. And then we'll do it with VBA, with the transfer spreadsheet command. All right, here we go.
Now, for you expert users, if you've never imported data into Excel before, go watch this video first to cover all the basics of importing. Okay, so here I am in my TechHelp free template. This is a free database. You can grab it off my website if you want to. And we're going to go to external data, new data source, from file. I'm going to pick Excel. All right, this thing comes up. They're going to browse to where your folder is that's got the file in it and pick the file. So I'm going to click on browse. Let's do my student grades. Now, you'll see a little preview comes up over here. You can resize this, and you can look through the different sheets that you have in here and click on them. But this doesn't really do anything. Okay, you can click and look at stuff. But nothing that you do here in this preview matters.
Okay, I'm going to hit open. And then we're going to pick import the source data into a new table. So we're going to basically take that spreadsheet data and put it in where we want it in the table in here. Hit okay. Now, here's where you can pick which worksheet you want. You can pick worksheet or named ranges. I do have a named range in here. We'll talk about that in a bit. All right, show worksheets. I'll pick the English 101, which is the second one. Let me show you a copy of the spreadsheet here. This is it real simple. All right, I got Math 101, and I got English 101. There's two sheets inside this workbook file. All right, so let's import English 101. I'll hit next. The first row contains the column headings. Yep, those will become your field names. I know they're not named perfectly because there's spaces, and then we'll deal with that later.
Next, now here's where you can go and you can pick each column. You can set the data type for it. All right, student, short text is fine. Test one. It defaults numbers to double. That's okay if you've got 92.6 or whatever in here, or you can change these to long integers if they're just long integers or whatever else you want to do. Just click on each field. I talked about this in the other video. Next, let Access add a primary key. If you want an auto number over here, yeah, sure. Or you can pick your own. Next. Then what table do you want? I'll call this English 101T just because that's how I like to name things and then hit finish. If you want to save these steps, you can. So you can do this in the future really quickly. I cover that in the other video as well. And there we go. We have a nice table. There's our data. Okay.
Now, that works great if you want to be able to just go through that wizard every time you want to import it. Okay. Or you can use a saved import. That's fine. But if you want to do it with a single button where you click a button and bang, there's your data, that's going to involve a little bit of programming. So let's close this, and I'm going to delete that table. Get out of here. There it is. Goodbye.
Now, what do we need to do for the programming part? Well, if you've never done any programming before, go watch this video first. It's my intro to VBA. Takes about 20 minutes to get you up to speed. It'll teach you everything you need to know to get started programming in VBA. It's not hard. And also go watch this video. This is where I introduce you to the do command transfer spreadsheet command, which we're going to use more of today. Again, these are free videos. They're on my website. They're on YouTube channel. Go watch them. Come on back.
All right. So the VBA solution. Let's go into our design view. I'm going to close up the ribbon. It's room here to work. And I'm going to just use this hello world button right-click build event. That's going to bring up the code builder. There you are. And we'll say status importing. Okay. So the command is do command not transfer spreadsheet. It's that guy transfers spreadsheet. Okay. Now, you can pick there's a whole bunch of different versions here. If you just hit comma, it's going to use the default one, which is good because in the future, right, in future versions of Access. If they add new ones, then it'll just use the current one. Sometimes you do need a specific version. There are some older commands that aren't supported anymore. So if you have to, for example, you know, you got to use Excel 5. Great. So I just hit comma and it will always use the current one. Make sure there's nothing there. Use the default.
Okay. Next is the table name. That is the name of the table in your database where you want to put the data. All right. Let's just say I'm going to call this my Excel import T because nine times out of 10 when I import stuff from Excel, I don't put it directly in the table that it's going to. I usually put it in a temporary table that I use some action queries to put that data where I want it. So I'll like import from Excel into a temporary table, use an append query. Let's say to put it in my actual wherever it's going. Okay. So usually importing is a temporary step. Next is the file name. I know you can't see all that. Let me slide the video window over there. Got some more space. Next is the file name, which I'm just going to copy and paste. For me, it's that. That's the full path and file name of your spreadsheet. Okay. If you don't know how to find that, you're in the wrong class.
No, I'm kidding. Open up an explorer window to where your file is. There it is. And then click up here in the address bar that will give you the path right there. And then just put a slash in the name of your file after. Okay. Okay. All right. Running out of room here. So I'm going to hit comma and then know there's one more thing I want to do for now. Actually, let's put a little line break here. Move that over. There we go. All right. And the next thing is, sorry, I got to keep moving, but the little window is popping over there. Okay. The next thing is has field names. And that basically means do you want to use these column headers in your spreadsheet as the field names in your field? Yeah. Okay. Sure. Why not? We'll say true. All right. Let's leave it at that for now. Let's save it. Throw in a debug compile once in a while. And then let's come over here. Close this. Open it back up again and hit the button. And then you should see over here. There's my Excel import T and it looks good.
Now we didn't specify anything about the inside of that file. What we wanted to import. And as you can see, it grabbed the data from the first sheet that it finds. In this case, Math 101. Well, what if I want English 101? Well, that's where that next option comes in. Let's close this. Let's delete the table. Let's go back to our VBA editor. All right. The next thing here is range. What's the range? Well, for the range, we can specify an actual range if you want to. For example, let's say I do A1 through E8. Okay. You know how to do an Excel range. That's going to be A1 through E6. Actually, let's do E6. We'll just get through work. All right. A1 through E6. All right. Save that. Let's run it now to what happens. All right. There's my Excel import, and there's.
Oh, did we get E6? What did we get? E6. Oh, I'm in the wrong sheet. See, I'm still on the English sheet. Let's get here. Okay. We got through. We're all good. All right. So that's exactly what I told to import. We can get the stuff off the English 101 sheet. Well, referencing different sheets in your command here is very similar to how you reference different sheets in Excel itself. Let's say you want to just import the entire English 101 sheet. Okay. It's going to look like this. English 101 dollar sign. The dollar sign says that's the sheet that I want. Okay. And if I run it now, save it. Come over here, hit the button. There you go. It imported the entire English 101 sheet. Same.
You can do the same thing with the range too. Let's delete this again. You can say I want English 101 A1 through, let's see who do we want. Again, let's get through work. E6. All right. E6. E6. There we go. Save it. And run it. And there we go. We got through work. Okay. That's how you do a range and then a different sheet and then a different range on a sheet. The only thing left is what about a named range? If you're not familiar with named ranges, you can set up a range in your sheet anywhere and give it a particular name. For example, I have one that I call her. Is it, come here. I have one that I call grades, and it's right there. Why there? I don't know.
Let's say over here, I'm on English 101. Let's select Riker through Jority and just their first two tests. And I'm going to give this a name. Let's call it Riker G. Okay. So now I made a named range right there called Riker G. It's just those cells on just that sheet. So now if I come back over here, again, let's delete that import table and let's change our code. Now I want to import Riker G. Okay. Save it. Come over here. Hit the button and it will import just those cells. And look at that. Okay. See, look at that. It made the column headings because we used that true instead of changing it to false. It made that column headings the name of the fields, which is not desirable in this case. So what I'm going to do is I'm going to go back over here and make this false. So it does not use the first column as the names of the fields. Ready? And there you go. It's F1, F2, F3, but at least that's better than this being Riker and this being 99 and so on. That's what that option is for. All right. So there you go. That's how you do it.
If you'd like to learn more, I do have a lot of Excel classes too. I know most of what I do is Access, but I cover named cells and ranges in my Excel expert level one class. I cover a lot more with importing data from Excel into Access starting in Access expert level 20, continued in 21. And of course, I've got tons and tons of different Access developer lessons on my website. I think I'm up to 45 now, different levels of developer classes. So if you want to learn how to program in VBA for Microsoft Access, this is your place to do it. But that's going to do it for your TechHelp video for today, folks. Hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
- Importing specific Excel cell ranges into Access
- Using the External Data Wizard in Access
- Importing worksheets without programming
- Setting data types during import
- Adding primary keys in imported tables
- Saving import steps for future use
- Creating VBA code for Access automation
- DoCmd.TransferSpreadsheet command in VBA
- Using temporary tables for Excel imports
- Specifying file paths and table names in VBA
- Using field names from Excel as Access field names
- Importing specific Excel sheets in VBA
- Specifying Excel ranges in VBA code
- Importing named ranges from Excel
COMMERCIAL:
In today's video, we're going to learn how to import a specific range of cells from a particular Excel sheet into your Microsoft Access database. This is a two-part lesson. First, you'll see the expert method using a step-by-step wizard—no programming required. Then, we'll show you how to automate the process with VBA using the TransferSpreadsheet command. Whether you prefer the hands-on or automated approach, this tutorial covers both. Get ready to learn some cool tricks! You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the first step to import data from an Excel sheet into Microsoft Access using the non-programming method?
A. Create a new Access database
B. Use the step-by-step wizard
C. Write a VBA script
D. Use the import/export tool
Q2. In the non-programming method, when you first import Excel data, where should you navigate to access the import wizard?
A. File > Import Settings
B. External Data > New Data Source > From File
C. Data > Import from Excel
D. Tools > Data Import Wizard
Q3. What should you select if you want the column headers from your Excel sheet to become field names in Access?
A. Leave the option unchecked
B. Specify primary keys
C. Check the option "First row contains column headings"
D. Rename the columns during data import
Q4. How can you set the data type for each column when using the step-by-step wizard to import Excel data into Access?
A. It cannot be set in the wizard
B. It must be set in Excel before import
C. By selecting each column in the wizard and choosing the data type
D. By manipulating the data in Access after import
Q5. What is the purpose of letting Access add a primary key during the import process?
A. To import data faster
B. To help uniquely identify each record in the new table
C. To overwrite existing data in Access
D. To enhance data security
Q6. Which VBA command is used to import Excel data into Access in the programming method?
A. DoCmd.ImportExcel
B. DoCmd.TransferSpreadsheet
C. DoCmd.ExcelImport
D. DoCmd.DataTransfer
Q7. In the VBA method, which parameter is used to define whether the first row of your spreadsheet is used as field names in Access?
A. ColumnHeaders
B. HeaderRow
C. HasFieldNames
D. UseFirstRow
Q8. How do you specify an Excel sheet as an import source using the VBA TransferSpreadsheet command?
A. Specify the sheet name in uppercase letters
B. Append the sheet name with a pound sign (#)
C. Use a dollar sign ($) after the sheet name
D. Place the sheet name in double quotes
Q9. How can you reference a named range in Excel when using VBA to import data?
A. Use the range name directly in the Range parameter
B. Enter the range manually in the Excel file
C. Use the range name after the file name
D. Reference the named range with a hashtag (#)
Q10. When using VBA to import only a specific range of cells from an Excel sheet, how should the range be formatted?
A. As a single row index
B. Using Excel's notation, such as A1 through E6
C. By inputting column letters only
D. With comma-separated values
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-A; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will guide you through the process of importing a specific range of cells from an Excel sheet into your Microsoft Access database. This lesson addresses a common question from users: how can I import a specific range of cells from an Excel workbook into Access?
We'll tackle this with a two-part lesson. First, we'll explore the expert method, which lies between beginner and developer levels. This does not require programming knowledge, and we'll use a step-by-step wizard for this approach. Then, we'll proceed to the developer method, which involves using VBA and the TransferSpreadsheet command.
Let's start with the expert method. To begin the import process, go to External Data, select New Data Source, and choose From File, and then Excel. After locating your file, select it, and choose to import the source data into a new table within Access. You'll then decide which worksheet or named range to import. You can view your Excel file to ensure you're selecting the correct sheet. For example, if your workbook has Math 101 and English 101 sheets, select the desired one and proceed.
You'll confirm that the first row contains column headings, which will become field names in Access. You can further customize by setting data types for each column. Allow Access to add a primary key or choose your own. Name the resulting table appropriately and finish the process. Optionally, you can save these steps to streamline future imports.
If you prefer a more automatic process, you can utilize VBA programming for the task. This requires some familiarity with VBA. If you're new to this, I recommend watching my introductory VBA video, which covers the essentials. The VBA method involves using the DoCmd.TransferSpreadsheet command. You'll specify the table name, the file name and path, and whether the spreadsheet's column headers should be used as field names in Access.
The range option allows you to define specific cells to import. This can include entire sheets or just designated ranges. For instance, using "English 101$" imports the entire sheet, while "English 101$A1:E6" targets a specific range. You can also utilize named ranges from Excel by simply referencing them in your VBA code. Adjust the HasFieldNames parameter depending on whether you want to use the spreadsheet headers as field names.
This concludes the session on importing Excel data into Access. For more detailed guidance, including step-by-step instructions, refer to the complete video tutorial available on my website.
Live long and prosper, my friends.Topic List - Importing specific Excel cell ranges into Access
- Using the External Data Wizard in Access
- Importing worksheets without programming
- Setting data types during import
- Adding primary keys in imported tables
- Saving import steps for future use
- Creating VBA code for Access automation
- DoCmd.TransferSpreadsheet command in VBA
- Using temporary tables for Excel imports
- Specifying file paths and table names in VBA
- Using field names from Excel as Access field names
- Importing specific Excel sheets in VBA
- Specifying Excel ranges in VBA code
- Importing named ranges from Excel |
