Write Text File
By Richard Rost
4 hours ago
How To Write A Text File In Microsoft Access VBA In this lesson, we will cover how to write a text file using Microsoft Access VBA to export a single customer's information in a custom format. I will show you how to add a button to your customer form, use classic file IO in VBA, and output specific fields to a text file formatted exactly as needed for external programs. We will discuss how to generate individual text files for each customer and talk about additional tools like the File System Object for more advanced file handling. Clinton from Greensboro, North Carolina (a Platinum Member) asks: How can I export one customer's information from my Access database into a plain text file? We use an old label-printing program at work that needs the customer's name and address in a very specific format, and Access doesn't seem to have a simple way to export just one record exactly the way that program wants it. MembersThere is no extended cut, but here is the file 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, create custom text file, export customer information VBA, classic file IO, write text file VBA, File System Object FSO, export single record, VBA file handle, FreeFile function, write customer form data, output text file, generate log file VBA, append file VBA, dynamic file name VBA, export notes, export plain text email template
Intro In this lesson, we will cover how to write a text file using Microsoft Access VBA to export a single customer's information in a custom format. I will show you how to add a button to your customer form, use classic file IO in VBA, and output specific fields to a text file formatted exactly as needed for external programs. We will discuss how to generate individual text files for each customer and talk about additional tools like the File System Object for more advanced file handling.Transcript Ever need to create a custom text file from your Microsoft Access database? Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to talk about how to create or write text files using Microsoft Access VBA. This is a simple and useful technique for exporting notes, saving data, or generating files that other programs can read.
Today's question comes from Clinton in Greensboro, North Carolina, one of my platinum members. Clinton says, how can I export one customer's information from my Access database into a plain text file? We use an old label printing program at work that needs the customer's name and address in a very specific format. Access doesn't seem to have a simple way to export just one record exactly the way the program wants it.
Clinton sent me a sample of the file format he needs. It's basically one field per line with a label in front of each value. First name, John, last name, Smith, and so on.
While Microsoft Access does have built-in export tools, they're designed for standard formats like CSV files, tab-delimited files, fixed width files, and Excel spreadsheets. Those are great when you want to export rows and columns, like a whole bunch of records, but they don't provide an easy way to take one specific record and format it exactly the way your other program might want it.
For that kind of custom output, the simplest solution is just to write the file yourself using a little teeny tiny bit of VBA.
Access has several built-in tools in VBA like TransferText. There are import/export wizards and table exports. Those are great when you want to dump a whole table or a query in one of those standard formats. But sometimes you need complete control over exactly what gets written to the file.
I've dealt with customers in the past who had some other software they were using that had very specific import specifications that you can't just do without some custom VBA. Or maybe you do need CSV, but you need some kind of special formatting done to that CSV format. You could write that yourself. Or you want to generate a plain text email template. Or I've got a lesson coming up soon where we're going to deal with multiple instances of Access loading up, where you want to create a sample lock file that tells your database, hey, I'm already running, don't open another copy.
When you write your own text files in VBA, you control every line, every comma, every character. That opens the door to all kinds of stuff: custom exports, logging, automation, all kinds of fancy tricks.
There are two different ways that you can work with reading and writing files in VBA. There's classic file IO (input/output). It's the original way to work with text files in VBA. It's been around since, geez, the old BASIC days. I've been using it since I was programming on my TRS-80 Color Computer. It's almost the same thing. You get a file number, you open the file, you write your text, you close the file. That's it. It's simple, it's lightweight, it's built right into VBA, there's nothing extra to set up. It's easy, and that's what we're going to be doing today.
There's a newer thing called the File System Object or FSO. It's a more modern approach. It gives you access to advanced file and folder features like checking whether a file exists, getting its size, reading folder contents, and lots more. It uses objects so the code's a little more involved, but it's very powerful.
Think of classic file IO as a basic screwdriver. It does one job, but it does it very well. File System Object is like a full toolbox with wrenches, sockets, power tools, and all that stuff. So for today's lesson, all we need is a screwdriver, so we're going to use classic file IO.
So what we're going to do is create a button on the customer form that writes the current customer's information to a text file. That's it. We can format that file any way we want using labels, commas, tabs, or plain text. In this example, we'll write out a few customer fields in a simple field name and value format just like you saw a second ago.
The process is very straightforward. We'll build the file name, get the available file number (which I'll explain in a second), file write each line of text, and then close the file when we're done.
Let's go see how it works in the database.
First, a prerequisite. As I mentioned earlier, this will require a little bit of VBA, not much, like six or seven lines of code. I'm going to show you exactly what you have to do. But go watch this video first. If you've never done any VBA programming before, it's about 20 minutes long and it'll get you started.
Here I've got a database folder and here's my database sitting in it. Let's open it up. This is my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In here, we have a customer form that's got all kinds of customers in it.
I want to make a button down here that I can click on that will take this customer's name and address and write it out to a text file.
Let's go to design view. I'm going to open this up a little bit. Copy one of these buttons (Control-C, Control-V). We're going to copy and paste. I'm going to slide it right down under here. For the caption, we'll say "Write to Text File." There's our button.
Let's give the button a good name. Double-click on it to bring up its properties. I don't want "Command30." Let's call it "WriteTextButton" (BTN).
Right-click, Build Event. That'll open up the code builder. Resize that, bring it down here.
Here I am in my Write Text Button. Let's open this up a little bit, get some room in here.
Before we can write anything to a text file, we need a variable to hold the file number, sometimes called a file handle. This is just a number that VBA uses internally to keep track of which file we're working with, because you can open multiple files at the same time. Think of it like a claim ticket at the dry cleaner. You hand in your clothes, they give you ticket number seven. From that point on, ticket number seven refers to your stuff. The same way, VBA is going to assign us file number one, file number two, and so on. That number represents the file we're working with.
To get that number, we call the FreeFile function, which asks VBA for the next available file number that isn't already in use. Once we have it, we'll use that number to open, print, close, whatever, so that VBA knows exactly which file to write to. When we're finished, when we close the file, we'll specify that number and it'll become available for later use.
So we need somewhere to put that file handle. So, Dim FF As Long (or you can use Integer, it doesn't matter). FF = FreeFile. That's a function that VBA is now going to return a number to you. So now FF is our number that we use.
Do we need to worry about what that number is? No. Do we need to see that number? No. Just use FF. Multiple files, you have FF1, FF2, and so on.
Now we open the file. So: Open "C:\Path\Customer.txt" For Output As #FF.
Open says we're opening a file. Here's the path and file name. For Output says we're creating a new file. It's going to overwrite anything that exists at that file name. Yes, you can open For Append to add to a file, but that's a different video. As #FF says, as whatever your number is: number one, number two, number three, and so on.
What if you don't want to write to this specific location? What if you want to put this file wherever your database is, right in your database's folder? Out front here, put CurrentProject.Path and a little ampersand. That'll say, whatever your database folder is, put it in there and call it Customer.txt.
So we've got our file open. It's all set to receive some data. Tab in. Print #FF, "CustomerID: " & CustomerID. Next line will be Print #FF, "FirstName: " & FirstName. These are the fields from your form. Remember, this button is on this form so we can use these fields right in here.
We'll do one more: Print #FF, "LastName: " & LastName. Then you continue on with the address and the phone number or whatever other fields you want to put in here.
When you're all done: Close #FF. Then we're going to Beep, just so we know something happened.
Save it. Debug-Compile once in a while to make sure everything's good. We're good.
We can close the editor. Close it. Open it up and click the button. We wouldn't have seen anything happen, but we heard the beep.
Let's minimize this and go take a look at our folder. There's Customer.txt. Open it up and there we go. There's the file we just created. Nice and simple. Really easy. Add whatever other fields you want in here and you're good to go.
I've got one open from earlier that I was messing with, too.
What if you want to have one for each customer? You want multiple customers in here. Now we can just throw the ID in the file name. Let me delete this one.
Let's go back to our database. Let's go back to our design and we'll go back to our code. Where's our button? Right-click, Build Event. Right here, let's put the CustomerID, so close the string, add CustomerID, and open the string back up again. Now we'll have Customer1.txt, Customer3.txt, whatever.
Save it. Debug-Compile. Close it. Open it. Click it.
Let's do another one. Let's do William Riker. He's Customer 5. Click it.
One more. We'll do a Digital Bar click. Click it.
Go back to the folder and now we've got three different customers right there, ready to go.
If you'd like to take this further, check out my Access Developer 30 course where I cover classic VB file IO in detail. We go beyond this simple example. Learn how to write complete customer records, export invoices with all their line items, read text files back into the database, even open the finished file automatically in Notepad.
Then in Developer 33, we begin working with the File System Object or FSO, which is a more advanced but much more powerful way to work with files and folders. We'll learn how to create folders, loop through files and subfolders, read file properties like size and attributes. We continue in Developer 34 and Developer 35 with even more advanced FSO techniques, including copying and moving files and folders, reading and writing text with the TextStream objects, sending files as email attachments, working with drives, temporary file names, and special system folders. In 35, we cover the On Not In List event, which is totally separate, but it's really, really cool.
If you're ready to go from writing one simple text file to full-blown file and folder automation, those Developer lessons will get you there.
There you go. Today we learned how to create and write text files directly from Microsoft Access VBA. Once you understand how to open a file, write your data, and close it properly, you can do all kinds of stuff: generate logs, exports, reports, all kinds of custom output.
Post a comment below. Let me know how you like today's video and how you plan to use text files in your database.
That's going to be your TechHelp video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main topic of this video tutorial?
A. Writing SQL queries in Access
B. Creating and writing custom text files using Microsoft Access VBA
C. Importing Excel data into Access
D. Designing Access forms
Q2. What method is demonstrated in the video for writing a custom text file?
A. Using Microsoft Access Export Wizard
B. Using classic VBA File IO (input/output)
C. Using Microsoft Word Mail Merge
D. Using Access Macros
Q3. What is the primary reason to use classic File IO over the File System Object (FSO) in this lesson?
A. Classic File IO is more modern and feature-rich
B. FSO requires additional installations
C. Classic File IO is simple and lightweight, suitable for basic file writing tasks
D. FSO cannot write to text files
Q4. What is a file handle or file number used for in VBA?
A. To specify the type of data being written
B. To track which file is open and being worked with
C. To identify the type of form being used
D. To assign permissions to database users
Q5. Which VBA function is used to obtain the next available file number?
A. FileNum()
B. NewFile()
C. FreeFile
D. GetFileNumber()
Q6. What is the purpose of the statement 'Open "C:\Path\Customer.txt" For Output As #FF'?
A. To read data from an existing file
B. To write data to a new or existing file, overwriting it
C. To lock the file for exclusive use
D. To delete the file
Q7. How can you write customer-specific information in the file name?
A. By using a static file name every time
B. By manually renaming the file after export
C. By appending the CustomerID to the file name in code
D. By exporting all records at once
Q8. What is the effect of using 'CurrentProject.Path' in your file path?
A. It opens the file in a new Access project
B. It saves the file to the user's Downloads folder
C. It saves the text file in the same folder as the Access database
D. It deletes the file from the project folder
Q9. What should you do after writing all your data to the text file in VBA?
A. Restart the database
B. Close the file handle using 'Close #FF'
C. Print the file contents to the screen
D. Delete the file
Q10. What is a possible use case mentioned for writing custom text files in Access VBA?
A. Restricting form access for some users
B. Exporting formatted data for use in other programs
C. Running compact and repair operations
D. Importing PDF files into Access
Q11. What kind of output format was required for Clinton's label printing program?
A. Tab-delimited
B. Fixed width columns
C. One field per line with a label in front of each value
D. Standard CSV file
Q12. What should you do frequently while writing VBA code, as recommended in the lesson?
A. Ignore errors until the end
B. Run the code every time you make a change
C. Use Debug-Compile to check for errors
D. Delete old code files
Q13. Which of the following is NOT a topic covered in Developer 30, 33, 34, or 35 courses mentioned at the end?
A. Advanced File System Object techniques
B. Reading and writing text with TextStream objects
C. Building multi-user chat rooms
D. Creating and managing folders and files programmatically
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B; 11-C; 12-C; 13-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 covers how to create custom text files from your Microsoft Access database using VBA. Frequently, people need to export data in a very specific format for use in other programs, and Access's built-in export options like CSV, tab-delimited, and Excel files do not allow you to format one record exactly as some older systems or third-party software might require.
For example, a viewer asked how to export a single customer's information from an Access database into a plain text file for use with an old label printing program that expects a specific format: one field per line, each prefixed with a label such as "First name: John," "Last name: Smith," and so on.
While Access can export tables and queries easily in standard formats, there is no direct option to export just one record in a precise arrangement. In cases like this, the most effective solution is to use a little VBA to write your own text file.
Access provides several methods for file exports. Built-in wizards such as TransferText are excellent for standard formats, but if you need total control over the structure of your export - right down to each line, comma, or character - VBA gives you that ability. This is especially useful when other applications set strict file formatting requirements or when you need to generate something unique, such as a custom email template or a lock file for multi-user environments.
There are two main approaches to handling file input and output in VBA. The first, classic file IO, has been part of BASIC for decades. It works by getting a file number, opening the file, writing text, and closing the file when done. This technique is lightweight, does not need extra setup, and is perfect when you only need basic functionality.
The other method involves the File System Object (FSO), which offers a more modern and powerful set of tools if you want to interact with files and folders - such as checking if a file exists, getting file properties, or working with directories. However, for the task at hand, classic file IO is the simplest choice.
In the lesson, I demonstrate how to place a button on your customer form in Access that, when clicked, writes all the information from the current record to a text file. The process is straightforward: build the file name, obtain a file number using the FreeFile function (which simply tells VBA which file handles are available), write each desired line, and then close the file.
If you're not familiar with VBA, I recommend a beginner video (about 20 minutes long) that covers the basics before jumping into this text file example. For those who want to follow along, you can use my free TechHelp template database, which you can download from my website.
To put this technique into practice, add a button to your form for writing to a text file. Give the button a descriptive name and set up an event to run your code. When your code runs, the FreeFile function assigns the next available file number, which lets you have multiple files opened simultaneously if necessary. After that, you open the file for output, write your lines (for example, "CustomerID: 1", "FirstName: John", and so on), and close the file when finished.
If you want to save the text file in the same folder as your database, use CurrentProject.Path to dynamically generate the file path, so you are not hard-coding directory locations.
You can easily modify the process to create a separate file for each customer just by including the customer ID in the filename, for example, "Customer1.txt," "Customer5.txt," and so on.
This basic technique forms the foundation for more advanced tasks. In my Access Developer 30 course, I explore classic file IO in more depth, covering advanced topics such as writing large records, exporting invoices with line items, reading text files back into Access, and launching Notepad automatically. Later in Developer 33, I introduce working with the File System Object, which is a fuller toolbox for handling folders, batch operations, and other attributes, with even more advanced FSO skills covered in Developer 34 and 35.
Writing your own text files is a fantastic way to create tailored exports, logs, and custom reports - anything that requires more control than Access's export wizards can provide.
If you found this video helpful or plan to use this technique in your own database, leave a comment letting me know how you will use text files in Access.
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 button to export customer data to a text file
Naming and configuring buttons in form design view
Using VBA to get the next available file handle with FreeFile
Opening a text file for output in VBA
Building a file path using CurrentProject.Path
Writing specific form fields to a text file with Print #
Inserting dynamic data into file names for unique output
Closing a file in VBA after writing
Testing the export function and verifying output
Adding additional fields to exported text filesArticle If you ever need to create a custom text file from your Microsoft Access database, you might discover that the standard export options are just not flexible enough. Often, you need to write out one record - like a customer's details - in a way that matches another program's specific format. For example, maybe you have an old label printer that needs one field per line with a label before each value, such as "FirstName: John" and "LastName: Smith." Access's standard export tools work best for exporting full tables or queries in CSV, Excel, or similar formats, but not for these custom requirements.
To handle this, the best solution is to use a little VBA code to write your own text file. VBA in Access offers classic file IO methods that let you open, write, and close text files exactly as you want. This approach is simple and gives you full control over every character in the output file. There is also a more modern method called the File System Object (FSO), which gives you advanced actions like checking if a file exists or looping through directories, but for straightforward writing, classic file IO is the best place to start.
Let's walk through the process of exporting a single customer's information as a plain text file, formatted however you need. This is practical for a huge range of scenarios, such as generating custom data files, creating logs, or producing templates for other applications.
First, open your Access database and go to the form where you view customer records. You will need to add a button to this form that will trigger your export. To do this, switch the form into design view, copy and paste an existing button for convenience, and place it where you want. You might want to set the caption to "Write to Text File" and give it a meaningful name, like "btnWriteText".
Next, right-click the button and choose "Build Event" to open the VBA editor. Inside, you need to write the code that will create and write to the text file. The first step is to declare a variable to store the file number (or file handle), which tells VBA which file you are writing to - even if you only ever use one file at a time. Use the FreeFile function to get the next available file number.
Here is how your VBA code could look:
Dim FF As Long
FF = FreeFile
Now, open the file for writing. The Open statement is how this happens. For instance, to output the file into the same folder as your database, use:
Open CurrentProject.Path & "\Customer" & CustomerID & ".txt" For Output As #FF
This will create a file named with the customer's ID, like Customer1.txt or Customer5.txt, in the same folder as your database. The "For Output" part means the file will be created (or overwritten if it already exists) for writing.
Next, for each field you want to export, write a line to the file using the Print command. For example:
Print #FF, "CustomerID: " & CustomerID
Print #FF, "FirstName: " & FirstName
Print #FF, "LastName: " & LastName
Print #FF, "Address: " & Address
Print #FF, "Phone: " & Phone
You can add as many fields as needed, matching the format required by your target application.
Once you have written all the data, close the file to make sure everything is saved:
Close #FF
(Optional) You can add a Beep command just to indicate the process is complete.
Your complete button code might look like this:
Dim FF As Long
FF = FreeFile
Open CurrentProject.Path & "\Customer" & CustomerID & ".txt" For Output As #FF
Print #FF, "CustomerID: " & CustomerID
Print #FF, "FirstName: " & FirstName
Print #FF, "LastName: " & LastName
Print #FF, "Address: " & Address
Print #FF, "Phone: " & Phone
Close #FF
Beep
Make sure to save and compile your VBA code to check for typing errors. Now, when you open your customer form in Access and click the "Write to Text File" button, the file will appear in your database folder, named specifically for the customer whose record was active. Open the file with Notepad or any text editor and you will see lines like:
CustomerID: 5
FirstName: William
LastName: Riker
Address: 123 Galaxy St.
Phone: 555-1234
You can repeat this with different customers, and each time you click, a new file will be created for the active record. This method gives you complete flexibility over how the file is formatted. If you need to export multiple records (for example, in a loop), you could adjust the VBA code accordingly.
This approach is great not just for custom exports but also for logging, custom reports, and automation that needs tailored file formats. Once you understand the basics of file IO in VBA - open, write, close - you can extend this for more advanced exports or even integrate with other processes or software.
With a little practice, you will be able to generate all sorts of custom data files from Access. Take time to explore further if you need more advanced file features, such as managing folders, checking if files exist, or handling more complex data. But for simple custom exports, classic file IO is all you need. |
