Excel Automation
By Richard Rost
2 years 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
Intro In this video, we will learn how to automate Microsoft Excel from Microsoft Access using VBA. I will show you how to set up your development environment, create and manage Excel Application, Workbook, and Sheet objects, make Excel visible from Access, and prepare variables for exporting data. We will also talk about using record sets, setting up late binding, and the initial steps for exporting Access data to a new Excel workbook with the ability to add formatting. This is a developer-level tutorial focused on the basics of Excel automation with VBA from Access. This is part 1.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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on automating Excel directly from Microsoft Access using VBA. Our goal is to move beyond basic data exports and instead use VBA code to create a new Excel workbook, send data to specific cells, and apply formatting to enhance the presentation. This approach gives you much more control over the output compared to a standard export.
This lesson comes from a question about exporting Access data for customers who do not use Access themselves, making Excel the preferred format. The built-in export features in Access are functional but provide limited room for customization, especially when it comes to formatting options like cell colors and alignment. If you want to add these visual improvements automatically, you need something called Excel automation, which we will be working with in this tutorial.
I want to note right up front that today's session is intended for developers who already have some experience with VBA. The project will involve a considerable amount of VBA and will require a good understanding of various concepts like record sets and binding methods. If you are new to VBA, I suggest watching my introductory videos on the subject first to get comfortable with the basics. I also highly recommend reviewing my lessons on record sets, as we will be creating and looping through them to send data from Access to Excel, and my video on late binding, which explains the difference between early and late binding. I prefer late binding because it makes your code more portable and avoids issues with missing references.
To get organized, I suggest creating a new folder for this project, perhaps naming it "Excel Automation." Place your Access database there, along with any Excel files you create along the way. I am using my TechHelp free template as a starting point for the database, but you can use any Access database you like.
To demonstrate, I will be exporting the contents of a Customer table over to Excel. I am repurposing a command button in my database form to initiate the Excel automation process. If you have followed along with my previous videos, you should be familiar with this general setup.
First, I declare all the variables needed for the process. We need three objects for Excel itself: the Application object to represent Excel as a whole, a Workbook object for the file you'll be working with, and a Sheet object for the specific worksheet. On the Access side, you'll want a record set to hold and loop through your data, as well as some counter variables to keep track of rows and looping, plus a couple strings for file name and record source details.
Once the variables are set up, I define the record source – in this case, the Customer table – and update the status indicators appropriately. Although today we are working with the Customer table, this process will work with any table or query in your database.
With the basics out of the way, I establish the Excel application object using CreateObject, targeting Excel specifically. Setting the application's 'Visible' property to True lets you see Excel open and watch as your data is sent over, though you can set this to False if you prefer Excel to work in the background.
After creating the Excel application object, I add a new workbook to it, which starts with one default worksheet. I then reference this worksheet with the Sheet object. At this point, we have Excel started, a workbook created, and a worksheet selected. Later, we will be performing all our data transfers and formatting on this sheet.
Before moving further, I like to build the framework to properly clean up all the objects at the end of the process. This includes setting each Excel object back to 'Nothing' and, if you want to close Excel after the export, using the 'Quit' method on the Excel application. For demonstration purposes, I leave Excel open at the end so you can inspect the exported file.
After saving the project and compiling the code to ensure there are no errors, you can run the export. The result is that Access will open Excel, create a new workbook, and hand control back to you with everything set up and ready for the data import step.
In the next tutorial, we will actually bring in the data from Access and write it out into the Excel worksheet, then apply formatting like colors and alignment to your liking. So stay tuned for part two of this lesson, where we will cover populating and customizing your Excel export.
For a full step-by-step demonstration of everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Creating a new folder for Excel automation files
Copying and renaming an Access database file
Opening the database in Access
Identifying the customer table for export
Assigning VBA code to a form button
Declaring variables for Excel automation in VBA
Setting the Excel Application object using late binding
Setting Excel Application visibility from VBA
Creating a new Excel workbook with VBA
Referencing a specific worksheet in Excel via VBA
Assigning a record source for data export
Commenting on increasing code flexibility for different tables
Destroying and releasing Excel-related VBA objects
Running the code to create an empty Excel workbook from Access
Debug compiling VBA code for Excel automation |
