Excel Automation
By Richard Rost
2 years ago
Automate Excel: Import Access Data with VBA
In this Microsoft Excel tutorial, I will show you how to automate Excel using VBA to create a new sheet based on data from an Access database. We'll cover adding buttons, setting cell formats, adjusting column widths, and more, making it easy to pull and display Access data in Excel with a single click.
Prerequisites
Up Next
Keywords
TechHelp Excel, Excel VBA automation, importing Access data to Excel, create Excel sheet from Access, Excel macro button, Excel VBA tutorial, control Excel formatting with VBA, create new sheet in Excel, import customer data Excel, Excel VBA variable declaration, automate Excel using VBA, setup Excel Developer tab, Excel and Access integration, loop through records Excel VBA, format Excel columns VBA, save macro-enabled workbook, DAO.DBEngine VBA, connection to Access database Excel, Excel VBA record set, create Excel headers VBA, save Excel workbook with macros
Intro In this video, you'll learn how to automate Microsoft Excel using VBA to create a button that imports data from a Microsoft Access database and generates a new worksheet. I will walk you through connecting Excel to Access, declaring VBA variables, adding a button with form controls, setting up column headers based on your database fields, and discussing the important differences between working with VBA in Excel versus Access. You'll also see how to save your workbook as a macro-enabled file to support automation. Part 1.Transcript Today I'm going to teach you how to use Excel automation with VBA to construct a new sheet based on data from a Microsoft Access database. We're going to click a button, and it's going to go out and pull in the data from an Access database and create a sheet from it. Using automation, we can control things like background colors, foreground colors, cell widths, and all that kind of stuff.
So, when we're done, our Excel spreadsheet will have a button in it. You can click the button, and it's going to go out to Access, pull all that information in, and then we'll do things like change the column widths, set the background color, foreground color, all that kind of stuff. Left align the first column.
Now, last week, I showed you how to do this from Access with the Excel automation video and Access, where you can have your Access database create an Excel file that you can then send to a customer or an employee or someone who doesn't necessarily have Access but you want to give them the data in Excel, and you want to have it formatted and blah blah blah, right? Well, today's video basically goes the other way. This is going to be a spreadsheet you can set up or a workbook file you can give to your employees. You've got the Access database out on the network, and they might not be running Access, but this will give them a button they can click on that will then go out to your Access database and get the data that they need from the tables and create a new sheet for them. So it's basically the reverse of what we did last week.
So, if you haven't yet watched this video, I recommend you go watch it. It's a three-part series. It's all free. It's on my website and my YouTube channel. Go watch it. I'm assuming if you're watching this video, you're probably an Access user as well, hence the need to get data from Access into Excel.
I also strongly recommend you go watch this video if you've never done any programming in Excel VBA. In this video, I teach you how to turn on the Developer tab on your ribbon and all kinds of stuff. The basics that you need to get started with VBA and Excel. It's different from Access. There are a lot of differences between the two. So in this video, I explain those. Go watch both of those videos and come back when you're done.
Alright. So in the Access version of this, we create a folder called Excel Automation, and in here, I've got my TechHelp database. Then we created these spreadsheet files from the TechHelp database. We click a button, and it creates those sheets. Well, let's go the other way. Let's create an Excel workbook that will have a macro in it that will go to this TechHelp database and get what we need. So let's start up Excel. We'll open a blank workbook.
Let's go to our Developer tab. And again, I show how to turn this guy on in the other video. Basically, you've got to go to your ribbon and customize it. Let's go and add a button. We'll go to Insert, and then under Form Control, not ActiveX Controls, under Form Controls, pick the button, this guy right there, and then click and drag where you want the button to be. OK. We're going to give this guy a name. Let's call it Import Customer Data. Then we'll hit New. That'll open up the VBA editor. It's over here.
Very similar if you're used to working with Access. In here is where we're going to put all of our code to import information from our Access database. It's going to look a little bit different, but a lot of it's going to look the same. We're going to start off with some variable declarations. I already ran through this myself earlier just to make sure it worked because Access is something I can program off the top of my head, but Excel, I don't use Excel VBA all the time, so I like to run through it first before I walk you through it. So I'm just going to copy and paste from my notes.
Alright, here's our variable declarations. We need a DB engine object. We need a DB, which is our Access database. We need an RS, which is going to be our record set. Database file and record source are going to be for the file name for the database, and the record source will be like the table or query that we want. Then we need some other objects. We need a worksheet object in Excel. L and R are going to be for counter variables.
Now, the database file is going to be our tech help ACCDB file. Now in Access, you've got currentproject.path. Well, in Excel, it's a little bit different. It's thisworkbook.path. Same thing. That just means the folder that your database is in. Or the folder that your spreadsheet is in. We haven't saved the spreadsheet file yet. We'll get to that in a minute. But basically, wherever you got this guy, this is the file that contains the database information.
Now record source is going to be the table or the query that you want from that database. So I'm just going to pull all the records in from CustomerT. If you want different records, you can set up a query. You can limit the query based on the records. You can limit the fields that get pulled in. You might not want all the fields, like sensitive information. So you can control that by just creating a query. Now, if you want to bring in the data in the current active sheet, you can use setWS, that's our worksheet object, equals thisWorkbook.activeSheet.
Now one of my students, Gary Smith, asked if it's possible to have it create a new sheet. Yeah, we could definitely do that. So I'm just going to run this out. I'll leave that in there for people who want it. But here's the code that you would use if you want to add a new sheet at the end of the list of sheets in the workbook. We're going to dim another variable, sheetname as a string. I'm going to set the sheetname equal to the record source and then a space and then the date. For example, the record source is CustomerT, so the name of your sheet will be CustomerT and then the date, so you can see what date you imported. You can change it to whatever you want. You want to call it Rick's new sheet? Have at it, okay? If you import something every hour, change it, put the hour in there, whatever you want to do. They're your Legos.
Alright, and here's the command that actually adds the sheet. It's thisworkbook.sheets.add, and then it says after:=, that means set it after thisworkbook.sheets.thisworkbook.sheets.count. Excel's got weird nomenclature for stuff. You just have to look it up half the time yourself. Like I said, I find Access a lot more intuitive. I love Excel too. It's just different. And then this last line, we're setting the name equal to that. That's what you see down in the Sheet tab.
So Gary, I hope that answers your question. And I should probably be commenting this stuff as we're going here. Let's do connect setup. I'll declare database. So now we've got the sheet all set. Now we have to get the connection to the database. All right, connection to database. Here's what this looks like: setDBEngine equals createobject("DAO.DBEngine.120"). 120 is the version for Access 2010 and later. It's currently 2024, so I'm assuming all of you guys are using Access 2010 or later. If you're not, google this, and I'm sure you'll find there's another different number here that goes in there for older versions, like if you had Access 97. It's time to upgrade, folks. But likewise, in the future, if you're watching this 5-10 years in the future, they might have upgraded the database engine in that point you might just have to change this. But that's what it is as of today.
And then the rest will be familiar to Access users: setDB equal to DBEngine.opendatabase. Instead of the current database, because in Access we say current database, here we have a pointer to the DBEngine itself. Basically, DBEngine is a pointer to Access. Then you've got a pointer to the database file. Then you've got a pointer to the record source, which is your table. That's basically how that works.
Next, we set up the column headers. Here's our L value: for L equals 0 to RS.Fields.Count - 1. We went through this in the last video. There's a list of fields in the record source, and they have a count value, but it's zero-based. And then each cell, again, you've got row one and then cell L + 1 because this is one-based. Excel is one-based; Access tables and stuff are zero-based. So we're just going to loop from zero to the count of all of the fields and set the cell header equal to that name, the column header.
Here is where we're going to import the data. But before we do that, I want to test to make sure what we've got so far is working. Let's put our cleanup stuff down on the bottom here. I'm going to copy this. Let's see here. Where's my cleanup stuff? Right there is my cleanup. Let's leave some blanks in here. OK. Close the record set, close the database, set them equal to nothing, set dbEngine equals nothing, and then we'll just message box import complete since we don't have the status window. That should do it.
So this is going to create a new worksheet, open a connection to the database, create the column headers based on the fields in the table, and then close everything up. We'll import the data in the next step. Let's save this. Now navigate to the folder where you've got your TechHelp database in. Mine's on my desktop, so let me go find it.
Alright, so my desktop Excel automation folder. I'm going to call this my import from Access spreadsheet. But watch what happens when you hit save. You get a message saying, "Hey, you can't save it in a macro-free workbook." What does that mean? Well, xlsx means that this is a macro-free spreadsheet. It's the default, but you can't put macros in it. That's a Microsoft safety feature. So let's go back, cancel that. You have to change the save type to an xlsm, a macro-enabled workbook. This just means if you send it to someone else, they're going to get all kinds of warnings saying, "You got a macro, watch out." Alright, hit save.
OK, so that's good. We can close this. Let's rename that button. Right-click on it and go to format control. I'm sorry, it's under edit text. There's my Access kicking in. I'm so used to right-click, format properties, right? But different. Excel, yeah! Edit text and import customers. It's the little slight differences that get me.
Alright, ready? Click on it, and there we go. It did exactly what we wanted it to do. It went to a new sheet, but you should have edited that name, put in the names of the fields in the table, and gave us a message box. And there we go.
OK, now we have to import the data. We're going to loop through all the records with our record set. We're going to format stuff, set the alignments, resize the columns, and we'll do all of that in tomorrow's video, part two. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because I'm going to record it in just a few minutes, and we will cover all the rest of that stuff with our Excel spreadsheet lesson.
But 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 two.
TOPICS:
Using VBA for Excel automation
Connecting Excel to an Access database
Creating a button in Excel
Writing VBA code for the button
Declaring variables in VBA
Connecting to a database with VBA
Adding a new sheet in Excel via VBA
Setting up column headers in Excel
Handling Excel and Access differences
Saving a macro-enabled Excel workbook
COMMERCIAL:
In today's video from Excel Learning Zone, I will teach you how to use Excel automation with VBA to pull in data from a Microsoft Access database and create a new Excel sheet. You will learn to set up buttons, customize colors, adjust cell widths, and much more. This tutorial is the flip side of last week's lesson, where we did it all from Access. Perfect for those who need to give employees an easy way to pull data from Access into Excel with a single click. 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 the VBA automation taught in this video?
A. To export Excel data into an Access database
B. To import data from an Access database into Excel
C. To automate Excel calculations
D. To format existing Excel data
Q2. What type of controls should be used to create the button in Excel for this VBA automation?
A. ActiveX Controls
B. Form Controls
C. Macro Controls
D. Command Controls
Q3. What is the correct VBA code to create a new worksheet and set its name in Excel?
A. thisworkbook.sheets.add and sheets.count
B. thisworkbook.worksheets.create and worksheets.count
C. thisworkbook.sheets.newsheet and sheets.max
D. thisworkbook.sheets.create and sheets.total
Q4. Which VBA object in Excel represents the Access database file that the data will be imported from?
A. dbEngine
B. dbField
C. dbFile
D. dbWorkbook
Q5. What file extension should be used to save an Excel workbook that contains macros?
A. .xlsx
B. .xls
C. .xlsm
D. .csv
Q6. In the provided VBA automation, what does the code thisworkbook.path refer to?
A. The path of the Access database
B. The path where the Excel file will be saved
C. The path of the VBA project
D. The path to the Excel application
Q7. How are the column headers created in the new worksheet based on the fields from the Access table?
A. By looping through each record in the table
B. By directly copying the column names from Access
C. By setting each cell in the first row to the field name
D. By using a predefined template for column headers
Q8. Which method is used to create an SQL connection to the Access database in the video?
A. ADO.Connection
B. SQL.Connect
C. DAO.DBEngine
D. VBA.SqlConnection
Q9. What is done to ensure the correctness of the VBA code before importing the data?
A. The connection is tested with sample code
B. The VBA code is validated using Excel's built-in tools
C. The data is exported to a temporary file first
D. A message box confirms successful import completion
Q10. What action is performed right after setting up the database connection in the VBA code?
A. Sending a success notification to the user
B. Creating column headers in Excel based on the Access table fields
C. Formatting the cells in the new worksheet
D. Importing all records from the Access table
Answers: 1-B; 2-B; 3-A; 4-A; 5-C; 6-B; 7-C; 8-C; 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 focuses on using VBA automation in Excel to construct a worksheet based on data pulled directly from a Microsoft Access database. I will demonstrate how to set up a button in your Excel spreadsheet that, when pressed, will fetch information from an Access database and generate a new sheet, allowing you to control formatting options like background and foreground colors, cell widths, and column alignment.
In this approach, the completed Excel file gives users a simple button they can use to retrieve updated data from Access at any time. This process is particularly helpful for teams or employees who may not have Access installed but need to view or work with the data in Excel. It is essentially the reverse of last week's lesson, where I walked through exporting data from Access into Excel; here, we're starting from Excel and having it reach out to Access for the data.
If you have not yet watched the previous video on Access to Excel automation, I highly recommend seeing it first. It is a free, three-part series available on both my website and YouTube channel. It will help you better understand how the data transfer works in the other direction. Also, if you are new to programming with Excel VBA, I suggest watching my introductory video where I cover the basics, including how to enable the Developer tab, since VBA in Excel is quite different from Access VBA.
In the Access version, we worked with an "Excel Automation" folder containing the database and created spreadsheets by clicking a button within Access. Now, we will set up an Excel workbook with a macro that connects to the TechHelp Access database and retrieves the data we specify.
We begin by opening a blank Excel workbook, ensuring the Developer tab is visible on your ribbon. To insert a button, you use the Form Controls (not ActiveX Controls), assign it a name such as "Import Customer Data," and then create a new macro for it in the VBA editor.
Inside the VBA editor, you will declare variables needed for your database connection, as well as objects for your workbook and worksheet. The technique for referencing file paths is similar to Access but uses "ThisWorkbook.Path" in Excel. You designate the record source (such as a table or query) whose data you want to import. If you would like to bring the data into the currently active sheet, you can do that, but I also address a common question about automatically creating a new worksheet for each import. You can customize the names of these new sheets, for example, by combining the table name with the current date, so it is clear when the data was imported.
Adding a new worksheet is straightforward, though Excel's naming conventions can be quirky compared to Access. Once your worksheet is set, the next step is to establish a connection to your Access database. For Access 2010 and later, you use a specific version string for the database engine. If you're using an older version (though it's probably time to update if that's the case), a different version string would be necessary.
With the connection established, you create the column headers dynamically based on the fields in your chosen table or query. Due to differences between Excel and Access, you need to keep in mind that Excel uses one-based numbering for cells, while Access is zero-based for fields.
Before importing the actual data, it is a good idea to test whether your connection, worksheet creation, and header setup work as expected. You also want to make sure to properly close your recordset and database objects at the end of the process to avoid locking files or creating memory issues.
A key point is that your macro will only run in a macro-enabled workbook, which you must save with the ".xlsm" extension. If you try to save it as a regular ".xlsx" file, Excel will block your macros for security reasons. Make sure to rename your button and give it a descriptive label so users know what it does.
When you run your macro by pressing the button, it should create a new worksheet, populate the headers, and confirm with a message that the import is complete. In the next lesson, we will handle the actual data import, formatting, column resizing, and other finishing touches.
That wraps up today's TechHelp video. 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 Using VBA for Excel automation
Connecting Excel to an Access database
Creating a button in Excel
Writing VBA code for the button
Declaring variables in VBA
Connecting to a database with VBA
Adding a new sheet in Excel via VBA
Naming new worksheets dynamically
Setting up column headers in Excel
Referencing fields in a recordset
Handling zero-based vs one-based indexing
Cleaning up objects and closing connections
Saving a macro-enabled Excel workbook
Renaming form control buttons in Excel
Using ThisWorkbook.Path vs CurrentProject.Path |
