Writing to a Text File from within a Macro in the 2007 Office System
Summary: Learn to use the Scripting Runtime Library to write to a text file for use with VBA macros, validation, and other automation in the 2007 Microsoft Office system. (3 printed pages)
Stephen Oliver, Microsoft Corporation
January 2009
Applies to: 2007 Microsoft Office system
Contents
Writing to a Text File
Adding a Reference to the Scripting Runtime Library
Open the Text File
Write to the Text File
Reading from a Text File
Conclusion
Additional Resources
Writing to a Text File
The Scripting Runtime Library is designed to provide a simple way to write to a text file from within your VBA macro code. To use the Scripting Runtime Library, set a reference to it in your VBA project. The example in this article uses Microsoft Excel 2007, although the steps work exactly the same in Microsoft Word 2007.
Adding a Reference to the Scripting Runtime Library
The Scripting Runtime Library contains objects that you can manipulate with macro code just like any other VBA object, such as an Excel 2007 workbook or worksheet that you already use in your macros. You can use these Scripting Runtime Library objects to write to a text file. To use the Scripting Runtime Library objects, add a reference to the Scripting Runtime Library in your project.
To add a reference to the Scripting Runtime Library
Start Microsoft Excel 2007 and open the Visual Basic Editor (VBE) by pressing ALT+F11. Alternatively, you can click the Developer tab and then click the Visual Basic button under the Code group
In the VBA Project Explorer, click the project node for which to add the scripting reference. For example, for an Excel workbook named MonthlySales.xlsx, the associated VBA project name is VBAProject (MonthlySales.xlsx).
On the Tools menu, click References.
In the References – VBAProject dialog box, under Available References:, scroll down to Microsoft Scripting Runtime.
Select the Microsoft Scripting Runtime check box, and then click OK.
Open the Text File
To write to a text file, you must either create it or open it if it already exists. The FileSystemObject class contains methods that can be used to create or open a file. You declare and create a FileSystemObject object just like any other variable.
' Declare a FileSystemObject.
Dim fso As FileSystemObject
' Create a FileSystemObject.
Set fso = New FileSystemObject
You can also declare and create a FileSystemObject object in one line of code.
Dim fso as New FileSystemObject
After you have a FileSystemObject created, create an object that represents the text file that you want to work with. The Scripting Runtime Library object that you use for this purpose is a TextStream object. After you declare the TextStream object, you create it using the CreateTextFile method of the FileSystemObject object that you created earlier.
' Declare a TextStream.
Dim stream As TextStream
' Create a TextStream.
Set stream = fso.CreateTextFile("h:\code\Support.log", True)
In the previous code example, you use the CreateTextFile method to specify where to create the file (be sure that you have the appropriate rights to create files in the location you set) and whether or not to replace or overwrite the file if it already exists. In this case, the Overwrite option is set to True. To open an existing text file, use the OpenTextFile method of the FileSystemObject.
Write to the Text File
After the file has been created or opened, you can write to the file using the Write or WriteLine method of the TextStream object. The Write method writes a line of text without adding a newline character at the end. The WriteLine method writes a line of text and adds a newline character at the end of the line. Both methods start writing at the beginning of the file.
Note
When you use the Scripting Runtime Library to write to a text file, you must start at the beginning (or end of the file, by using the OpenTextFile method of the FileSystemObject object and the ForAppending IOMode option to append text); you cannot start writing at any other place in the file. To start writing at any random location in the file, you can use the VBA FileSystem class and associated objects and methods for writing to files and not the Scripting Runtime Library provided methods discussed in this article.
stream.WriteLine "This line uses the WriteLine method."
stream.Write "This line uses the Write method."
When you have finished writing to the text file, close it using the Close method of the TextStream object.
' Close the file.
stream.Close
Reading from a Text File
Reading from a text file is similar to writing to a text file. The main difference is that you use the Read, ReadLine, or ReadAll method of the TextStream object. All three methods start at the beginning of the text file and read to the end; you cannot start reading from a random location in the text file.
Note
When you use the Scripting Runtime Library to read from a text file, you must start at the beginning of the file; however, you can skip a specified number of characters using the Skip method of the TextStream object. If you require the ability to read anywhere in the file, you can use the VBA FileSystem class and associated objects and methods for reading from files and not the Scripting Runtime Library provided methods discussed in this article.
Conclusion
This article has discussed using the Scripting Runtime Library to complete simple read and write operations to text files.
Additional Resources
For more information about concepts related to this article, see the following links.