Continuing 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:

Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileName = "C:\test.txt"
fileNo = FreeFile 'Get first free file number  
textData ="Hello World!"

Open fileName For Output As #fileNo  'Open file for overwriting! Replace Output with Append to append
Write #fileNo, textData
Close #fileNo

This approach will result in the following output:

"Hello, World!"

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.

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

Writing strings of data to text files without quotes:

Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileName = "C:\test.txt"
fileNo = FreeFile 'Get first free file number  
    
Open fileName For Output As #fileNo 'Open file for overwriting! Replace Output with Append to append
Print #fileNo, textData
Close #fileNo

This approach will result in the following output:

Hello, World!

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

Dim fileName As String, fileNo As Integer, testVar As Integer
fileName = "C:\test.bin"
testVar = 4
fileNo = FreeFile
Open fileName For Binary Lock Read Write As #fileNo
Put #fileNo, , testVar
Close #fileNo

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.

Type TestType
    intVar As Integer
    strVar As String
End Type

Sub WriteBinary()
  Dim fileName As String, fileNo As Integer, testVar As TestType
  fileName = "C:\test.bin"
  testVar.intVar = 4
  testVar.strVar = "Hello!"
  fileNo = FreeFile
  Open fileName For Binary Lock Read Write As #fileNo
  Put #fileNo, , testVar
  Close #fileNo
End Sub

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.

PathName = "C:\test.csv"
'Assuming you want to export your current Worksheet
ActiveSheet.Move 'Move the Worksheet to a new Workbook
ActiveWorkbook.SaveAs Filename:=PathName, FileFormat:=xlCSV, CreateBackup:=False

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

PathName = "C:\test.csv"
'Assuming you want to export your current Worksheet
ActiveSheet.Copy 'Copy the Worksheet to a new Workbook
ActiveWorkbook.SaveAs Filename:=PathName, FileFormat:=xlCSV, CreateBackup:=False

Functions needed to write files in VBA

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.