Excel Automation 2
By Richard Rost
2 years ago
Automate Excel: Import Access Data with VBA, Part 2
In this Microsoft Excel tutorial, I will show you how to import data from Access to Excel using VBA, apply formatting, handle multiple sheet imports, and selectively import specific fields. This is part 2 of 2.
Members
There is no extended cut, but here is the workbook file download and link to the VBA in the Code Vault:
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 Excel, automate Excel with VBA, import data from Access to Excel, Excel VBA import data, creating buttons in Excel VBA, formatting imported data in Excel, handling duplicate sheet names in Excel, using SQL in Excel VBA, customizing imported fields in Excel, looping through records in Excel VBA, sorting imported data in Excel
Intro In this video, we'll continue our Microsoft Excel automation series by showing how to import records from a Microsoft Access database directly into Excel with a VBA button. I'll cover how to handle duplicate sheet names by adding timestamps, delete old sheets, and format your imported data with custom font and background colors. You'll also see how to use VBA to loop through records, auto-fit column widths, align columns, and customize which fields you bring over from Access using SQL statements. This is part 2.Transcript This is part two of my Excel automation from Excel video series where I'm showing you how to make a button to bring data in from Access from Excel. Makes sense? Okay. This is part two, so if you haven't watched part one yet, go watch part one where I show you how to set everything up.
Today, we're going to continue with importing the records from our table and applying some formatting and some other pretty stuff. Okay, so here we are back in our sheet. Yesterday, we made our little button here to import our customers. You click on it. Oh, see, the name's already taken. See, if you already got that name, right, debug, then you can't create that sheet again because you already got it there.
So if you really want to address that, if you plan on importing more than one sheet per day, you could just do the format here and do like yyyy, mmdd, and then - H H N N S S. Remember N is for minutes. So save that. Now we should be able to do it again. Let's see. Click. And there you go. Now we've got our minute and seconds. Instead of just that. Whichever one you want. If you plan on doing it multiple times per day, that's what I would go with right there.
All right, but let's delete these sheets down here. Delete. Yes. Okay. So what did we leave off with? We left off with this. Looks good. I'm gonna get rid of that, okay, that import. You can leave that if you want to, but I find it annoying. Maybe put a beep down here.
Alright. Okay. So we've got the data imported. Actually, let's, before we do that, apply formatting and it's with WSRows1, the first row. Font color equals that, we're going to make it white, interior color and remember access is background color in Excel, it's interior color and I picked a dark blue background, okay? So, let's test it, make sure it works. Let's go back over here, hit the button again, boom, and there we go.
Okay, and yes, you're going to keep getting a new sheet every time you do it. I'll just delete that one and okay back to our code. Now we have to do the actual import of the data and that is gonna look like this. Okay, so RS move first that means move to the first record of our record set. R equals 2. I want to start at row 2 in my spreadsheet, because row 1's got the headers in it.
Do while we still have records, but we're not at the record set end of file. That's what that means. It goes back to the text file days. For L equals 0 to RS fields dot count minus 1, that's columns, that's our columns. So WS sells whatever row we're in, column plus one, or field plus one for the column. Its value equals the field's value from the record coming in from access. OK, then repeat for each field, move to the next record, increment the row, and move forward. OK, so it's going to run through all the records and then run through all the fields.
Let's give it a test. Always throw in a debug compile, come back over at me and hit the button. Ready, go. Looks good. Looks pretty good. Okay, we got to do our thing where we reformat the column widths. We did that in in access, we could do it in here too. All right, auto fit columns, and it's going to look like that. Same command as we had before. Ready, go. There you go. Everything looks good. And again, let's left align that first column, just to give you an example of how that works.
All right, switch back over here. Left align the first column, looks like that. Remember that Excel, here we can use Excel left because it's actually part of Excel so we can get away with it here. In Access we needed that negative 4131 because I didn't want to make a reference to the Excel library. I hate doing that. Debug compile, come back over here. Let's delete this and click the button again and boom, there you go. And notice how it's left aligned.
Okay, now, what if you don't want all these fields? What if you got sensitive information in your customer record? You don't want, you know, credit limit, you don't want customer since maybe. Okay, well, for that, we'll just make a query. Or, better yet, if you know what the fields are, okay, you can come in here and instead of having to make a query in Access, you can say select customer ID, first name, last name from customer T, order by last name, first name. Let me make that two columns so you can see it. Two lines of code like that. Your record source can be an SQL statement. Okay, and you get exactly what you want.
So again, let's delete this and then go. Yeah, I didn't think of that. This is the one thing I didn't run through I just decided to do in here. Okay, so it says you typed in an invalid name for a sheet or chart. Now this will make more sense if you hit debug. It'll show you what line it's on. This works just fine. I've done this a million times. I forgot about this guy. The sheet name, we're trying to set it equal to our record source, remember that? Customer T and then the date time stamp. Oops, someone's beaming in. We're making that tab name just a little bit too long. So instead of putting the sheet name in here, and I'll just run that out so you guys got that. I'll say WS.name. Actually let's do this. Let's leave that one alone. We'll just change it up here. Let's just make this say import. There. That'll be whatever you're importing because you might change this.
All right, now it should work. Ready? Go. And there's your import. Just the fields you wanted. Sorted by last name, first name. And there's your new sheet name. That threw me for a loop. But that's it. That's pretty much all you gotta do right there. I mean, it's not easy. And like I said in the last video, I don't work with Excel VBA every day myself, so I still have to look some of the syntax up myself. But you know, that's what lessons like this and books are for and the Google machine and chat GPT. It's understanding the concept that's important, understanding how this stuff works. You can get the syntax from anywhere. The actual commands. If you understand, okay, I have to open a connection to the database, understand SQL, understand how files work and file names, okay, understand looping through records, what these counters mean, that's the row, that's the cell, all right, just get the concept, just understand how it's working because even me, I've been doing this for 30 years, even I have to look up the syntax sometimes. Yeah, access I know like the back of my hand, I could do it in my sleep, but Excel, the few times that I do work with Excel VBA, I sometimes have to look this stuff up too. So don't feel bad if you can't remember it all. I don't remember it all.
Okay? All right. Gold members, this will be in the code vault where you can download the spreadsheet from the website. Everybody else, that's gonna be your TechHelp video for today. I hope you learned something. If you want to see more Excel VBA, let me know. I am planning on doing some Excel developer classes. I'm gonna be redoing all my Excel lessons soon. I know I've been saying that for a couple of years, but all the old lessons that are on my website are still good. All the material is still perfectly valid. It's just I want to make the video better quality because I recorded them before and in little small video windows. But yeah, that's going to be your TechHelp video for today.
Hope you learned something. Live long and prosper, my friends. I'll see you next time. And if you want more Excel, you know what to do. Put a comment down below. Say, I want more Excel. Okay, bye.
TOPICS:
Importing records from Access to Excel
Creating a button to initiate data import
Handling duplicate sheet names with timestamps
Deleting existing sheets
Formatting imported data in Excel
Setting font color and interior color in Excel
Using VBA to navigate records in Access
Transferring records from Access to Excel sheet
Auto-fitting column widths in Excel
Left-aligning specific columns in Excel
Filtering specific fields from the database import
Using SQL statements to customize data import
Handling long sheet names in Excel VBA
COMMERCIAL:
In today's video from Excel Learning Zone, it's part two of our Excel automation series. We dive back in where we left off. First, we sort out importing data from Access into Excel, addressing issues like sheet name conflicts. Then, we'll apply some formatting for a cleaner look, such as adjusting font and background colors. You'll learn how to loop through records, set column widths, and align data properly. We'll also touch on how to select specific fields for import, ensuring only the data you want is included. 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 should you update in the format to handle importing multiple sheets per day?
A. Change the date format to "ddmmyyyy"
B. Use "yyyy, mmdd, - H H N N S S" format
C. Change just the hour format to "HHmmss"
D. Remove the date and time format altogether
Q2. What Excel VBA property corresponds to the "background color" in Access?
A. BackColor
B. BgColor
C. Interior.Color
D. Background.Color
Q3. In the Excel import process, where does the coding start to load the data, excluding headers?
A. R = 1
B. R = 0
C. R = 3
D. R = 2
Q4. Which VBA command is used to adjust the column widths automatically to fit the content?
A. AutoAdjustColumns
B. ColumnsAutoFit
C. AutoFitColumns
D. FitColumnsAutomatically
Q5. How can you change the alignment of the first column in Excel using VBA?
A. AlignLeft = True
B. WS.Cells(1,1).HorizontalAlignment = xlLeft
C. WS.Cells(1,1).HorizontalAlignment = -4131
D. LeftAlign(1)
Q6. What is the suggested solution if you don't want to import certain sensitive information from Access?
A. Filter the sensitive information in Excel after importing
B. Use a different database without sensitive information
C. Modify the SQL statement in the code to select specific fields
D. Delete the sensitive information manually
Q7. What error might you encounter when trying to create a sheet with a name that is too long?
A. Runtime Error: 1004
B. Syntax Error
C. Invalid Name Error for a sheet or chart
D. Record Set End Error
Q8. According to the video, what is the most important aspect to understand when working with Excel VBA?
A. Remembering all the syntax
B. Understanding the underlying concepts
C. Memorizing common VBA functions
D. Knowing only specific commands
Q9. In the video, what is the primary purpose of 'RS.MoveFirst' in the VBA code?
A. To sort the records in ascending order
B. To move the cursor to the first row of Excel
C. To avoid end-of-file error while importing
D. To start from the first record of the record set
Q10. How does the video suggest you debug and test the VBA code during development?
A. Run the entire program without stops
B. Compile code regularly and use the debug feature when errors occur
C. Write all the code at once, then test
D. Manually cross-check each line of code before running
Answers: 1-B; 2-C; 3-D; 4-C; 5-C; 6-C; 7-C; 8-B; 9-D; 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 video from Excel Learning Zone continues our Excel automation series by focusing on how to bring data into Excel from Access using a button. If you have not yet gone through the first part of this series, I recommend starting there, as it covers the setup process needed for what we tackle here.
In this lesson, I continue with importing records from your Access table into Excel and show you how to enhance your worksheet with effective formatting options. This includes setting up the automation button we built previously and addressing a common issue: what to do when the worksheet name you want to use already exists. If you try to create a sheet with a name that already exists, Excel will give you an error. To avoid this, especially when importing multiple times a day, you can append a timestamp to the sheet name, including the year, month, day, hour, minute, and second. Using this method ensures every time you import, a unique sheet name is generated.
Once the import works reliably, you can clean up any extra sheets that get created from testing and start looking at improving the look and feel of your imported data. For instance, after successfully importing, you might want to delete any 'import' confirmation text or sound a beep to make the process smoother.
Formatting is essential for readability. I demonstrate how to set the font color of your header row to white and give it a dark blue background. Remember, in Access this is called background color, while in Excel it is interior color. After making these changes, test your workflow to ensure your formatting sticks once you re-import the data.
When bringing in the records, it is important to start at row two in Excel so the first row can be reserved for headers. As you go through your dataset, you want to loop through every record and for each field, transfer its value into the respective cell in Excel, then move on to the next field. This loop continues until all records are imported.
After the data is in Excel, you can make it even easier to read by automatically resizing the columns to fit the content. You can also align the first column to the left, just to give you an example of column formatting.
One common scenario is wanting to limit which fields you import. For example, perhaps you have sensitive fields in your customer table, like credit limit, which you do not want to bring over. Instead of importing everything, you can tailor your SQL statement to only select the fields you need such as customer ID, first name, and last name. You can even order the results by last name and first name for improved organization. With a custom SQL record source, you can easily control the data you import without making separate queries in Access.
If you try to use a very long string, like an entire SQL statement, as your sheet name, Excel may give you an error about sheet names being invalid or too long. You can simplify the naming by just calling the sheet something generic, like 'import', possibly with a timestamp if needed. Make sure the sheet name you generate is concise enough for Excel's requirements.
Even though the logic and process are straightforward, the exact Excel VBA syntax can be tricky, particularly for those who do not use it every day. Do not worry if you need to look up commands as you go. The most important thing is to understand the key concepts: how to connect to an external database, structure SQL queries, work with file and sheet names, and use loops to transfer records. With the concepts in hand, you can always research the exact commands when you need them.
For those of you with membership access, this code and sample spreadsheet are downloadable from my website. For everyone else, this concludes your TechHelp video for today. If this lesson has helped you or if you want to see more content on Excel VBA, let me know in the comments.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Importing records from Access to Excel
Creating a button to initiate data import
Handling duplicate sheet names with timestamps
Deleting existing sheets
Formatting imported data in Excel
Setting font color and interior color in Excel
Navigating records in Access with VBA
Transferring records from Access to Excel worksheet
Auto-fitting column widths in Excel
Left-aligning specific columns in Excel
Filtering specific fields from the database import
Using SQL statements to customize data import
Handling long sheet names in Excel VBA |
