Writing Text Files Using Handles
Outputting Text files from Excel can be accomplished by the Print # statement. Using this method is an alternative to collecting rows of data in a worksheet and then saving the workbook as a text tab-delimited file. The example below writes 10 lines to a text file.
Program Code
Option Explicit
Public Sub WriteTextFile()
Dim intHandle As Integer
Dim strOutputLine As String
Dim i As Integer
strOutputLine = "ABCDEFG"
On Error GoTo WriteTextFileError
' ***********************************************************************
' Get a Text File Handle
' ***********************************************************************
intHandle = FreeFile
' ***********************************************************************
' Open The Output File
' ***********************************************************************
Open "C:\samplefile1.txt" For _
Output Access Write _
As intHandle
' ***********************************************************************
' Loop Through 10 Write Statements
' ***********************************************************************
For i = 1 To 10
Print #intHandle, strOutputLine
Next i
' ***********************************************************************
' Close The File
' ***********************************************************************
WriteTextFileExit:
Close intHandle
Exit Sub
WriteTextFileError:
MsgBox "An Error Occurred In This Application" & vbCrLf & _
"Please Contact The Developer" & vbCrLf & vbCrLf & _
"Error Number = " & Err.Number & " Description = " & _
Err.Description, vbCritical
Resume WriteTextFileExit
End Sub
