Introduction

Welcome! Read/Write Files, Copy Folders, Email Attach

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 34. In this course we will continue working with the File System object, learn to read and write text files, and discuss using Outlook to send bulk email with attachments. We will walk through exporting a customer's data file, emailing it to them, and then importing any changes they return, updating customer records and creating a change log. Prerequisites from previous levels, especially Developer 33, are recommended. This course uses Microsoft Access 365 but the material applies to earlier versions as well. We will cover copying, moving, deleting, and listing folders and files, as well as reading and writing text files.

Navigation

Prev: D34
Next: Lesson 01

Keywords

Access Developer, File System Object, read text files, write text files, Outlook send email, bulk mail, email attachments, export customer data, import data, change log, copy folder, move folder, delete folder, list files in folder, TextStream object, Sen

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 34, brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

In today's class, we continue working with the File System object that we started in Developer 33. We are going to learn how to read and write text files. This time we will be using the File System object, which is a little bit different than the classic VB file input/output we have done before.

We are going to learn how to use Outlook to send email. We have covered it briefly before, but I am going to show you a new way of doing it today. This new method will allow us to send bulk mail and send attachments of any file type.

Then, knowing how to read and write text files, we are going to make it so we can export a customer's data file - first name, last name, and so on. We will email it to them. Then, when they email it back to us, we will be able to import those changes, update their customer record, and make a change log.

All that is covered in today's class.

Of course, this is the 34th Developer Class. I strongly recommend you have taken all my Beginner, Expert, Advanced, and Developer classes before this, especially Developer 30 and 31, which cover basic file I/O, and Developer 33, which is part 1 and 2 of the File System object, which is what we are doing in this class.

So, definitely take Developer 33 before this one. Otherwise, you will not get a lot of the stuff I am talking about as far as File System object, declaring it, early and late binding, and all that stuff. Developer 33 is definitely a hard prerequisite for this one. And of course, do not skip levels or read that page for more information as to why.

I am using Microsoft Access 365 as part of a 365 subscription, roughly equivalent to Access 2019, but everything covered in this class should go back to at least 2007.

Got questions? Post them down below in the bottom of this page or post them in my Access forum.

Do not forget to check out my TechHelp series, my kind of almost daily podcast about Access. I make two or three new ones every week.

Let's take a quick look at what is covered in today's class.

In lesson one, we are continuing with the File System object that we started in Access Developer 34. We are going to learn how to copy a folder, which copies all the subfolders and files in that folder. We will learn how to move a folder, delete a folder, and list all of the files in a folder. We will copy a file, copy a file with wildcards, copy a file with a files collection loop, and then copy a file based on either its size or its date or any other property. That is all coming up in lesson one.

In lesson two, we are going to have some fun. We are going to learn about the TextStream object where we can write text files. We will talk about WriteLine, Write, write blank lines, and more. We will create a customer text file with all the customer's details - customer ID, first name, last name, address, and so on. I will show you how to loop through the controls on your form and export just the ones we want. Then I will show you how to send that through Outlook email. We will cover both SendObject, which we have done before, and the Outlook application, so we can send it as a text file attachment to the customer. Then I will show you how to convert some code from early to late binding, because I have the email code set to early binding, where you need a reference to Microsoft Outlook. But we do not want that, so we are going to convert it in class to late binding. All that is in lesson two.

In lesson three, we are going to learn how to read text files back in. We will learn about ReadLine, read by character, ReadAll (which is read the whole file in one shot), and how to know if we are at the end of a line or at the end of the entire stream (which is the file). Then we will read back in any changes the customer made from the file that we sent them in lesson two. We will make those changes in our form and we will track the changes in a change log, which we will just store in the notes field.

All that is in lesson three.

Quiz

Q1. What is the main topic of Access Developer 34?
A. Working with the File System object to manage files and folders in Access
B. Learning only about classic VB file input/output
C. Writing SQL queries
D. Building macros for Access forms

Q2. Which of the following is NOT covered in Access Developer 34?
A. Copying, moving, and deleting folders using the File System object
B. Writing and reading text files with the TextStream object
C. Setting up Access security permissions
D. Sending emails through Microsoft Outlook with attachments

Q3. What is an important prerequisite for Access Developer 34?
A. Access Developer 33, which covers the File System object basics
B. Access Beginner 1 only
C. Excel training classes
D. Knowledge of SQL Server exclusively

Q4. What does the TextStream object allow you to do?
A. Write and read text files
B. Secure Access databases
C. Create Access tables automatically
D. Backup Access databases

Q5. When sending an email with Outlook, what new feature is added in this lesson?
A. Sending bulk mail and attachments of any file type
B. Encrypting emails in Access
C. Automatically responding to emails
D. Scheduling emails for later

Q6. Which method allows you to send a text file as an attachment via Access?
A. Using the Outlook application object
B. Using only classic file I/O
C. Using only Access queries
D. Using data macros

