Export to Word
By Richard Rost
4 years ago
Export a Report from Access to a Word Document
In this Microsoft Access tutorial, I will show you how to export a customer report to a Microsoft Word document using a Report, the OutputTo command, and two lines of VBA code.
Tricia from Flint, Michigan (a Platinum Member) asks: Is there any way I can export the information in a customer's file to a Word document? I know you can create reports in Access, but my boss wants us to save each customer's info in a Word doc that can be easily edited and emailed without having to use the database, but I also want to keep all of the rich text formatting I have in my database. Additionally, if there's a way to put the customer's picture in the Word document, that would be awesome.
Members
Members will learn how to dynamically create a Word document using VBA. This will allow us to take direct control of each object that goes into the document, formatting (including the formatted rich text from Access), and we can insert pictures and change their attributes.
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!
Up Next...
Suggested Course
Links
Gold Member Code Vault
 Keywords
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, export to word, rich text, rtf, outputto, acformatrtf, acformattxt, acformatpdf, How to Export Access Data to a Word Document, export images, How To Convert Access Report To Word Document, MS Access export report to Word with formatting, Export report to MS Word with formatting intact, me.refresh, acOutputReport, microsoft word 16.0 object library, typetext, inlineshapes.addpicture, activedocument
Subscribe to Export to Word
Get notifications when this page is updated
Intro
In this video, I will show you how to export a report from Microsoft Access to Microsoft Word using just a couple of lines of VBA code. We will talk about creating a customer-specific report, setting up a query to show only one record, and adding command buttons to preview the report and export it as a Word document in RTF format. I'll cover important tips like refreshing your data before exporting and explain the limitations of the export process, including support for basic text but not rich text formatting or images.
Transcript
Welcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to export a report from Microsoft Access to Microsoft Word.
Today's question comes from Trisha in Flint, Michigan, one of my Platinum members. Trisha asks, is there any way I can export the information in a customer's file to a Word document? I know you can create reports in Access, but my boss wants us to save each customer's info in a Word doc that can be easily edited and emailed without having to use the database. I also want to keep all the rich text formatting I have in my database. Additionally, if there's a way to put the customer's picture in the Word document, that would be awesome.
Well, Trisha, first the good news. Can you export the customer info to a Word document? Yes, absolutely. We can save it right from Access into a file in your Documents folder or wherever you want to put it, and you can put any customer information in there that you want.
Next, the bad news. Simple exports will not include rich text formatting or images. So if you have a notes field with rich text format in there with colors, fonts, bold, and all that, that will not export, nor will any pictures from your database export. However, the more good news is I will show you how to do that in the extended cut. We can use Word automation and some VBA programming to not only export all that formatted text from your rich text files, but you can also insert pictures. So that will be in the extended cut.
But in this video, I'm going to show you how to create a document like this: a simple, nicely formatted document in Microsoft Word that you can export right out of your database. It's pretty simple to do.
Now, I know your boss said that he wants to save each customer's file as a Word document, and you want to be able to store that on your network so everybody can open it up and edit it without using the database. But I want everyone to know that unless you specifically have a work requirement to create Word documents, you can do pretty much everything very easily straight inside of Microsoft Access. So go watch my Letter Writer document. I show you how to do all that stuff.
Now, for this class, even though it's the simple export, we're going to use VBA. Two lines of VBA code, folks, just two. You can do this with a wizard, but it's got a shortcoming and I'll explain what that is in a few minutes. Go watch my free Intro to VBA video if you do not know any VBA programming. It's 20 minutes long and will teach you just the basics, everything you need to know to get started. Watch my "Open a Report to a Specific Record" as well. I show you how to open a report and show just one customer's record, for example. That's what we're going to do in this video.
You'll find links to all this stuff down below under the video in the description area. You might have to click the Show More button to find it if you're on YouTube, but it's down there. There are links you can click on.
So here I am in my TechHelp free template. This is a free database. You can go download a copy on my website. Let's say you have a customer form right here and you want to be able to export this information, some of it at least—maybe the first name, last name, email, phone number, and whatever's in their notes field.
I'm going to go grab some stuff from my web page and just copy and paste some stuff from my bio and put it in there. So we have some stuff, just a couple of paragraphs of text that I grabbed off my website.
Now, I want to create a Word document to export this guy. The first thing I need to do is create a query that will display just this customer record. The report needs to pull off a data source which is just the customer you want to see, and I cover that in my "Open a Report to a Specific Record" video.
We're going to go up to Create and then Query Design. Then I want to pull data from my customer table, bring in all the fields where the customer ID equals (right down here) =Forms!CustomerF!CustomerID. That's query criteria. That's all covered in the other video if you don't know how to do this stuff.
Save this as MyCustomerCurrentQ. Now when I run this, I'll get one record, and that's the record we're going to base our report on so it displays just this record. If I go to someone else—here's Jim Kirk—and I run that query, now I get just Jim Kirk's record. See what that's good for.
Now I can make my report. I've already got a blank report down here. I'm just going to use this guy. If you don't know what this is, go watch my Blank Database tutorial where I build this blank database, the TechHelp database. I'll put a link to that down below, too.
I'm going to copy and paste this guy. Copy, Control+C, paste, Control+V. I'm going to call this MyCustomerCurrentR for report. Let's modify this guy, Design View. Now I'm going to maximize that. I find it's easier to work with reports while they're maximized.
We don't need anything in the header and report header page header sections right now, so I'm just going to shrink those up like that. Let's get rid of this guy and open up Add Existing Fields. We don't have any fields yet. We have to bind our report first. Hold on.
Come over here, double-click on this guy. Find the Record Source property. Drop that down. We're going to pick MyCustomerCurrentQ, so it's going to only show the customer that's currently opened on the customer form.
Now we can add existing fields. There they are. Let's get first name, last name. You could bring in the customer ID if you want to. I'm going to hold down the Control key: first name, last name, email, phone. Bring all those over here.
We'll edit the labels just a little bit: we got customer ID, first name, last name. That doesn't have to be that big. Let's make everybody black so we can read them. I always wonder why they come in gray. I don't like that.
We need the notes field. There's the notes field right there. We're going to bring that over and drop it right there. Get rid of this label that comes in with it. Now just make this guy nice and big, like so. Close that now. Make it as wide as you want. That's where my notes are going to go.
I know from experience that these guys come in with a border. I'm going to select all of that and turn the shape outline to transparent.
Let's save this (Control+S), then I'm going to restore it down and close it. Now if I preview it, that's what it's going to look like. Numbers come in aligned to the right like that, and everything else is lined up left. A little bit more room for email.
So, Design that guy. Let's change this just a bit. Select all these guys. Go Format, Left. Make these a little bit wider so that email address fits in there.
Save it one more time. Right-click, Preview. There it is. Looks much better.
Let's go back to my record. Come back over here, and let's put a button down here to preview that report. Design View. This is why I wanted to do this manually instead of using the wizard. The wizard will preview the report, but it doesn't refresh the record. I cover this in that other video that I asked you to watch, "Open the Report to a Specific Record." If you have a customer open and I come in here and make a change, right now this record is dirty. The pencil is there. That means this data hasn't been saved yet. If I open up the report, it hasn't changed.
Why I like to do this myself is the wizard does not refresh the record before generating the report.
So what we're going to do is copy this button, paste it, Preview Report. Right-click, Build Event. It brings up a code builder. Oh, I'm in Command 30. Click. I forgot to name my button. Hold on. Let's go back. I always forget to name my buttons. I don't want Alex getting mad at me. Open that up. Go to All. Let's call this PreviewReportButtonBTN. Much better. Right-click, Build Event.
The first thing we have to do is issue a Me.Refresh. That saves any changes that are made in that form to the table underneath. Then I can say DoCmd.OpenReport. What's the report name? CustomerCurrentR. Then comma, make sure you go to acViewPreview. If not, this guy will be spitting out on your default printer. We don't want that.
Normally, I could go comma comma and put in the Where Condition, the filter, instead of having to make a query, but it's not going to work with the OutputTo command in a second, as you will see.
Save that. Come back over here, close this, reopen it, and then Preview Report. There it is.
Now, if you decide to change this to rich text—and I have other videos on how to do that, I'll put a link down below again so you can watch it—that will show up in the report here, and it will print that way, but it won't export when we do the export. So if you have to convert this over to a Word document, you won't get your formatting.
The next step is to make a button now to create that Word document. Design View. Copy this guy, paste it. Export to Word. Right-click, almost forgot again, Properties. ExportWordButton. Now Alex will be proud of me here. Build Event.
To export to Word, the first thing again is Me.Refresh. Then, DoCmd.OutputTo. What do you want to output? We're outputting a report, so it's acOutputReport, comma. What's the object name? CustomerCurrentR, comma. Output format.
Now, I'm kind of mad at the Microsoft people. They don't give you a list of the output formats like they did for the object type. You just have to know what they are. Google it if you need to. Search "OutputTo" and it will tell you. But there's only a couple that you really want. There's acFormatPDF if you want a PDF file, and if you press enter right now, you'll see it capitalized. See that? The format and then the PDF capitalized. That's one of the valid ones.
The only other one you're going to use might be acFormatTXT (that's a plain text file) or acFormatRTF (which is a rich text file, which is what you need to use for a Word document). Even though it doesn't export the rich text from the notes fields, which I think is dumb, that's the format you have to use.
The only way to really get an exact image of what your report looks like from Access that's portable is to use PDF.
Comma, what's the output file? Where do you want to save this guy to? What's the file name? Well, you can put in something like C:\\whatever\\myfile.rtf, or you can be smart about it. You can drop it in the current folder that the database is in. So I'm going to do a line continuation character there (underscore), then I'm going to say CurrentProject.Path, that's wherever your database is stored. It could be your C:\\Database folder, your Documents folder, whatever. Whatever folder your database is in is where this file will go. Ampersand, a backslash.
What do you want to name this file? Let's go with the customer ID, the customer's first name and last name, and .rtf. So we're going to go customer ID, dash, first name, dash, last name, .rtf.
Comma, AutoStart means do you want Word to automatically start up once this thing is exported? That's up to you. I'm going to go with True.
Don't worry about the other things—template file, encoding, output quality. We don't need those right now.
That's all we need right now. I have a goof in here somewhere. Hold on. Let me find it. There it is right there. I forgot my ampersand.
So that's what you need right there. Like I said, two lines of code. This is technically two lines of code because the line continuation is there.
Let's go back over here, close it, save it, open it. Ready? Export to Word. Boom. There it is. There is our Word document. Some of this formatting is a little off, but that's pretty good.
Let's do a little bit of fine-tuning here. Design View. Right-click, size to grid. Let's shrink these guys right next to each other. See if we can get this to look a little tighter. Save it, close it. Try it now. There we go. That looks a whole lot better. That's not bad. I like it. See? Like I said, along with just a couple of lines of VB code, your databases can be so much more powerful. You can do all kinds of cool things.
My file, since my database is on my desktop, is right there: 1-richard-rost.rtf. It's right there.
Now you have a Word document and you can come in here and make any changes you want. You can do this, you can do that, you can save it in different formats. Do whatever you want to it. I'm going to close it. Save changes? No.
That is the simple method for exporting customer information as a Word document.
If you want to learn more, it is possible to make a fully formatted document like this in Word directly from Access. We have to use something called VBA automation, which is where we actually write VBA code to create a Word document in VB. Then we can insert pictures. We can copy over formatted text like this color. These colors came from Access.
I cover all that in the extended cut for the members. Silver members and up get access to all the extended cut videos. There are about 260 of them now. Gold members can download these databases and get access to my Code Vault.
If you want to learn more about creating Word documents from Access, check out Access Expert Level 19. I cover all kinds of cool ways to create different types of Word documents, including Mail Merge, where we can set up a Mail Merge document in Word and feed it with data from Access. That's pretty cool, too.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.
Quiz
Q1. What is the main goal of the video tutorial?
A. To demonstrate how to export a Microsoft Access report to Microsoft Word
B. To import Word documents into Access
C. To automate document scanning in Access
D. To generate invoices using Access macros
Q2. What is a limitation of Access's simple export feature to Word?
A. It cannot export customer names
B. It does not include rich text formatting or images
C. It only exports data to Excel
D. It exports only one field at a time
Q3. Which Microsoft Access feature is used to select a specific customer record for export?
A. Macros
B. Queries with criteria based on the open form
C. Import wizard
D. Linked tables
Q4. Why does the instructor prefer to use manual VBA code instead of the Access wizard for exporting?
A. The wizard cannot save records before exporting
B. The wizard is slower than VBA
C. The wizard exports data to PDF only
D. The wizard does not display labels
Q5. What is the recommended file format to use when exporting an Access report to a Word-editable document?
A. acFormatPDF
B. acFormatXLS
C. acFormatTXT
D. acFormatRTF
Q6. What does the Me.Refresh command do before generating the report or exporting?
A. Deletes the current record
B. Refreshes the list of tables
C. Saves any changes in the current form to the table
D. Opens Microsoft Word automatically
Q7. How does the output file's location get determined in the VBA example provided?
A. It is always the Documents folder
B. It is set with the CurrentProject.Path property
C. It defaults to the C:\\Temp folder
D. It asks the user every time
Q8. What must you include in the VBA DoCmd.OutputTo method to output the report as a Word-editable file?
A. acOutputQuery
B. acOutputReport
C. OutputTypePDF
D. DoCmd.OpenReport
Q9. What is one of the improvements mentioned for making the export button user-friendly?
A. Previewing the report before exporting
B. Automatically refreshing data before export
C. Enabling simultaneous export to multiple formats
D. Including a scheduling feature for exports
Q10. According to the instructor, what is the only way to export a report with all formatting and images preserved?
A. Using rich text formatted export
B. Using PDF export
C. Using the Export Wizard
D. Manually typing the report in Word
Q11. What feature is available in the extended cut of the lesson for members?
A. Exporting with rich text formatting and images using VBA automation
B. Automating Excel exports
C. Securing Access databases
D. Converting Access macros to SQL
Q12. In the context of membership levels, what is a unique perk of Platinum membership?
A. Only access to beginner videos
B. Access to download TechHelp sample databases only
C. Access to all full beginner courses for every subject and a free developer class each month after completing expert classes
D. Monthly PowerPoint templates
Answers: 1-A; 2-B; 3-B; 4-A; 5-D; 6-C; 7-B; 8-B; 9-B; 10-B; 11-A; 12-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 video from Access Learning Zone is all about exporting reports from Microsoft Access directly to Microsoft Word. I'm your instructor, Richard Rost, and I'm here to guide you through the process.
This session was inspired by a question from one of my Platinum members. She wanted to know if there's a way to export customer information from Access into a Word document. The goal is to make these files editable and shareable, even for people who may not use Access. She also wanted to keep any rich text formatting from her notes fields and include customer pictures in the Word exports if possible.
The short answer is yes, you can export customer data to Word documents from Access. That part is straightforward. You can save the Word file anywhere you like and include whichever customer details you want. However, basic exports will not keep rich text formatting or bring over pictures. If your notes field has colored text, fonts, or bolding, or if you store photos in your database, those elements won't transfer with a basic export. But the good news is, there is a way to handle those needs using Word automation and some VBA code, which I explain in the Extended Cut. With those techniques, you can transfer fully formatted text and even images. For today's video, though, I'll stick to the basic method for a simple, clean export.
Many people are asked by their companies to produce editable Word files from their Access data. It's important to note that Access itself can often create and store this information quite effectively without involving Word. For writing letters and similar documents within Access, you can check out my Letter Writer tutorial if you're interested.
For this class, you'll see how to use two simple lines of VBA code to export your report to Word. This approach overcomes some of the limitations of the built-in export wizards, which do not always refresh your data or support more specific requirements. If you're new to VBA, I recommend my free Introduction to VBA video, which will give you a solid foundation. You might also find my video on opening a report to a specific record helpful. I demonstrate these techniques to make sure you're exporting just the customer you want.
The process starts by creating a query that provides just the current customer's record from your form. This query acts as the data source for your report. To set this up, you use a criteria expression in your query to match the customer ID on your form. Save the query, and from there, you'll build a report that displays only this data.
Open your blank report (or create one if you need to), then bind it to your query using the Record Source property. Once the report is connected to your query, you can add fields like first name, last name, email address, phone number, and notes. Arrange the fields, adjust label sizes, and tweak the formatting to your liking. I usually make everything readable by changing label text to black and ensuring fields are nicely aligned.
It's a good practice to remove outlines and borders from your controls for a cleaner look. After finishing your initial setup, preview the report to ensure your formatting looks good. If anything needs adjustment, fine-tune the field sizes and alignments as needed.
To help users easily generate the report, add a button to your form that will preview the customer's report on demand. When a user clicks this button, it first saves any changes to the current record to ensure data is up to date. Then it opens the new report for review. This method avoids the common problem where the wizard fails to refresh the record, which can cause your report to miss updates.
Now, to actually export this report to Word, add another button labeled "Export to Word." When this button is clicked, the procedure saves changes and then runs an export command to output the report to a file in Rich Text Format (RTF), which Word can open and edit. You have control over where the file is saved and what it's called – for example, using the current folder and including the customer's ID and name in the filename. You can also choose whether Word should automatically open the exported file.
There are only a couple of valid output formats for this process, such as PDF (great for printing) and RTF (suitable for Word). Even though RTF export does not retain rich text from Access note fields, it's the standard for exporting to an editable Word document. If you need an exact image of your report, exporting as a PDF is another solid choice.
After exporting, you'll see the file in your designated folder, ready to be opened and edited. You can make any changes you like in Word, save it in other formats, and share it with colleagues who do not use Access.
This is the basic approach to exporting customer data from Access to an editable Word document. If you need more advanced features like exporting rich text formatting or pictures, that's where VBA automation with Word comes in. I cover this in detail in the Extended Cut available to my members, where I walk through transferring formatted text and images directly to a Word file.
If you're interested in expanding your knowledge, I highly recommend my Access Expert Level 19 course, where I explore creating Word documents in several different ways, including Mail Merge, which allows you to combine Access data with Word templates efficiently.
For those who would like to support my work and access additional content, membership options are available, each with its own set of benefits. Silver members and above get the Extended Cut TechHelp videos and free beginner classes each month. Gold members can download all sample databases and gain access to my comprehensive Code Vault, while Platinum members get the highest priority for questions and access to full introductory courses in several subjects, not just Access.
The key takeaway is that even with just a few lines of VBA, you can add powerful new features to your Access applications and make them much more useful in business environments where Word integration is important.
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
Creating a query to display a specific customer record
Setting query criteria to match the current form record
Saving and testing a single-record customer query
Building a report based on a single-customer query
Modifying report design and layout for export
Binding a report to a query as its data source
Adding and formatting fields in the report
Using VBA to preview a report for the current customer
Adding a button to preview the report in the customer form
Using Me.Refresh to save form data before report preview
Writing VBA code to export a report to Word (RTF format)
Configuring the DoCmd.OutputTo method for export
Dynamically naming exported files using record data
Setting export location using CurrentProject.Path
Explaining output format options (PDF, TXT, RTF)
Enabling AutoStart to open the file after export
Troubleshooting common issues with OutputTo
Fine-tuning report layout for better export results
Testing and verifying the exported Word document
Understanding the limitations of simple exports (no images, limited rich text)
|
