Excel Automation 3
By Richard Rost
2 years ago
Construct an Excel Sheet Using VBA Automation, Part 3 In this Microsoft Access tutorial, I will show you how to automate exporting data from Access to an Excel sheet with VBA. We will cover saving the file, suppressing warning messages, applying formatting to the Excel sheet, and more. This is part 3. MembersThere is no extended cut, but here is the database download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesRecommended Courses
Keywords TechHelp Access, VBA Excel automation, Access VBA export Excel, Excel sheet formatting VBA, VBA create new Excel file, Excel VBA save workbook, Excel VBA set font color, Excel VBA set background color, Excel VBA align column, VBA automate Excel, Export Access data to Excel, Late binding in VBA, Excel VBA horizontal alignment
Intro In this video, I will show you how to automate exporting data from Microsoft Access to Microsoft Excel, add custom formatting to your Excel sheet, and save the workbook automatically without prompts. We will cover setting the file path with VBA, turning off Excel warning messages, formatting the header row with font and background colors using the RGB function, and left-aligning the first column. You will also learn how to use late binding for Excel automation and how to quickly change your export source and re-run the process. This is part 3.Transcript This is part 3 of my Excel automation series. Part 3 of 3 as of right now. And I'm showing you how to take data from Access and get it over into Excel. We've got our data in there right now in a new sheet. Now we're going to add some formatting, do some cool stuff, and save the file. All right, here we go.
Okay, so we've got it exporting, we've got it creating a new file, it's doing all this stuff, it's pretty cool. We have to save the file. It would be nice to save it to our drive and add some formatting like I mentioned before. So how do we do all that? Well, let's do this and come back to our code.
So after all of this is done, after we're done exporting, let's save the workbook. See, I always call it a sheet. Save the workbook. Okay. I'm going to save it without prompting, which means it's going to overwrite it if it exists because this is generally something that's like a temp file. You're going to keep exporting it, exporting it, exporting.
If you want to change the file name, you can. That's up to you. But we already reserved a variable for file name, which is right there. That's what this is going to be. Equals current project dot path. That takes the folder that your database is in, and that's what that is. So if it's in C: backslash my database, that's what current project dot path is.
We're going to add on to that a backslash, and then we're going to add record source. So whatever this is, if it's customer T, if it's customer Q, whatever it happens to be, that goes in the file name and .xlsx, that's the name of the extension of an Excel file.
Okay, now the command to save is Excel workbook.save as and then the file name and that'll work just as it is. But I also want to turn off the warning messages. It's like, "We're set warnings." Well, in Excel, it's ExcelApp.DisplayAlerts equals false. And then the same thing after that, turn them back on. It's like set warnings and Access. Don't leave them off unless you want to leave them off, then you can leave them off. But that will save the file without prompting, okay? And without warning it. Save it.
Let's give it a shot, let's try it out. And hit the button. And it's gonna do its thing. And then, you should see, yep, there it goes right there. Customer T.xlsx. And if I close this, it's not prompting me anymore. And if I go to my folder, where's the folder?
There it is. Oh, there we go, there's our file. Beautiful. And yes, you can use automation to then automatically email this to someone too. That's a whole separate video. But I do have videos on it.
Now, the whole point of doing all this in the first place was so we could add some formatting and do some other stuff aside from just dumping the data. So let's come here right after we add the column headers and we're going to say format the header row. We're going to say Excel sheet.rows(1). Remember, the rows are one-based. The loops in Access are zero-based. It's confusing, I know. This is the stuff you spend hours on.
With that row, Excel sheet.rows(1).font.color equals whatever color you want using RGB, so the RGB function, right, it's red, green, blue, 0 to 255. Let's make it white. 255, 255, 255. All right, and I have to put after that white. OK, now let's set the background color.
And again, you'd think that they could stay consistent between the different Office apps, but they're not. And in Access, it's back color. Here it's interior color. And what do we want for that? Let's go with a dark blue. So I'm gonna go zero, and then maybe, I don't know, 50. All right, and this will be, let's do blue.
Okay, save it, and let's see what it looks like. You ready? Click. And ooh, look at that. Snazzy. All right, let's left-align the first column where the ID is. And that's pretty much safe. You can pretty much always do that with pretty much every row, right, if it's an ID.
Or we can even check to see if the name of that column is ID. I mean, you can do all kinds of stuff here. Let's just left-align it. So right down here after we write out all that data, right here, left-align column 1.
Excel sheet.columns(1).horizontalalignment equals minus 4131. Where did I get that number from? Well, that's the value that represents Excel left. I'll explain in just a second. Remember, we're doing late binding, so we can't use any of these fancy variables, okay? If you do set up a reference under tools references to the Excel library, right, the Microsoft Excel 16 object library, you can look through all the constants and the variables and stuff like that.
I find it easier just to Google for it. Like if you know you want to use horizontal alignment, it will give you those numbers. Or even ask ChatGPT for them. It's really easy to get. I just told ChatGPT I'm using VBA with Access and Excel and late binding. Can you give me the available settings for the columns.horizontal alignment property and all their numeric constants? And there they are.
This is one of the things that ChatGPT is really good for, taking stuff that you know how to do, kind of, but just give me the details that I need. So as you can see, left is negative 41, 31. There's general, which is the default. Center, right, fill, justify, center across selection, and distribute. And there they are. So if you want right align, just take that value. Keep these on hand. That's the easiest way to get these instead of digging through stuff.
Now, the nice thing about this code we wrote, like I said before, let's not save it. I made some changes, so let's not save that. We can very easily change what we're exporting. Let's say we want to export, what do we got? Customer order queue, what's that? Customers with all their orders. How about just the customer LFQ, the customers with their last name, first name. Let's do that one.
So we'll just come over here. We'll switch this to Customer LFQ, save it, and then export that. And there you go. See? All nice and simple and quick and fast. We should have a Customer LFQ file now sitting there. Let's come back out and check our folder. And there it is. See? Now you just send this away.
So that pretty much is that. Now, if you want to learn more about this Excel automation stuff, if you're enjoying this, if you're having fun, I need you right now to post a comment down below, whether you're on YouTube or on my website, and let me know you want to learn more about Excel automation.
I do have some lessons planned for my Access Developer students. If you want to see more videos on this, let me know. Squeaky wheel gets the grease. I do have more lessons available on Word automation, where I show you basically how to do the same thing with Microsoft Word, creating documents in Word.
That's in Access Developer 39. And also in my document index video, I think it's pretty much the same thing. I cover a little bit more of it in the developer lesson. And of course, in my Access email seminar, I teach Outlook automation, with the classic Outlook, not the new web-based version, the old school Outlook, right, that came with Office, comes with Office, the desktop version where you can then take this Excel document, right, and then we can send that as an attachment through Outlook using automation as well.
So that's covered in my email seminar. Yeah, this is an older picture. I have to update that still.
So that's going to do it for now for part three. Like I said, if you want to learn more about this stuff, post a comment down below. I have something different planned already for tomorrow for Wednesday's video, but on Thursday I'm gonna show you how to go the other direction. We're gonna start an Excel - it'll be an Excel Learning Zone video, right - but it's gonna have some Access to it. We're gonna start with an Excel spreadsheet, and then we're gonna click a button and have it pull down the information from Access.
So I'm in Excel now, and my user can then click the Import Customers button, and it does the same thing in reverse. We're going to have Excel go out to Access and pull in the information that way. And, of course, our button slides over here. That's fine, that's okay, you can deal with that.
Now, they're gonna have to obviously have the Access database on their network, but let's say you have users who really don't care to have Access, you don't wanna bother making them a front end, they just wanna open up an Excel sheet, hit a button and pull in the data from your Access database.
Uh-huh, yeah, I'm gonna show you how to do that on Thursday the 30th of May, 2024. And if you're watching this in the future, if you've come to us from some future land, like the year 2027, well, it's already going to be on my website and my YouTube channel, but for the rest of us, you've got to wait a few days. Or sign up and become a member and you can watch it right now.
But, that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Exporting data from Access to Excel
Saving an Excel workbook without prompting
Setting Excel workbook file path dynamically
Turning off Excel DisplayAlerts
Formatting Excel header row
Setting font color in Excel with RGB function
Setting background color in Excel header row
Left-aligning first column in Excel
Using horizontal alignment properties in Excel
Using late binding for Excel automation in VBA
Modifying and re-running export process
Dynamic switching of data sources for export
COMMERCIAL:
In today's video from Access Learning Zone, I’ll teach you how to take data from Access and put it into Excel, add some custom formatting, and save the file with no prompts. First, we set up the file name and export path. Then, turn off warning messages using ExcelApp.DisplayAlerts, and save the workbook. After that, we’ll add some styling to your header rows with colors and alignment. Finally, you’ll see how to adjust your exports on the fly. 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 command used to save the Excel workbook without prompting the user?
A. ExcelWorkbook.SaveAs
B. ExcelFile.Save
C. ExcelApp.SaveWorkbook
D. Workbook.SaveAsFile
Q2. Which function is used in the code to set the font color in the header row?
A. ExcelSheet.FontColor
B. ExcelSheet.Rows(1).Font.Color
C. ExcelSheet.Header.FontColor
D. ExcelSheet.Rows.Font.Color
Q3. What RGB value is used in the tutorial to set the font color to white?
A. 255, 0, 0
B. 255, 255, 255
C. 0, 0, 0
D. 0, 255, 255
Q4. Which Excel object property is used to set the background color of the header row?
A. ExcelSheet.Rows(1).BackgroundColor
B. ExcelSheet.Rows.InteriorColor
C. ExcelSheet.Rows(1).BackColor
D. ExcelSheet.Rows(1).Interior.Color
Q5. To left-align the first column, which numerical constant is used for the horizontal alignment property?
A. -4108
B. -4131
C. 0
D. 1
Q6. What is the purpose of setting ExcelApp.DisplayAlerts to false?
A. To prevent the user from closing the workbook
B. To disable all alerts permanently
C. To save the file without any warning messages or prompts
D. To ensure data integrity
Q7. Why is it recommended to use VBA late binding in some scenarios?
A. It makes code execution faster
B. It avoids issues with different version libraries
C. It allows direct referencing of Excel constants
D. It simplifies the coding process
Q8. After changing the record source to "Customer LFQ", what must be done to export the new data correctly?
A. Reopen the Access database
B. Modify the Excel sheet manually
C. Change the code to refer to "Customer LFQ"
D. Restart the computer
Q9. What does currentproject.path return in the VBA code?
A. The full path of the current project
B. The name of the current Access database
C. The path to Excel library
D. The path where the temporary Excel file is stored
Q10. What is the suggested method to find the numeric constants for properties like Horizontal Alignment when using late binding?
A. Use trial and error
B. Look them up in the Excel documentation
C. Ask ChatGPT or Google for them
D. Guess the values
Answers: 1-A; 2-B; 3-B; 4-D; 5-B; 6-C; 7-B; 8-C; 9-A; 10-C
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 the process of exporting data from Microsoft Access to Excel, enhancing the exported data with formatting, and ensuring the file is saved without any user prompts.
At this stage, we already have our data exporting from Access and being placed into a new Excel sheet. The next steps involve saving the file to our drive and applying some formatting to make the output look more professional and useful. Once the workbook has been populated with data, we need to save it. My approach is to save the file automatically, overwriting any existing file with the same name. This is ideal for scenarios where you are repeatedly exporting temporary files and do not want to manually confirm overwriting each time.
To set the file path and name, I use the path where the Access database resides, appending the record source name and the ".xlsx" extension to it. For example, if your database is located at "C:\\my database" and you are exporting a table called "CustomerT," the resulting file name would be "C:\\my database\\CustomerT.xlsx." This way, the exported file is always easy to locate and identify.
In order to save the file in Excel without interruption, Excel's display alerts are disabled. Similar to Access's SetWarnings, setting ExcelApp.DisplayAlerts to false ensures that the save operation completes silently, without any prompts to the user. Once the file is saved, you can optionally turn alerts back on, depending on your needs.
After confirming that the file is saved correctly in the target folder, you could even build on this process by automating email delivery of the exported file, though that topic is covered in other lessons.
The initial reason for exporting data this way wasn't just to move the data, but also to allow for custom formatting. Right after the column headers are generated, we can apply formatting to the header row. In Excel VBA, rows are one-based, so the first row is row 1. For example, you can change the font color of the header row to white using the RGB function (with arguments 255, 255, 255). Then, for the background color, set the interior color property. In Excel, it is called "interior color," unlike in Access where it's "back color." For a dark blue header, you might set the RGB values to something like 0, 0, 50.
Once the formatting is applied and saved, you can verify the results by running your export process and checking the newly created Excel file in your folder. The headers should now appear with the new colors. Additionally, it can be useful to left-align the first column, especially when it holds IDs or similar data. While you could add logic to check the column name, typically you can just apply left alignment to column 1 for simplicity.
It's worth mentioning that when working with Excel automation in late binding mode, you cannot use the built-in named constants from the Excel library. Instead, you need to use the numeric values for settings like horizontal alignment. These constants are easy to find online or by asking tools like ChatGPT to provide them. For left alignment, the corresponding value is -4131. You can find similar values for center, right, justify, and other alignments, and keeping these numbers handy will save you time during development.
The flexibility of this export routine means you can change the record source at will. If you have another query, such as a customer list with first and last names, you can just update the record source and rerun the export to produce a new file. The exported file will reflect the new data source, all with the formatting applied.
If you would like to learn more about Excel automation from Access, including advanced techniques and other Office application integrations, let me know in the comments on my website or on YouTube. I have lessons planned that cover tasks like automating Word document creation (found in my Access Developer 39 series), as well as Outlook email automation, which shows how to attach the files generated in Excel and send them directly via email.
Today's lesson concludes the three-part series on exporting from Access to Excel as of now. In an upcoming lesson, I will be covering the reverse process, demonstrating how to start from Excel and import data from Access by clicking a button in Excel. This will be helpful for situations where your users prefer working solely in Excel and want to pull data from a shared Access backend without launching Access itself. This lesson will be available soon on my website and YouTube channel, or immediately for members.
For more information and to follow each step exactly as shown in this article, you can watch the complete video tutorial at my website using the link below.
Live long and prosper, my friends.Topic List Exporting data from Access to Excel
Saving the Excel workbook without prompting
Setting the Excel file path dynamically using VBA
Turning off Excel DisplayAlerts to suppress prompts
Formatting the Excel header row
Setting header row font color with the RGB function
Setting background color for the header row
Left-aligning the first column in Excel
Applying horizontal alignment using Excel constants
Using late binding for Excel automation in VBA
Modifying export code to change data sources
Switching exported tables or queries dynamically |
