Reading and writing to/from a text file using TextStreams
Part one 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 (this blog)
  2. Creating a Text File and Writing Lines
  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.

Reading and Writing Text Files using TextStreams

This blog shows how to read from (and write to) text files using something called TextStream objects.

There is another way to do this in VBA using commands like Input and Open, but I think the way shown here is much easier to remember and use.

In order to get any of the code in this blog to work, you first need to create a FileSystemObject.

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

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 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.