13

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?

HenricAbsolom
  • 155
  • 1
  • 1
  • 5

4 Answers4

15

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

Ben Hoffstein
  • 102,129
  • 8
  • 104
  • 120
4

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

Hans Olsson
  • 54,199
  • 15
  • 94
  • 116
1

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.

DMKE
  • 4,553
  • 1
  • 31
  • 50
Jim Snyder
  • 99
  • 2
  • 9
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.

Ben Hoffstein
  • 102,129
  • 8
  • 104
  • 120