Automated Export
By Richard Rost
3 years ago
Automated Export of Data from Access to Excel
In this Microsoft Access tutorial, I'm going to teach you how to automate the export of data from Access to Excel with a little VBA and just one click. We're going to discuss the TransferSpreadsheet and OutputTo commands.
Members
There is no Extended Cut, but here's the database for the Gold members.
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!
Prerequisites
Links
Recommended Courses
Code
DoCmd.OutputTo acOutputForm, "FormNameF", acFormatXLSX, "C:\Folder\File.xlsx", True
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, automated export from Access to excel, automate exporting to excel, TransferSpreadsheet, docmd.OutputTo, acExport, acSpreadsheetTypeExcel12Xml, corrupt XLSX file, acOutputForm, acFormatXLSX
Intro In this video, we will learn how to automate exporting data from a continuous form in Microsoft Access to Microsoft Excel with the click of a button. I will show you step by step how to set up a button that exports your filtered and formatted form data directly into an Excel spreadsheet using VBA, preserving your formatting where possible. We'll also compare the DoCmd.OutputTo and TransferSpreadsheet commands, discuss best practices for file formats, and cover simple error handling to manage issues like open files.Transcript Today we're going to see how to do an automated export from Access to Microsoft Excel. You can also use the same technique for Word, PDF, and a bunch of other formats, but we're going to focus on Excel today. We're going to talk about a command you should not use to transfer data to Excel.
If you did not watch yesterday's beginner video, go watch that first. I show you how to export data from a continuous form manually to Excel. Why use a continuous form? Because you can keep all the formatting, colors, and all that good stuff if you use a form. Don't use a table. Don't use a query. Use a form. You can also use a report, but I think forms work better.
In today's video, I'm going to show you how to automate that process with one click. We're going to make a button to export to Excel. First, the user can do whatever filtering they want on the form – they can sort it, filter by state, whatever. Once they're done, then they make their click. That's the one click I'm talking about. Click the Export to Excel button, and it will do the export in the background. It'll pop up the spreadsheet and you're done.
First, let's talk about what not to use. There's a command called TransferSpreadsheet. It's okay, but it's very basic. I don't like it. You can export a table or a query with no formatting. It's not as good as the one I'm going to show you. We'll talk about TransferSpreadsheet a little bit toward the end of class.
What are we going to use? We're going to use DoCmd.OutputTo, which allows you to export all kinds of different stuff and all kinds of different formats. I'm going to show you how to do it with formatted data from a form into an Excel spreadsheet. You can even put card approves. This one is going to be a developer video, so we're going to use some VBA.
If you've never done any VBA programming before, go watch my introductory video. It will teach you everything you need to know to get started in about 20 minutes. We need one line of code to do what we need to do.
All right, so here I am back in the TechHelp free template. This is a free database you can download from my website. We're going to use the Customer List continuous form because you can come in here and set your colors and your formats just like we did in yesterday's video.
You can go into design view and set the format equal to whatever you want. Unfortunately, conditional formatting does not come through. You have to use just regular formatting, one field at a time. If you want conditional formatting, you'll have to put that in after you export it to Excel. You can do it with VBA automation. That's going to be the focus of one of my developer lessons coming up soon. It is possible. Pretty much anything is possible in Access if you want to spend enough time doing it.
Let's make a button in design view. I'm just going to copy this button over here, copy and paste it, and slide it over there. On the button face, put "Export to Excel." Let's bring up the properties. We're going to make this the Export button. Right-click, build event.
Your command is going to be DoCmd.OutputTo. What do we want to output? Object type is going to be acOutputForm. Yes, you can output pretty much all these other things in here, so feel free to experiment with them if you want. In fact, I have videos where I show you how to output reports, and I show you all kinds of different stuff. I'll put some links to some different videos at the end. For now, let's stick to acOutputForm – we're outputting that form.
What is the object name? What's the form name you want to output? You could just say "Me.Name" here if you want to. That way, you can just use this for whatever form you happen to be on, or put the actual name of the form here, which in this case is CustomerListF.
What format do you want? You don't get IntelliSense, which gives you a list of formats. If you happen to Google it, if you Google the output format, or if you're using Bing or whatever else, you may come across this page, which is the old OutputTo method. If you scroll down, it gives you a list of these output object types, and here are all the acFormats that you can select.
You might be thinking, "Oh, acFormatXLS, that's what I want," and you might decide to come over here and paste that in. In 2003, this would have worked just fine. But it's not correct, and it's not going to work. You have to make it acFormatXLSX. That's the new file format for Excel after 2007. I only mention this because lots of people ask about it too, because Microsoft still has an old page that shows up pretty high in Google searches.
In fact, I just searched for "MSXS OutputTo output format." Here's the OutputTo method. It's the number one Google search. If you scroll down a bit, you might find some other stuff from 2012, 2014, and so on. Down here, DevHut. I love DevHut – it's one of my favorite sites. He's got it right here: XLSX, 2007+ format. You can always count on Daniel to come up with stuff before most other places do. That's what you want to use right there.
Alternatively, you can use his updated list down there. The reason why I mention this is because Microsoft's page comes up first and it doesn't have updated information. Be careful. A lot of people fall into this trap. Just remember, the number one Google result isn't always the best one – unless it's me or Daniel or Michael or a few other sites. In fact, my goal is to try to get the number one video search for anything YouTube related for Access. If you search for something Access related and my video doesn't show up, I'll be working on that.
So make sure you use the updated acFormatXLSX. Comma, what's your output file? Where do you want it to go? Give it a file name. I'm going to drop it on my desktop as XL.XLSX or whatever. And don't ask about my username; it's a long story. I think I told it in one of the previous videos.
Comma, AutoStart. Do you want Excel to automatically start and load this up once the export is complete? If you want to have the one click and not have to have the user go hunt for it, then yes. If you're going to be doing something like exporting it and then emailing it out, you might not necessarily want to automatically open it up – you want to export it in the background, drop it into an email, and go. In this particular case, since I promised a one click and there's your sheet, that's what we're going to do.
There are some other options here: Template File (that's mostly for HTML if you're exporting as HTML), Encoding (don't worry about it), Output Quality (that's for if you're exporting to PDF, you can control the quality). You don't need those other options for Excel outputs.
That's pretty much it. Unlike the manual export, this will also overwrite without asking. If this file exists, it's going to overwrite it without prompting you, which is a good thing, I think. If you're doing an automated solution, you don't want it to bother you all the time saying, "Hey, this file already exists. Do you want to overwrite it?" The one thing that will happen, though, is if you already have this file and it's open—like let's say you export it, it opens, and you try to do the export again—if Excel is open, it will generate an error. You might want to put some error handling in here, like at least an On Error Resume Next or something.
I've got separate whole videos on debugging and error handling, and I'll put a link to some of that stuff down below.
So that's good. Give it a quick Debug > Compile. Come back over here, close it, save changes.
I'm going to come in here, say "Give me everybody from Texas." Oh, there's only one person. That's fine. Export to Excel. Click. And that's one click. There we go. There's our one person from Texas. I reverted the database to the previous edition, so the formatting that I put in there in the last video is gone. That's why it's coming up like that, but if you put short date in there, it'll work.
If you try to hit this button again, it'll say it can't do it because it's open already. So, what you can do is add a quick little error trap in here:
On Error GoTo Error
Do this, then Exit Sub.
Error:
MessageBox "Error outputting: Excel may be open. Close it first."
Resume Next
Always resume error traps like that, then Exit Sub. If the error doesn't generate, it exits out; otherwise, it displays the message and exits.
Save it, better than popping up those error messages. "Error outputting: Excel may be open. Close it first." Close Excel and try again.
Come over here, close your Excel file, turn your filter off, maybe sort by first name, and now just view people from Florida. Now, Export, and boom—there you go. It's beautiful.
That's it. That's your one-click export, one line of code. Not hard. See how cool this export—or this VBA stuff—is? For those of you who don't know much VBA, it's easy to do; you just have to learn the commands. That's all.
So, as you've seen, if you want to export data to Excel and have it keep your formatting so it will look pretty, use a form and use OutputTo.
Now, let's talk briefly about TransferSpreadsheet. If all you care about is exporting the data, maybe you're sending it to someone so they can import it into their database, and you really don't care about any of the formatting, TransferSpreadsheet is okay. You can use it to export a table or query.
Let me show you real quick. View and I'll just repurpose this button here. It's DoCmd.TransferSpreadsheet. There it is. The first option is acExport. You can do an import too. We're going to show you an import in the next video – today is Friday, so we're going to show it to you in Monday's video. For today, we're going to stick with this.
There are a whole bunch of different formats here. If you want to use Excel formats from a bygone day—which we don't—you want to use the latest version (it's currently 2023), so use the Excel 12 XML file format right there. Otherwise, you might get an error message when you try to open it.
Next is the table name. This can be a table or query. If you want to filter or sort the data a specific way, make a query first and export that query. I'm just going to stick to CustomerT.
File name: I'll stick with the same file name I had before. Paste that in.
Has Field Names: Do you want to export the field names into your Excel spreadsheet (the header row)? I'll say true. There are a couple of other options, the range also, but you don't need the other stuff. That's all you need here.
Save it. Give it a quick compile. Close it and reopen it. I forgot to put a beep or a status update in there—add a beep or something so you know when it's done.
Make sure that file is okay and that the other one is deleted. Hit your button, and it's done. Go look at your desktop. There it is. Double-click and open it up. It's simple – no frills, just basic data.
I get emails at least once a month from someone saying they're trying to do it and it's exporting a corrupted Excel file. They try to open it and Excel says, "I don't know what you have here; I can't open it." So personally, I think TransferSpreadsheet is unreliable sometimes. That's why I generally tend to stick to OutputTo, but if this works for you, great, use it.
If you want to learn more, I do have some other videos using OutputTo, showing you how to take a report and output it to Word, output to PDF, and so on. This video will show you how to export directly to a PDF. If you want to learn more about export/import stuff, I cover the OutputTo command in several different videos: Developer 17, 21, and a bunch of others. If you go to my website, search for "OutputTo" and you'll find a list of all the lessons where I cover it. There are a lot of them because I show you different things in different lessons – in one, we're exporting a PDF to make an email attachment, in another, we're exporting a Word document for mail merge purposes. Access is so versatile—it can do all kinds of different stuff. I don't have one definitive lesson that covers everything you can do with OutputTo. That's what these TechHelp videos are for: to show a bit of this and a bit of that. I cover OutputTo in my developer lessons in a bunch of different places.
That's going to do it. In Monday's video (today is Friday, September 8, 2023), we're going to cover importing stuff automatically. We're going to export a spreadsheet like we did today, then get it back from our user and import their changes. That will be on Monday.
That's going to be Monday's video. I hope you learned something today. Live long and prosper. I'll see you next time.Quiz Q1. What is the main advantage of using a continuous form as the data source for an Excel export in Access?
A. It preserves formatting, such as colors and layouts, in the exported data
B. It exports data faster than tables or queries
C. It exports all related tables automatically
D. It prevents data from being edited during export
Q2. When automating an export to Excel in Access, which VBA command is recommended in this video?
A. DoCmd.TransferText
B. DoCmd.TransferSpreadsheet
C. DoCmd.OutputTo
D. DoCmd.RunExport
Q3. Why is the TransferSpreadsheet command NOT recommended for exporting with formatting?
A. It is only available in older versions of Access
B. It supports conditional formatting but not regular formatting
C. It does not export formatting, only raw data
D. It cannot export to Excel format at all
Q4. Which of the following output object types should be used to export a form to Excel using OutputTo?
A. acOutputTable
B. acOutputReport
C. acOutputQuery
D. acOutputForm
Q5. What file format string should be used with DoCmd.OutputTo when exporting to modern versions of Excel?
A. acFormatXLS
B. acFormatCSV
C. acFormatXLSX
D. acFormatXLSM
Q6. What happens if you try to export to a file that is currently open in Excel?
A. Access allows the export and overwrites the file
B. Access creates a duplicate file with a new name
C. Access generates an error and the export fails
D. Access exports only data that is not open in Excel
Q7. What can you use as the object name parameter when calling DoCmd.OutputTo from a form?
A. Only the exact string name of the form
B. Only "Me.Name"
C. Either "Me.Name" or the string name of the form
D. Only the filename you want to export to
Q8. How does the automated export handle an existing Excel file with the same output name?
A. It prompts the user for confirmation before overwriting
B. It overwrites the file without asking
C. It appends the new data to the existing file
D. It cancels the export automatically
Q9. If you want Excel to automatically open the exported file after export, what should you set in the OutputTo command?
A. AutoStart to False
B. Output Quality to Low
C. AutoStart to True
D. Encoding to UTF-8
Q10. Why might TransferSpreadsheet occasionally produce a corrupted Excel file, according to the video?
A. It lacks support for modern Excel formats if not configured correctly
B. It always uses the oldest Excel format
C. It tries to add formatting and fails
D. It only works with reports, not forms or tables
Q11. Which limitation is mentioned regarding exporting conditional formatting from Access forms to Excel?
A. Conditional formatting is always preserved
B. Only numeric fields can retain conditional formatting
C. Conditional formatting does not export and must be redone in Excel
D. Exporting conditional formatting causes Access to crash
Q12. What is suggested as a way to handle errors when exporting if the Excel file is already open?
A. Add an error trap with On Error GoTo and a user-friendly message
B. Restart the computer automatically
C. Delete the open file from Excel
D. Switch to TransferSpreadsheet instead
Q13. What is a key benefit of using DoCmd.OutputTo for automated export compared to a manual export?
A. It allows batch exporting of all forms
B. It overwrites files without prompting and requires only one line of code
C. It does not support file overwriting
D. It asks for user confirmation before export
Q14. What info should you be cautious of when searching for OutputTo file format options online?
A. Microsofts documentation always lists the latest file formats
B. Google search results may show outdated information for file formats
C. You can find all formats by trial and error in Access
D. Any blog post found online is the most accurate source
Q15. Which of the following is TRUE regarding the TransferSpreadsheet command?
A. It exports the underlying data with formatting
B. It can export tables or queries but not forms with formatting
C. It requires a report as the data source
D. It is the preferred method for exporting formatted data
Q16. When would TransferSpreadsheet be acceptable to use, based on the video?
A. When you need to export both data and formatting
B. When exporting raw data for someone to import into a database and formatting does not matter
C. When creating large PDF files from Access
D. When exporting data with conditional formatting
Answers: 1-A; 2-C; 3-C; 4-D; 5-C; 6-C; 7-C; 8-B; 9-C; 10-A; 11-C; 12-A; 13-B; 14-B; 15-B; 16-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 TechHelp tutorial from Access Learning Zone will focus on automating the process of exporting data from Microsoft Access to Excel. While this technique can be adapted for other formats such as Word or PDF, the primary focus here will be Excel. In addition to showing you what methods to use, I'll discuss which commands should be avoided for this task.
If you are new to exporting data and have not yet watched the introductory lesson on manually exporting from a continuous form to Excel, I recommend starting there. The primary advantage of using a continuous form for exporting is that it preserves your formatting, color choices, and other design selections. Using tables or queries for export will not maintain these visual styles. Although reports can be used as well, I generally prefer forms for this purpose.
Today, I'll guide you through automating the export process so users can filter or sort their data in a form, then with a single click, instantly export those filtered results to Excel. The exported spreadsheet will open automatically, all in just one seamless action.
Before demonstrating the preferred approach, let's talk about a method you should avoid. There is a command called TransferSpreadsheet. While it can export tables or queries, it only provides basic data with no formatting. For more advanced exports where maintaining presentation matters, it is not ideal. Toward the end of this article, I'll discuss its limited use cases.
The recommended way to export formatted data is by using the DoCmd.OutputTo command. This method grants greater flexibility, allowing exports of various Access objects (like forms or reports) into several formats, including Excel. You can even set it up for automation using VBA.
For anyone who is not yet familiar with VBA programming, I have a separate video that will help you get started quickly. For this specific export task, a single line of VBA code is sufficient.
I'll demonstrate everything using the TechHelp free template, which you can download from my website if you want to follow along. The example will use the Customer List continuous form. This layout allows you to set all formatting and colors as shown in the earlier manual export lesson. Note, however, that conditional formatting does not transfer through the export. Only fundamental formatting settings—applied one field at a time—will be preserved. If you need conditional formatting in your Excel output, you will have to apply it after the export, possibly with additional VBA automation in Excel, which I cover in a separate developer lesson.
To create the export function, go into design view and add (or copy-paste) a button labeled "Export to Excel." Set up its properties and connect it to a VBA event handler.
Within the VBA code for the button, use DoCmd.OutputTo. First, specify the object type. In this case, set it to export a form, but know that OutputTo works with other Access objects as well, so feel free to explore those on your own. You'll also provide the name of the form you want exported. To keep the code flexible, you could use Me.Name, or specify the form name directly, such as CustomerListF.
Next, define the export format. This step is important because Access does not provide automatic suggestions for format strings (no IntelliSense here), and older documentation can be misleading. If you search online, you may encounter the acFormatXLS value, but that only works for older Excel formats (prior to Excel 2007). For modern use, always select acFormatXLSX, which is compatible with the current Excel file type. Be sure to avoid outdated advice and check reliable sources for updated values.
Once the format is set, decide where to save the file. You'll enter the file path and filename for your export, such as XL.XLSX on your desktop. As an option, you can enable AutoStart, so Excel automatically opens the file after export—ideal for the one-click experience. Otherwise, you might want to run the export silently in the background if you're going to email the file or process it further.
Other settings in OutputTo, like template files (for HTML), encoding, or output quality (for PDF export), are not necessary for Excel exports.
One useful feature of this automated approach is that it overwrites existing files without prompting. This makes the process more streamlined for repeated use. However, beware of attempting to overwrite an Excel file if it is already open; in that case, Access will trigger an error. To handle this, you can include basic error handling in your VBA code, perhaps with an error trap that notifies the user to close Excel before trying again.
Compile and test your code in Debug mode to ensure it runs smoothly. You can now use your form, set filters or sorts as you wish, and export the results to Excel with a single click. Any formatting you applied (except for conditional rules) should be reflected in the Excel output.
If an error occurs during export because the file is already open, your error trap will catch it, prompting the user to close Excel before trying again. Filtering and sorting work as expected—for example, you can filter for customers from Texas or Florida and only export those results.
To sum up, if you want your exported Excel files to retain formatting, use a continuous form with DoCmd.OutputTo. This approach makes the exports both presentable and convenient.
As promised, I'll briefly mention TransferSpreadsheet. If your only concern is quickly exporting simple data (such as providing a table to another person for database import), this method may be sufficient. However, it outputs only the raw data with no formatting. When using TransferSpreadsheet, you must specify the export type, typically acExport, select the file format (preferably the updated Excel XML format), and point to the table or query you want to export. You can also choose whether to include field names as headers.
In my experience, TransferSpreadsheet sometimes leads to corrupted Excel files, especially with newer versions of Access and Excel. Many users have shared similar issues, so I lean toward OutputTo for reliability and flexibility.
If you're interested in exporting to other formats, such as PDF or Word, I have separate videos covering OutputTo with those objects as well. Search for "OutputTo" on my website to find individual lessons for various export scenarios, including using reports, batch emailing PDFs, or generating documents for mail merges.
That covers today's lesson. In the next tutorial, which is scheduled for Monday, I'll cover how to import data from Excel back into Access automatically, completing the export-import cycle.
If you would like a full video tutorial with step-by-step instructions on everything discussed here, please visit my website at the link below.
Live long and prosper, my friends.Topic List Automating export from Access form to Excel
Creating an Export to Excel button on a form
Using DoCmd.OutputTo for export in VBA
Exporting filtered and sorted data from a continuous form
Preserving formatting when exporting from forms
Choosing the correct acFormatXLSX output format
Setting output file path and AutoStart options
Error handling when exporting to Excel if file is open
Comparison between OutputTo and TransferSpreadsheet
Exporting data using DoCmd.TransferSpreadsheet
Exporting tables and queries to Excel
Including field names in exported Excel files |
