Excel Automation 2
By Richard Rost
19 months ago
Construct an Excel Sheet Using VBA Automation, Part 2
In this Microsoft Access tutorial, we will learn how to automate the construction of an Excel sheet using VBA, focusing on setting up connections, extracting column headers, exporting data, and applying basic formatting. This is part 2.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, VBA automation, export data from Access to Excel, create Excel sheet with VBA, Excel column headers from Access, Access VBA recordset, automate Excel cell values, autofit columns in Excel, format Excel cells with VBA, left align column VBA, Excel automation VBA tutorial, VBA export to Excel, Excel sheet automation tutorial
Transcript
Today is part 2 of my Excel automation series, taking data from Access and putting it in Excel with automation so you can do crazy things like formatting and stuff like that. So if you haven't watched part 1 yet, go watch part 1, then come on back.
Alright, so yesterday we did the stuff to set up our Excel worksheet, create the file, and then close it all up. Now in the middle, we've got to do stuff. So first, before we actually do stuff, we're going to set up our connection to our table or query. So set RS, our record set, equals current DB dot open record set object. And if you've gone through my record set video, you know exactly what that is. That can point to a table or query. Record source is the name of whatever we're opening up, which in this case is customer T.
Alright. So the first thing we need is to get the names of the column headers for Excel, which are going to be the same as the field names in Access. Alright, so we'll put in here column headers. How are we going to do that? Well, every field in a record set has a field name property. So we just need to know which one it is.
So we're going to say for L, that's our long, equals 0 to RS.fields.count minus 1. What does that mean? Well, there's a field count property in a record set that tells you how many fields there are. And it's a zero-based index, so that means it goes from zero to n minus one. So if there's ten, it goes from zero to nine. So we're just going to loop through those. Next cell. And in here we're going to say ExcelSheet.Cells. sheet, sheet, sheet dot cells. This is how you make a direct reference to a particular cell in the spreadsheet. Okay, it's going to be row one, comma, L plus one location. So if we're on field zero, right, it's going to be cell one, comma, one, right, L plus one. If it's the next one, if it's first name, which will be the second column, right, it'll be row one comma two, right, L plus one. So we're going to just loop through all those. We're going to set the value of that cell equal to rs.fields L.name. So whatever field we're on, its name is going to be put into that cell. Make sense? Are you with me?
Alright, so if we're looking at this, L goes from 0 to whatever the count of the number of fields is. Minus 1. So the first one's going to be customer ID. That's going to be L equals 0. So in the sheet, we're always in row 1. That's the first thing. It's row, comma, column. So L plus 1 is 1. We're at 0. Its value is going to be whatever the current L field is, which is 0, its name is customer ID. All right, go to the next one. Now L is 1. So now we're looking at first name. So cell 1, 2 is going to be first name, and then last name, and then email, and then address. See how that works?
Okay. Now, again, I like to do this in pieces so I can see everything working. So we created the record set here, we did the column headers. Here we're going to export the data, but we're going to get to that in a minute. But now let's just close up the record set so we can see what we got and make sure it's working so far. So we're going to do rs.close and we're going to set rs equals nothing. This will be part of our cleanup area. We'll call this clean up. So here's all our Excel stuff. In fact, we're going to put this together with that stuff. Here's all the creating a bunch of stuff and the destroying a bunch of stuff. And you've got to destroy it in the reverse order with which you created it. So the Excel app goes first. It's the last thing to be destroyed. First and last up.
So right now, it should create the Excel sheet. It's going to create a workbook object, a sheet object, a record set object, set the column headers, and then exit and clean up. All right, you ready? Okay, one step at a time we're going to do this.
Alright, come back over here. We don't need you. All right, close it. Actually, let's give it a debug compile. Debug compile. All right, good. Back over here, open her up, and hit the button. And oh, look at that. Isn't that sexy? Look at that. There's all of our field names from our table. And I'm just going to select all of these and double click on one of them. And I'll show you how to auto do this later, so it automatically fits the columns to their widths. But there you go. All right, we're cooking with gas. We're getting somewhere. We've got the column headers in there. All right, close that, save changes. No, we'll save it later. We'll save it in our code, too.
OK, we're ready for the next step. All right, we're gonna say RS move first. You don't always have to do that because by default when you open a record set you should be on the first record. But it's nice to do. Make sure. OK, now we're gonna use our R variable to track what row we're on. So we're gonna set R equals 2 because the first row, R1, is going to be our... Huh, it's going to be our... do some stuff, okay? And then inside of your loop, don't forget, very important, rs.moveNext, and then we're going to say r equals r plus one just to increment our counter.
I always like to put the rs.moveNext and the while end inside my while loop. I probably should have mentioned while loops as a prerequisite for this, so if not, if you're not familiar with while loops, go watch this guy. Usually I assume by the time we get the record sets and all that stuff that you're familiar with the basic loop types and an if then statement and things like that. So if not, I got videos on all this stuff. It's on my website, go watch it. Come on back. That's the beauty of online learning is if you're not familiar with something, you can pause the video, go watch something else, and then come back to it. It's like taking a college course where you gotta go, oh, wait a minute, I gotta go sign up for this class. I'll see you next semester.
OK, now in here we have to go through the fields again in my table and send those out to the corresponding cells in Excel. It's going to look very similar to the stuff we did up here. Alright, we're going to reuse that L variable since we're done with it. We can use it again, that's fine. We don't need multiple variables. So for L equals 0 to RS fields count minus 1, same thing. And then we're going to use the zero two r s fields minus one same thing okay now right here we're gonna say something very similar in fact uh... i'm gonna copy this it's pretty much the same thing as this with one little change right that that cell's value is not the cell not the field's name but the field's value right we're doing the exact same thing we did before, except instead of putting the name in the cell, I want the actual value in this record in that field. That's pretty much it. Let's get rid of this. I think we are good to go. That's the core of it.
Let's see. Let's save this. Let's give it a debug compile. Let's come back over here and give it a test. Ready, click, and OK. All right. Oh, what's happening? Did you see what's happening? It's doing it all in the same row. I know what I did. I know what I forgot. Let's not save that. I know what I forgot. I'm leaving this mistake in the video because this is something that I've done a million times. You copy some code from somewhere else without logically running through it in your head. Now, what's happening here that it's putting all the data in row one? That guy. I forgot to update that because I just copied and pasted. I'm guilty of that myself, of taking old code that I know works, copy and paste it in a new location without running through everything in your head, and that happened. Right, because I just used this code. But once you see the mistake, it's easy to fix.
Save it. Let's try it again. Go. And oh, there it is. That's beautiful. Beautiful, beautiful, beautiful. And when it's all done, you hit a beep, and there's all your records. And yeah, these just don't fit. So we need to make it so that these guys automatically fit when we're done. I want Excel to do the same thing that I just did earlier, where I select all those cells and do that to it. And then everything fits nicely.
Alright, how do we do that? Let's close this again. Save changes, no. Alright, that's something we're going to come in here. And when we're done putting this stuff in here, we're going to say ExcelSheet.columns. And then inside of here, we're going to say A colon Z. That basically, in a nutshell, says select all the data in the sheet. That's kind of what that means. And then we're going to say.autofit, like that. We're going to autofit the data, the sheet. OK. Save it.
Again, always good to throw in a debug compile. One more time. You ready? Go. OK, it's filling its stuff in. And done. Look at that. You see that? The last thing it did was it auto fit all the columns. And yes, you can use automation to manually set column widths and all that stuff. Like if you want to make sure your notes don't go crazy long, you can do that with it.
Okay. Now, we've got all the data out here, but one of the whole reasons to do this in the first place was to add some formatting. We could get this with a simple Excel export. I want to be able to take this first column and left align it. I want to take the first row and maybe add a background color and a foreground color like that. Do some formatting with it.
How do we do that? Well, we'll talk about that in, let's see, today is what? Friday the 24th of May, 2024. We're going to talk about this in Monday's video. No, I lied. I take that back. Monday is a holiday. It's Memorial Day here in the United States, so we're going to cover this on Tuesday, Tuesday, the 28th of May, 2024. So come back then.
Yeah, I know it's not tomorrow, but I'm just, I'm keeping the slide. I like the slide. Tune in tomorrow. Same bad times, same bad channel. If you remember, you can watch it right now because I'm going to record it as soon as I'm done with this one. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. Have a good holiday weekend if you're here in the United States. The rest of you just have a good weekend. And I'll see you on Tuesday the 28th for part 3.
TOPICS:
Set up connection to Access table or query
Extract column headers from Access fields
Using loops to iterate through record fields
Set Excel cell values to Access field names
Close record set and clean up resources
Initialize row variable for data entry
Loop through records to export data
Set Excel cell values to Access record values
Debugging and fixing row increment issue
Auto-fit Excel columns to data width
Format Excel columns and rows
Save Excel workbook
COMMERCIAL:
In today's video from Access Learning Zone, I take you through part 2 of our Excel automation series! We'll start by establishing a connection to your Access database, grabbing field names to set up column headers in Excel. Then we’ll loop through and export all your data into the Excel sheet row by row. You'll see firsthand how to debug common coding errors and finally auto-fit those columns for a polished look. Get ready to elevate your data-transfer skills! 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 purpose of setting up a connection to a table or query in the tutorial?
A. To create a new Access database
B. To open an existing workbook
C. To retrieve data from Access into Excel
D. To format the Excel cells
Q2. Which object is used to represent the data retrieved from a table or query in Access?
A. Excel application object
B. Workbook object
C. Worksheet object
D. Record set object
Q3. How is the record set object created in the video tutorial?
A. Using the Excel object library
B. Using the CurrentDB.OpenRecordset method
C. By directly importing from an Access table
D. Using a web query
Q4. How does the tutorial suggest identifying the Excel column headers corresponding to fields in Access?
A. By hardcoding the headers
B. Manually entering the column names in Excel
C. Looping through the record set fields and capturing their names
D. Using a pivot table to manage the headers
Q5. In the example, what does 'RS.Fields.Count - 1' represent?
A. The total number of rows in the table
B. The index of the last field in the record set
C. The total number of columns in the Excel sheet
D. The total number of records to be updated
Q6. Why is 'RS.MoveFirst' used in the video tutorial before the loop?
A. To close the record set
B. To ensure the record set cursor is at the first record
C. To finish processing the last record set field
D. To delete the records in the record set
Q7. What is the purpose of the 'r' variable in the tutorial?
A. It tracks the current record in the record set
B. It specifies the column position in Excel
C. It counts the number of fields in the record set
D. It is used to determine the worksheet index in Excel
Q8. After using the 'rs.moveNext' method, what does 'r = r + 1' achieve in the loop?
A. Moves to the next column in Excel
B. Moves to the next sheet in the workbook
C. Skips the current row
D. Increments the row counter for Excel
Q9. How are the values from the record set copied into Excel cells?
A. By setting the cell values to 'rs.Fields(L).Name'
B. By setting the cell values to 'rs.Fields(L).Value'
C. By copying the entire record set at once
D. By writing a nested loop
Q10. What issue does the video demonstrate when initially attempting to populate rows, and how is it fixed?
A. Incorrect column assignment; fixed by debug compiling the code
B. All data populates into row one; fixed by updating the row variable in the loop
C. Wrong workbook opened; fixed by selecting the correct workbook
D. Columns not fitting; fixed by setting the column widths manually
Q11. What is the purpose of '.AutoFit' method in Excel?
A. To copy data from Access
B. To automatically adjust the cell sizes to fit the content
C. To generate new worksheet
D. To delete unnecessary columns
Q12. Which part of the code is responsible for cleaning up and destroying Excel objects?
A. rs.Open and rs.AddNew
B. ExcelApp.Quit only
C. rs.close and setting rs = nothing only
D. Destroying the objects in reverse order with which they were created
Answers: 1-C; 2-D; 3-B; 4-C; 5-B; 6-B; 7-A; 8-D; 9-B; 10-B; 11-B; 12-D
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 continues my Excel automation series with part 2. We are exploring how to transfer data from an Access database over to Excel while taking full advantage of automation features, including custom formatting and organization—much more than what a basic export would offer. If you missed part 1, I recommend watching that before continuing.
In the previous lesson, we covered preparing our Excel worksheet, creating a new file, and closing everything out properly. Now, we need to focus on what happens between setup and cleanup.
The first task is to connect to the desired Access table or query. To do this, I use a recordset object. If you've seen my recordset video, you are already familiar with how it works: it can point to any table or query, depending on the name you specify. For this demonstration, the table is called CustomerT.
Before exporting data, it's important to handle the Excel column headers. We want the column headers in Excel to match our field names from Access. Each field in a recordset has a name property, so we can loop through all fields and extract these names.
To accomplish this, I create a loop that runs from zero up to the number of fields minus one. This is important because field collections in Access are zero-based, so if you have ten fields, the index runs from zero to nine. Within this loop, I specify the cell in the first row of Excel corresponding to each field—meaning the header for the first field goes in row one, column one, the next in column two, and so on. I use the field's name property to populate the header. Once you see this in action, the logic becomes very clear.
For example, the first field might be customer ID. In Excel, row one, column one gets the name "customer ID." The next field, say "first name," goes into row one, column two, and the process repeats for each subsequent field.
I always like to work in small incremental steps to verify everything is functioning properly. After setting up the recordset and column headers, I close the recordset and release the associated resources to keep things clean and organized in the code. Remember, it's good practice to destroy objects in the reverse order they were created, so the Excel application is the last thing to be released.
At this point, the Excel sheet should be created, complete with the correct headers, and we are ready to move forward.
The next stage is exporting the actual data. While the recordset usually starts on the first record by default, I explicitly call MoveFirst just to be certain. The data rows in Excel begin with row two since the first row is reserved for headers. I use a variable to keep track of which row I am writing to and increment it after each record.
Now, within a loop that cycles through each record, I once again loop through all fields, but this time instead of writing the field names, I write the field values from the current record into the appropriate cells. It is easy to resort to copying and pasting code from earlier places, but you have to be mindful to update variables like the row number—otherwise, you might get all your data piling up in a single row, which is a common error.
After correcting any such issues, the export process works smoothly, and you can watch as each record from Access is transferred to its own row in your Excel sheet. At this stage, you've matched the structure and the data, but the appearance still needs improvement.
Often, when transferring data like this, the column widths are not quite right. The data can look cramped or cut off. To fix this, I utilize the Excel automation feature to auto-fit all the columns. This is done at the end of the data export so every column width matches its content, giving you a much more professional and readable spreadsheet.
Of course, one of the big advantages of automation is the ability to add a variety of formatting options that go beyond simple data placement. For instance, you might want the headers to have a background color, bold formatting, or change the alignment of certain columns. We will look at these formatting enhancements in part 3, coming up in the following lesson. There will be a brief pause for the Memorial Day holiday, so look for that video on Tuesday, May 28, 2024.
To recap, in this second part of the series, you learned how to set up a connection with your Access data, dynamically pull field names for column headers, export all records row by row, debug and correct common code mistakes, and apply basic formatting by auto-fitting columns. This sets the groundwork for adding more sophisticated formatting options and saving your completed Excel file—all topics we will explore in the next installment.
If you want a complete step-by-step walkthrough of everything discussed here, including code samples and demonstrations, visit my website at the link below to watch the full video tutorial.
Live long and prosper, my friends.
Topic List
Set up connection to Access table or query
Extract column headers from Access fields
Using loops to iterate through record fields
Set Excel cell values to Access field names
Close record set and clean up resources
Initialize row variable for data entry
Loop through records to export data
Set Excel cell values to Access record values
Debugging and fixing row increment issue
Auto-fit Excel columns to data width
|