Q7. What do you need to be cautious about if your email automation code is set to early binding?
A. The need for a reference to Microsoft Outlook
B. Compatibility with Internet Explorer
C. Upgrading to Windows 11
D. Virus scanning software

Q8. What is created to keep track of changes when importing customer data back into Access?
A. A change log stored in the notes field
B. A backup file on the desktop
C. A zip archive emailed to the manager
D. A printed report mailed to the customer

Q9. What file operations can you perform with the File System object as covered in lesson one?
A. Copy, move, delete folders and list files in a folder
B. Encrypt folders
C. Create Access tables
D. Import Excel sheets

Q10. What version of Access is being used for the course examples?
A. Microsoft Access 365, roughly equivalent to Access 2019
B. Access 97
C. Access 2003
D. Access XP

Answers: 1-A; 2-C; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 continues our Developer series, this time focusing on working with the File System object in Microsoft Access. My name is Richard Rost, and in this class, we are building on what we covered in Developer 33, and advancing our file management and email automation skills in Access VBA.

To start, we are expanding our understanding of the File System object, which is different from the older Visual Basic file input and output methods. I am going to demonstrate how you can read from and write to text files using this newer approach. This will allow you to interact with files in a modern and efficient way.

Next up, we are going to look at sending email through Outlook. While we have touched on this topic before, I will show you a new technique that makes it easier to send emails in bulk and include attachments of any file type. This will allow you to automate communication with your customers more efficiently.

With the ability to export and import text files, I will walk you through how to generate a file containing a customer's key data including their name and address. You will learn how to email this data to the customer, and then, when they respond and send it back, you will see how to import any updates they have made. We will also cover how to track these changes in a log.

Keep in mind, this is Developer Class number 34. I strongly recommend you have completed all of my Beginner, Expert, Advanced, and previous Developer classes, especially Developer 30 and 31, which introduce basic file input and output, and Developer 33, where we started working with the File System object. Developer 33 in particular is a required prerequisite for this class because it covers crucial topics like declaring the File System object and the differences between early and late binding.

I am using Microsoft Access 365, which is part of the Office 365 subscription and very similar to Access 2019. All of the material covered here should also be applicable to versions of Access going back as far as 2007.

If you have any questions during the course, feel free to post them on the website or in my Access forum. Also, do not forget to check out my TechHelp video series, where I post additional tutorials about Access several times a week.

Now, let me give you a quick overview of what you will learn in this class.

Lesson one explores the File System object in more depth. We will cover how to copy an entire folder, including all of its subfolders and the files inside them. You will see how to move folders, delete them, and generate a complete list of all files in a specific folder. In addition, I will explain how to copy files using wildcards, how to loop through a collection of files and copy them, and how to copy files based on their properties like size or date.

In lesson two, we will focus on the TextStream object for writing text files. You will learn the difference between WriteLine and Write, how to insert blank lines, and more. I will show you how to output an entire customer's record as a text file, selecting just the fields you need by looping through the form's controls. We will cover how to send this text file as an email attachment in two different ways: using Access's SendObject method, and using the Outlook application for greater flexibility. I will show you how to convert email code from early binding, where you have to set a reference to Outlook, to late binding, which avoids that requirement.

Lesson three is all about reading text files back into Access. We will discuss using ReadLine, reading files by character, and using ReadAll to load the complete file at once. I will also explain how to detect if you have reached the end of a line or the end of the file. With these skills, you will be able to process any changes a customer has made to a file you sent them, automatically update their record in your database, and save a changelog in the notes field.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Using the File System Object in Access VBA
Copying folders with subfolders and files
Moving folders using File System Object
Deleting folders using File System Object
Listing all files in a folder
Copying files with File System Object
Copying files with wildcards
Looping through the Files collection to copy files
Copying files based on size or date
Working with the TextStream object
Writing text files using WriteLine and Write
Writing blank lines to a text file
Exporting customer data to a text file
Looping through form controls to select export fields
Sending email with Outlook from Access
Sending bulk email with attachments
Using SendObject to send attachments
Using Outlook application object for sending emails
Converting code from early to late binding for Outlook
Reading text files using ReadLine and ReadAll
Detecting end of line and end of file in text streams
Importing customer changes from a returned text file
Updating customer records from imported files
Creating and maintaining a change log for customer updates

Article

Welcome to this Microsoft Access Developer tutorial, where we will delve into some advanced topics involving the File System Object, handling text files, and integrating email functionality with Outlook. By the end of this article, you will know how to manipulate files and folders, read and write text files, and send emails with attachments from your Access database using VBA. These techniques are valuable for automating tasks such as exporting customer data, processing file-based updates, and managing file and folder structures directly from Access.

