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 | 2 comments

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.

 

This blog has 2 comments

Comment added on 20 January 2012 at 16:26 GMT
Possible to use SQL on a PC to import large amount of data?

I usually work with MS-Acces, but this time I have more than 500 TXT files, each about 10 MB which exceeds the limitations of Access. now, I just want to merge these files in a big table and then the ability to "filter" different data... so I think I should work with SQL Server...my Q's:

1. can I install specific part of SQL Server, (e.g. Client Tools,...) on a PC? (considering that I don't need all of SQL Sever package, but only the ability to import data and filter what I want)

2. if I have to install the whole package, is it possible to install it on a PC?.. can you provide me with a link to help me achieve that goal?..

3.any other way?

thanks in advance...
Reply from Andy Brown
My advice would be to get SQL Server Express, which you can install as a standalone package on your laptop if you choose, and which - best of all - is free. However, I believe that SQL Server Management Studio (the client tool) is available as a free download also. If you do get the data into a database (which sounds a good idea), you can also write VBA to link to this from within Excel or any other MS application by referencing the ActiveX Data Objects object library. Unfortunately I haven't got round to writing this blog, but if you Google "Link to ADO using VBA" you should find the answer. Using this, you could just use Access. Typically the problems you get with this are with the user interface side, so providing you've got no more than 100,000 or so records and only a few people accessing the data simultaneously, you should be OK.
Comment added on 21 January 2012 at 16:23 GMT
thanks, you're the best...

1. I'm not familiar with VBA, so I kinda trying to find a way around it... (i.e: doing what I said above, without VBA).

2. this DB (which is actually one table) has more than 10 million records ...
Reply from Andy Brown
Definitely SQL Server, then! Good luck. My colleague Andrew has written a blog on downloading the Express edition.

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.