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

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 fi开发者_运维百科les somehow?

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

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

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.

Just a necro solution: I have found the Close #1 method to still leave a truncated file. Instead, or in addition to, use the Application.Quit for Excel to close Excel. This flushes the cache and completes the write to the text file.