Access.2007.VBA.Bibl..

FileSystemObject ..........................................................................................

The FileSystemObjectobject library provides an alternate approach to working with text files,

using the TextStreamobject. The FSO case in the cmdCreateEMails_Clickevent procedure

starts by creating a FileSystemObjectvariable and then creating a TextStreamvariable

using the CreateTextFilemethod. A string of introductory text to be written to the file is

saved to the strTextvariable and then written to a line in the text file using the WriteLine

method. To create two blank lines in the text file, the WriteBlankLinesmethod is then used

with the Linesargument set to 2.

You need a reference to the Microsoft Scripting Runtime object library to support

this code.

The code iterates through the ItemsSelected collection of the lstSelectContacts listbox, checking

each record for required fields (Email and CompanyName). For each record that lacks data in one

or both of the required fields (and thus won’t get an email message), an If... Thenstatement

writes a line containing information on which record has been skipped, and why, plus another

blank line. When all the items have been processed, a final line is written to the text file.

VB ................................................................................................................

The third case uses legacy VB statements from the earliest days of Access; no object model refer-

ence is needed. The Open strFile For Output As #1statement creates and opens the text file

for output. A string of introductory text to be written to the file is saved to the strTextvariable

and then written to a line in the text file using the rather unintuitive Print #1method. To create

two blank lines in the text file, two Print #1lines with no argument are used.

The code iterates through the ItemsSelected collection of the lstSelectContacts listbox, checking

each record for required fields (Email and CompanyName). For each record that lacks data in one

or both of the required fields (and thus won’t get an email message), an If... Thenstatement

writes another blank line and a line containing information on which record has been skipped.

At the end of the case, a final line is written, and the file is closed using the Close #1statement.

This does not delete the text file, just closes it.

Reading Data from Text Files ..................................................................................

Just as you can write data to text files, you can use legacy VB statements, FileSystemObjectcode,

or ADO code to read data from text files. The Import Data from Text File form (frmTextImport) has a

command button for selecting a text file to import (Figure 9.13 shows the form, and Figure 9.14

shows the File Picker dialog opened by this button), and an option group with a choice of ADO, FSO

(FileSystemObject), and VB-type text imports; the imported data is written to the large Imported Text

textbox when you click the “Load Data From File” button. A “Clear Imported Data” button clears the

Imported Text textbox so you can start over.

NOTENOTE

Part II Writing VBA Code to Exchange Data between Office Components