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.

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.

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.

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.

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.

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.

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.