Reading and writing to/from a text file using TextStreams
Part two of a three-part series of blogs

The easiest way to work with text files in VBA is using TextStream objects - this blog explains how they work.

  1. Reading and Writing Text Files using TextStreams
  2. Creating a Text File and Writing Lines (this blog)
  3. Reading in Lines from a Text File

This blog is part of a complete Excel VBA tutorial, but we also run classroom training courses in VBA.

Posted by Andy Brown on 13 January 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Text File and Writing Lines

What this Example Does

The code on this page will create a new text file on your hard disk:

New text file

The code will create the file called info.txt on your hard disk.

 

Here's what the contents of the text file will look like:

Contents of text file

The code will write out the two lines!

 

Referencing the FileSystemObject

The first thing we need to do is to create a reference to the Microsoft Scripting Runtime object library, and then create a FileSystemObject - refer to my separate FileSystemObject blog for how to do this and what it means:

Sub CreateFile()

'we need this, even though it doesn't make much sense!

Dim fso As New FileSystemObject

Creating a New Text File

To do this, apply the CreateTextFile method to the weird FileSystemObject:

'the file we're going to write to

Dim ts As TextStream

'open this file to write to it

Set ts = fso.CreateTextFile("C:\Wise Owl\info.txt", True)

In this case, the second argument True refers to whether we'll overwrite any existing version of the file when we create the new one.  We choose to do so.

Writing Out Lines of Text and Closing the File

You can use the WriteLine method to write lines of text, but you must then close the file:

'write out a couple of lines

ts.WriteLine ("If anyone finds this file")

ts.WriteLine ("they will wonder who created it")

'close down the file

ts.Close

End Sub

A Summary of the Entire Routine

Here's the entire routine for ease of copying and reference:

Sub CreateFile()

'we need this, even though it doesn't make much sense!

Dim fso As New FileSystemObject

'the file we're going to write to

Dim ts As TextStream

'open this file to write to it

Set ts = fso.CreateTextFile("C:\Wise Owl\info.txt", True)

'write out a couple of lines

ts.WriteLine ("If anyone finds this file")

ts.WriteLine ("they will wonder who created it")

'close down the file

ts.Close

End Sub

Now that we've successfully created a text file, how can we read it in?  To do this, we need to open another TextStream object, as shown in the final part of this blog.

 

  1. Reading and Writing Text Files using TextStreams
  2. Creating a Text File and Writing Lines (this blog)
  3. Reading in Lines from a Text File
This blog has 0 threads Add post