Tag Archives: write

Writing files in VBA (txt, xml, csv, binary)

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.80 out of 5)

write file vbaContinuing on the topic of working with files using VBA. Text/binary files are common ways of storing data as opposed to databases or regular Excel files.

Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY writing files in VBA. It is important to remember that you shouldn’t write all fills using the same approach. Be aware of the structure of the file. If you want performance – always select the right approach.

Writing text files in VBA

Approach #1: The Write function (strings in quotes)

Writing strings of data to text files with quotes:

This approach will result in the following output:

Notice the “” quotes. Writing further strings will result in appending further strings wrapped in double quotes which might not be a satisfactory approach. Look down to the Print function to avoid these quotes.

To Append to the text file instead of overwriting replace For Output with For Append in the Open fileName For Output As #fileNo

Approach #2: The Print function (dumping strings without quotes)

Writing strings of data to text files without quotes:

This approach will result in the following output:

This time there are no quotes in the file. In most case you will want to use the Print function instead of the Write function

Writing binary files in VBA

With Binary files often you will be using objects which are not of fixed byte length like Integers. For example you would want to save Strings. In such cases use the VBA Type object data type.

Below a simple example using the Type data type to save an object with an Integer and String.

Writing CSV files in VBA

If you want to save your worksheet as CSV you can always resort to the Text file section. However, this is an ineffective approach. If you want to save your current worksheet as a semicolon delimited file (‘;’) you can use the Workbook.SaveAs function like below.

In case you want to instead copy your worksheet simply replace Move for Copy:

Functions needed to write files in VBA

Function Description
FreeFile Get next free file number available for the Open statement / FileOpen function. Using this function is important especially when operating on multiple files simultaneously.
More info here.
BOF(fileNumber) Returns true if you are at the beginning of the file described by the file number.
More info here.
EOF(fileNumber) Returns true if you have reached the end of the file described by the file number. More info here.
Loc(fileNumber) Returns the current read/write position within an open file. More info here.
LOF(fileNumber) Returns the size in bytes of the file represented by the file number. More info here.

Write file VBA summary

Writing files in VBA is not hard and usually takes just a couple of lines of code. It makes sense however to do it write and be aware of certain traps like not omitting the use of the FreeFile function or knowing the difference between Print and Write.