How can I write to a text file reliably from Excel VBA?

Question

I'm trying to use Excel VBA to write to a text file. I'm doing a few of these:

MyFile1 = "C:\outputFromExcel1.txt"
fnum1 = FreeFile()
Open MyFile1 For Output As fnum1

and then writing to them like this:

Print #fnum1, text

All variables in the above are declared just with Dim. I'm writing hundreds of lines to the files and, very rarely, lines are being truncated -- i.e. the ends are being chopped off. Is there a better way to write to a file in Excel VBA?

EDIT: I've just realized that it's always the last lines to be written that are truncated. So I guess I need to close or flush the files somehow?

Answer 1

Have you considered writing the text to a different sheet (or a different workbook) and then using:

ActiveWorkbook.SaveAs Filename:="C:\MyFile.txt", FileFormat:=xlText

Not sure if this would give you better results (in terms of performance and/or formatting), but perhaps worth a try.

Answer 2

You can use Close #fnum1 to close the file handle and it should flush the remaining buffer contents.

Answer 3

Yes, you should be closing the files with the Close method. I'm not sure if that's what causing the problems but you should be doing that either way.

If you're doing a lot of filehandling in your VBA code it might be worth looking at using FSO (FileSystemObject), I think it was originally for letting VBScript do file processing, but I prefer it to both VB6s and VBAs built in file handling. See here for more details (and there's a big sample showing off how to do most things you need in one of those pages as well).