Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Excel Automation 2 < Excel Automation | Excel Automation 3 >
Back to Excel Automation 2    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost              
6 months ago
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.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Excel Automation 2.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 11/6/2024 3:02:45 PM. PLT: 1s