Let us begin by looking at file and folder operations with the File System Object in VBA. The File System Object, usually referred to as FSO, gives you a rich set of methods to work with drives, folders, and files on your computer. Before you can use it, you need to create an instance of the FSO. You can do this using early binding, which requires setting a reference in your VBA project to Microsoft Scripting Runtime, or you can use late binding, which lets you avoid setting explicit references.

Here is how you can create the File System Object with late binding:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

With this object, you can now perform a variety of tasks. For example, if you want to copy a folder and all its contents, use the CopyFolder method. Let us say you want to copy everything from C:\SourceFolder to C:\DestinationFolder. You can do it like this:

fso.CopyFolder "C:\SourceFolder", "C:\DestinationFolder"

If the destination folder does not exist, CopyFolder will create it for you. You can also move or delete folders using MoveFolder and DeleteFolder. Here is an example of moving a folder:

fso.MoveFolder "C:\SourceFolder", "C:ewLocation\SourceFolder"

To delete a folder and all its contents, use:

fso.DeleteFolder "C:\FolderToDelete"

If you need to list all files in a folder, you can loop through the Files collection of a Folder object. Here is how you can do this:

Dim folder As Object
Dim file As Object
Set folder = fso.GetFolder("C:\SourceFolder")
For Each file In folder.Files
Debug.Print file.Name
Next file

You can also copy files individually using CopyFile:

fso.CopyFile "C:\SourceFolder\file.txt", "C:\DestinationFolder\file.txt"

If you want to copy multiple files using wildcards, like all text files, write:

fso.CopyFile "C:\SourceFolder\*.txt", "C:\DestinationFolder\"

Sometimes, you might want to copy files based on certain properties, such as their size or the date they were last modified. You would loop through the files and use conditional logic to select which files to copy. Here is an example of copying only files larger than 100 KB:

For Each file In folder.Files
If file.Size > 102400 Then
fso.CopyFile file.Path, "C:\DestinationFolder\" & file.Name
End If
Next file

Now that we have covered file and folder manipulation, let us discuss working with text files using the TextStream object, which is also part of the File System Object. The TextStream object makes it easy to read from and write to text files.

To write to a text file, first create or open the file for writing:

Dim ts As Object
Set ts = fso.CreateTextFile("C:\Export\customer.txt", True)

You can write a line of text using WriteLine or write a string without adding a new line using Write. For example:

ts.WriteLine "CustomerID: 123"
ts.WriteLine "FirstName: John"
ts.WriteLine "LastName: Doe"

If you want to write blank lines, just call ts.WriteLine with no arguments:

ts.WriteLine

When you are finished, close the TextStream:

ts.Close

You might need to export the details of a customer from your form dynamically. To do this, you can loop through the controls on your form and decide which values to export. Here is a conceptual approach:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Export" Then
ts.WriteLine ctl.Name & ": " & ctl.Value
End If
Next ctl

This lets you flag only the fields you want to send to the customer using the Tag property.

After exporting a text file, you may want to email it to the customer. For this, you can use either the SendObject method or directly automate Outlook. SendObject is quick and easy but limited in attachment types, so automating Outlook with the Outlook Application object gives you greater flexibility.

To send an email with an attachment using Outlook late binding, do this:

Dim outlookApp As Object
Dim outlookMail As Object

Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)

With outlookMail
.To = "[email protected]"
.Subject = "Your Customer Data"
.Body = "Please find your data attached."
.Attachments.Add "C:\Export\customer.txt"
.Send
End With

Set outlookMail = Nothing
Set outlookApp = Nothing

If you have code set up for early binding (meaning you reference the Outlook object library in Tools > References), you may want to switch to late binding so your code is more portable. This means declaring objects simply as Object, as shown above, instead of specific Outlook classes.

When your customer responds with an updated file, you might want to import those changes back into your database. To read a text file in, use the following:

Set ts = fso.OpenTextFile("C:\Export\customer.txt", 1) ' 1 = ForReading

You can read each line using ReadLine:

Dim line As String
Do While Not ts.AtEndOfStream
line = ts.ReadLine
' Process the line here
Loop

Alternatively, you can read the whole file in one shot with ReadAll:

Dim content As String
content = ts.ReadAll

To know when you reach the end of the file, use the AtEndOfStream property. When reading line by line, extract the field names and values, then update the corresponding fields in your customer form or table. You may also want to record what changes were made, for example, by storing a summary in a Notes or Change Log field. This gives you a simple audit trail of customer edits.

Throughout all these tasks, always remember to close your TextStream when you are done to avoid file locks and resource leaks.

With the power of the File System Object and the ability to automate Outlook from Access, you can create highly dynamic workflows that interact with the file system and external applications. Whether you need to process batch updates, automate report generation, or exchange data efficiently with your customers, these techniques will help you build robust solutions in Access VBA.

If you have any questions about this tutorial or run into any snags, there are many forums and community resources where you can ask for help and share experiences with other Access developers. Good luck building out your Access applications!