Excel Automation
By Richard Rost
18 months ago
Construct an Excel Sheet Using VBA Automation
In this Microsoft Access tutorial, I will show you how to use VBA to automate the creation and formatting of an Excel workbook from an Access database. You will learn how to send data from Access to Excel and apply formatting like colors and cell alignment to make your exports more presentable.
Lydia from Santa Clara, California (a Platinum Member) asks: I'd like to be able to export some data from my Access database to send to customers. Since they don't have Access, I'm using an Excel export. I'm using the simple export that you showed in your previous videos, but it's not very pretty. Is there any way to add some formatting to this? Maybe some colors, change the cell alignment, that kind of stuff.
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, VBA Excel automation, exporting data from Access, Excel formatting VBA, automate Excel formatting, Access to Excel export example, Excel cell alignment VBA, add color VBA Excel, creating Excel sheet VBA, set Excel application VBA, late binding VBA, early vs late binding VBA, looping through records VBA
Transcript
Got a good one for you today, folks. We're going to learn a little Excel automation that's taking Microsoft Access and controlling Excel from it. Instead of just a simple export, we're going to actually use VBA to create a workbook and send the data to the cells and apply some formatting and do some cool stuff that you can't do with just a simple export.
So this is gonna be fun. Today's question comes from Lydia in Santa Clara, California, one of my Platinum members. Lydia says, I'd like to be able to export some data from my Access database to send to customers. Since they don't have access I'm using an Excel export.
Reasonable. I'm using the simple export that you showed in your previous videos, but it's not very pretty. Is there any way to add some formatting to this? Maybe some colors, change the cell alignment, that kind of stuff.
Yeah, one of the troubles is if you're doing a simple export like I show in this video, you really don't get a lot of options when you get over into Excel as far as formatting, unless you apply that formatting yourself. If you want to go in there after the export and change cell alignment, change colors, all that stuff, great. But if you want to automate that, then you have to use something called Excel automation, and that's what we're going to show you how to do today.
Now this is a developer-level video. What does that mean? Well that means we're going to be using extensive amounts of VBA in this class. Lots of it. Not just a couple lines. This is quite involved, so if you've never used VBA before, go watch this video first. This will get you started.
You're also going to need to understand how to use record sets. We need to create a record set to loop through the records in a table or query and one by one send them to Excel. So this is going to be very important to watch this first. And also go watch my video on late binding. I like to use late binding in my VBA code when we're dealing with automating another application.
That way, you don't have to worry about adding references. It just becomes a pain. So go watch this video on early versus late binding if you don't know what that means. These are all free videos. They're on my YouTube channel. They're on my website. Go watch those and then come on back.
All right, the first thing I'm going to do is create myself a new folder to put my stuff in. Let's just call this Excel Automation like that. We're going to have the database in this folder, and we're going to create whatever spreadsheets we're going to make, whatever workbook files.
I know I call them spreadsheets because back in the day it was a spreadsheet file, but now it's a workbook file. I get it. All right. So I'm going to take a copy of my TechHelp free template. This guy here, I'm just going to rename this to TechHelp for now because we're going to be doing some work with files and stuff and I don't like those long file names with spaces and stuff in them.
So I'm going to put this inside of there and then open you up. There you are. And now let's open up the database. This TechHelp free template, by the way, you can get a copy of this off my website if you want it, but there's nothing special about this database. You can do this with any database that you want.
All right, so let's take the customer table, this whole thing here, and we're going to send this over to Excel. So I'm going to just hijack the Hello World button here. If you watch the other videos I told you, then you should know what this is. We're going to export to Excel using automation.
All right, right-click, Build Event. It's going to bring up my code builder. All right, here we are. We're not going to status Hello World. We're going to status Exporting Data, like that. I'm just going to say Data for now, because we could change the record source later. You could export orders. You could export products, whatever you want. This works with any table or query in your database.
Let's dim some variables. We're going to need a bunch of variables. We're going to need three objects to work with Excel. There's the application object, which basically is pointing to Excel itself as an application. So Excel app as an object.
Then we need a reference to the workbook file, so Excel workbook as an object. And then we need a reference to the specific sheet in that workbook that we're working with, so Excel sheet as object. You get all that? Okay, three objects.
Now we need some stuff in Access so that we can work with the data. So we need an RS as a record set, which if you watched my record set video, you know what that means. We need a couple of simple counter variables.
We need L as a long, that's just for a loop, and another one, R as a long. That's going to indicate what row we're on in the spreadsheet, because as we move down through the rows, we're going to read in the records and then move down through the rows, and it's handy to know what row we're on.
Also, file name as a string to store the file name that we're working within. And one more, record source as a string, so we know what object we're actually exporting. And then let's set that record source before we status.
So we're going to say the record source equals, let's do the customer table first, so customer T. And now instead of just saying exporting data, we can say exporting record source. We know what we're exporting. It'll say exporting customer team. And if you want, later on, you could even send this in as a variable, and then export whatever you send to the function.
We're going to keep it simple for right now. Okay, we dimmed a bunch of variables. Let's set those variables up now. And the object needs a set in front of it. Set Excel app equals create object and it's going to be Excel.application.
This is one of those things you've just got to do. There's like a framework of stuff that you have to do to set this up right. I'll be honest with you, I don't remember it all the time myself. I have to look back at my older code or even Google it right. But that is an Excel application object.
So now Excel app is set equal to all of Excel. Now if you want to see this happening as you're working with it, we're going to say Excel app.visible equals true. Make the Excel application visible. If you don't want to see it and you want it to just do this in the background, set that to false. That's completely up to you. You're Legos.
Okay, so we got a blank Excel application object just running in the background. Now we're going to create a new workbook. So set Excel workbook. One of the problems also with this is that you don't always get IntelliSense when you're working with late binding because Access has no idea what these objects are.
If you use early binding and you set a reference up here under Tools, References, you can set a reference to the Excel library, then Access will know what all these Excel objects are. But unfortunately, we're not doing that. And we're not doing that because it makes the code more portable to keep it in late binding, which I explained in my late binding video.
I like working this way. Most of the time I'll start with early binding, get the code working, and then switch to late binding. All right, so what's our workbook going to be equal to? It's going to be equal to ExcelApp.Workbooks.Add. We're adding a workbook to the workbooks collection in that Excel application.
Now we're going to do the same thing and add a sheet. So set ExcelSheet equals ExcelWorkbook.Sheets(1), like that. Workbook.Sheets(1) like that because every workbook starts with one default sheet right when you open up Excel, right?
I open up Excel and you get one sheet to begin with. You can add more sheets later if you want to. Okay, now we've got the Excel book already set up, the sheets already set up. We're gonna do stuff here in a little bit, but right now, I want to close all this stuff down. I like to set it up, and then close it down first. Then we'll do the meat filling in the middle of our ravioli or the cheese filling. I like both. I like cheese raviolis more, I think.
We've created all these objects. Let's now destroy all these objects. So we're going to set Excel sheet equals nothing. Set Excel workbook equals nothing. And optionally right here, if you want Excel to quit, you'd say ExcelApp.quit. That'll shut it down. But I want to leave it open so I can look at it when it's done.
So I'm going to just rem. I'm going to leave that and rem it out so that you gold members have that in your code. And I'll put this in the code vault. And then set ExcelApp equals nothing. And then we'll status done at the end. And then maybe throw a beep in there.
OK. So here we are. We created our objects. We destroyed them. Now we're ready to do this stuff in the middle, which is going to be looping through the records in whatever our record source table or query is and then putting them in the spreadsheet.
Let's save this, give it a debug compile, make sure everything compiles. OK. And let's just run it real quick. Let's come over here, close this, and then run it and perfect. It created a workbook. Created the instance of Excel, created the workbook, and then it destroyed those objects in Access.
So Access is done. Where's Access? Access is done as far as it's concerned. It did all that and it handed you off to an Excel workbook. And now we're ready to fill this thing in with some data.
And we're going to do that in tomorrow's video part 2. So tune in tomorrow, same bad time, same bad channel, or if you remember you can watch it right now because I'm going to record it in about five minutes. And we will continue with filling in all the information from our database into our spreadsheet. But for now, that is 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 2.
TOPICS:
Excel Automation from Microsoft Access
Creating an Excel Workbook using VBA
Applying Formatting to Excel Cells
Creating a Record Set in VBA
Looping Through Records in a Table or Query
Exporting Data to Excel using VBA
Setting Up Excel Application Object
Creating and Using Excel Workbook Object
Creating and Using Excel Sheet Object
Making Excel Application Visible in VBA
Adding a Workbook to the Workbooks Collection
Referencing Sheets in an Excel Workbook
Setting and Destroying VBA Objects
Using Late Binding in VBA
Debugging and Compiling Excel Automation Code in VBA
COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to automate Excel using Microsoft Access and VBA. Instead of a simple export, we'll create a workbook, transfer data to cells, and apply custom formatting. Lydia from Santa Clara asks how to make her Excel exports look better, so we'll dive deep into VBA, loops, and record sets. First, I'll guide you through setting up your environment, creating Excel objects, and initializing variables. Then, learn how to generate a workbook and sheet within Excel. Tune in for tomorrow's video where we'll fill in the data. 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 primary purpose of this video tutorial?
A. To show a simple data export from Microsoft Access to Excel
B. To demonstrate how to use Excel macros for data formatting
C. To teach VBA to create and format Excel workbooks using data from Access
D. To explain how to use Microsoft Access to organize data
Q2. Which programming language is used for automation in this tutorial?
A. Python
B. C#
C. VBA
D. SQL
Q3. Why does the tutorial recommend using late binding for Excel automation?
A. It requires less code to write
B. It makes the code more portable and doesn't need references
C. It provides better performance than early binding
D. It allows for more complex operations within Excel
Q4. What should you watch first if you are new to VBA, according to the video?
A. A tutorial on Microsoft Access basics
B. A video on how to use record sets
C. A video on early vs. late binding
D. An introductory video on VBA
Q5. What object types are needed to work with Excel in the code demonstrated?
A. ExcelApp, Workbook, Cell
B. Application, Workbook, Sheets
C. ExcelApp, ExcelWorkbook, ExcelSheet
D. ExcelFile, ExcelWorkbook, ExcelColumn
Q6. In the tutorial, what method is used to make the Excel application visible?
A. ExcelApp.show()
B. ExcelApp.display()
C. ExcelApp.visible = True
D. ExcelApp.open()
Q7. What does the tutorial suggest to do first before starting the Excel automation process?
A. Open the Microsoft Excel application manually
B. Create a new folder to store the database and workbook files
C. Add formatting to the Excel template
D. Write a SQL query to select the data
Q8. What is the role of the 'record set' in this VBA automation process?
A. To store the Excel workbook
B. To define the Excel application object
C. To loop through the records in a table or query
D. To set up Excel sheet properties
Q9. What are the counter variables 'L' and 'R' used for in the VBA code?
A. To manage columns in Excel
B. To control the number of Excel workbooks
C. To indicate row positions and control loops in the code
D. To name Excel sheets
Q10. How does the tutorial suggest handling the reference to the Excel application's workbook?
A. By using early binding
B. By setting ExcelWorkbook = ExcelApp.Workbooks.Open
C. By setting ExcelWorkbook = ExcelApp.Workbooks.New
D. By setting ExcelWorkbook = ExcelApp.Workbooks.Add
Q11. According to the tutorial, what happens after the initial setup of the Excel objects?
A. The Excel application quits immediately
B. The data is transferred to Excel cells
C. The objects are destroyed, and the Excel application remains open
D. The Excel workbook is saved to a specified path
Q12. What is the tone of the video towards the end of the session?
A. Formal and educational
B. Friendly and engaging
C. Critical and instructional
D. Casual and humorous
Answers: 1-C; 2-C; 3-B; 4-D; 5-C; 6-C; 7-B; 8-C; 9-C; 10-D; 11-C; 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.
|
