How to write a text file with Excel VBA macros

With Excel VBA you can both write (export) and import text files. This example shows how you can easily write a text file. The text uses semicolon as delimiter, so the file can be imported to cells in Excel (see: Import text files) or some other program.

Sub WriteTextFile()
'This procedure writes and saves a text file.

Dim iFileNumber As Integer
Dim sFileText As String
Dim sFile As String

On Error GoTo ErrorHandle

'Text to be saved in the file.
'We use semicolon as a delimiter, so it can be
'imported to cells later on - or by some other program.
sFileText = "This is a text;" & vbNewLine & _
"And this is the next line (or row) in the text.;1;2;3"

'The filename
sFile = "filetest.txt"

'Change to C:\ (or some other folder)
ChDir "C:\"

'Get a free file number from the operating system
iFileNumber = FreeFile

'Open a new file for output
Open sFile For Output As #iFileNumber

'Write the file
Print #iFileNumber, sFileText

'Close the file
Close #iFileNumber

Exit Sub

ErrorHandle:
MsgBox Err.Description & ", procedure WriteTextFile"
End Sub

Write and save a text file with Excel VBA