CODEWITHSUNDEEP
vbaexcel
HenricAbsolom
HenricAbsolom

Reputation: 155

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 files somehow?

Upvotes: 13

Views: 45376

Answers (4)

Jim Snyder
Jim Snyder

Reputation: 109

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.

Upvotes: 1

Hans Olsson
Hans Olsson

Reputation: 55049

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

Upvotes: 4

Ben Hoffstein
Ben Hoffstein

Reputation: 103385

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

Upvotes: 15

Ben Hoffstein
Ben Hoffstein

Reputation: 103385

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.

Upvotes: 1

Related Questions