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?
In VBA, we can open or read or write a text file. To write a text file means the data we have in an Excel sheet, and we want it to be a text file or a notepad file. Therefore, there are two methods: the FileSystemObject property of VBA and the Open and Write method in VBA.
To create a text using a VBA code, you need to use the CreateTextFile method. This method allows you to define a location where you want to create it. This method has a syntax that where you can specify if you want to overwrite the file on the location and specify whether the file is created as a Unicode or ASCII file.
You can import data from a text file into an existing worksheet. On the Data tab, in the Get & Transform Data group, click From Text/CSV. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With