# Text Files

Text files are a common source for storing and transporting information.

This topic will address access, key functions and ways to import and export this data with VBA.

## VBA Open

To perform tasks with a text file in VBA you must first access it. We will do this through instruction Open.

Open requires parameters for:

• O pathname (directory or folder, and drive)
• O mode of access (Append, Binary, Input, Output, or Random)
• A filenumber of access (represented by a unique number per file)
    Open PathName For Mode As #FileNumber.


Each of these parameters will be detailed below.

### File's Path

You can assign the path name in two ways:

    PathName = "C:\test\database.txt" 'Directly through folders path

PathName = Application.GetOpenFilename() 'Through a dialog box selecting the file


The first form is used when the path does not change constantly. The second gives freedom, at each execution, to choose a different path.

Both of these forms will result in PathName associated with a String of the file location.

### Mode

The mode must be one of the following commands:

Output - Used to write Excel content to the text file
Input - Used to read the file
Append - Used to add content to a file
Binary - Used to read and write data in a byte format
Random - Used to place characters of defined size

The focus of this tutorial will be only on: Input (import) and Output (export).

### File Number

Each open file must contain a numbering, a number between 1 and 511 preceded by a hashtag #. Normally the numbering starts at #1 and follows successively #2...

Use the FreeFile statement to get the next available file number. If none is in use, FreeFile() will return 1.

### Testing Open Instruction

The following code will access the file you selected with Application.GetOpenFilename()

Sub OpenTeste()

Dim PathName As String

PathName = Application.GetOpenFilename()
'Opens the dialog box to select the file

'Notice that PathName will be a path String E.g. "C:\..."

Dim FileNumber As Integer

FileNumber  = FreeFile() 'Assigns the first available file number (E.g.: #1)

Open PathName For Input As #FileNumber

Close #FileNumber  'Closes the file (the number in FileNumber can be reused)
End Sub


Despite access, no information was imported or exported. To carry out these actions, we will need the help of the functions Input and Output respectively.

## Import Text File

To import content from a text file into Excel we will use an example file called "database.txt" and the function Input:

Sub SimpleImport()

Dim PathName As String

PathName = Application.GetOpenFilename()
'Opens the dialog box to select the file

'Notice that PathName will be a path String E.g. "C:\...\database.txt"

Dim FileNumber As Integer

Open PathName For Input As #1 'File will be associated with the #1

FirstCharacter = Input(1, #1) 'Collect 1 character from file # 1
SecondCharacter = Input(1, #1) 'Collect 1 more character, this being the next one (the 2nd in this case)

MsgBox FirstCharacter
MsgBox SecondCharacter

Close #1 'Close the file (number #1 to be reused)
End Sub


To collect all the characters at once we can use the LOF function:

Sub LOFimport()

Dim PathName As String

PathName = Application.GetOpenFilename()
'Opens the dialog box to select the file

'Notice that PathName will be a path String E.g. "C:\...\database.txt"

Dim FileNumber As Integer

Open PathName For Input As #1 'File will be associated with the #1

MsgBox LOF(1)  'Total number of characters in file # 1

Allcharacters = Input(LOF(1), #1) 'Collect all characters from file # 1

MsgBox Allcharacters

Close #1 'Close the file (number #1 to be reused)
End Sub


LOF returns the number of bytes of the file opened with Open. Because this is a text file, each byte is one character. Thus, the number of bytes will equal the number of characters.

To import the data into spreadsheet we can use the following code:

Sub TextImport ()
Dim PathName As String
Dim FileNumber As Integer
Dim Textdata As String
Dim BreakingLine as Variant
Dim Lastline as Integer
Dim Firstline as Integer

'Opens the dialog box to select the file
PathName = Application.GetOpenFilename()
'Or enter a path with PathName = "C:\FILE_LOCATION\database.txt"

FileNumber = FreeFile() 'Assigns the first available file number (E.g.: #1)

Open PathName For Input As #FileNumber 'Open file in read mode

'Copy the contents to Worksheet ---
Textdata = Input(LOF(FileNumber), FileNumber) 'Loads all file contents into variable
BreakingLine = Split(Textdata, vbCrLf) 'Creates a vector with each line of the file
Lastline = UBound(BreakingLine) 'Determines the last line of the vector
Firstline = LBound(BreakingLine) 'Determines the first line of the vector
'Transpose the vectors into the worksheet
Range("A1").Resize((Lastline) - (Firstline) + 1).Value = Application.Transpose(BreakingLine)
'----------------------------------
Close #FileNumber 'Closes the file (the number in FileNumber can be reused)
End Sub


vbCrLf is a non-visible character CrLf indicates a line break in the file.

## Main Functions Related to Open

Function Description
FreeFile Returns the next available number for the Open statement. Important when working with multiple files.
BOF Returns True if it is at the beginning of the defined #filenumber.
EOF Returns True if it has finished reading the defined #filenumber.
LOF Returns the size in bytes of the defined #filenumber.
Loc Returns the current read and write position for the Open.

BOF and EOF assist in building Loops when you want to work character by character, or line by line.

Character per character

    'Collect one by one
Characters = Input(1, #1) 'Collect 1 character from file # 1
Characters = Characters & Input(1, #1) 'Collect 1 more character, this being the next
Characters = Characters & Input(1, #1) 'Collect 1 more character, this being the next
'...


Loop with EOF support

    'Loop with EOF support
Characters = ""
Do While Not EOF(1)
Characters = Characters & Input(1, #1)
Loop


Loop with BOF support

    'Loop with BOF support
Do While BOF(1)
Characters = Characters & Input(1, #1)
Loop


## Export Text File

To export worksheet content to a text file:

Sub TextExport()

Dim LastRow As Long
Dim LastColumn As Long
Dim NewFile As String
Dim FileNumber As Integer
Dim CellData As Variant

FileNumber = FreeFile ' Assigns the first available file number (E.g.: #1)

'Determines the last row of the worksheet with data
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Determines the last column of the worksheet with data
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
NewFile = "C:\Test\Export.txt" 'Use an existing folder

'Exports the data from the worksheet to the created file
Open NewFile For Output As #FileNumber
For i = 1 To LastRow
For j = 1 To LastColumn
If j = LastColumn Then
CellData = CellData & Cells(i, j).Value
Else
CellData = CellData & Cells(i, j).Value & " "
End If
Next j
Print #FileNumber, CellData
CellData = ""
Next i
Close #FileNumber 'Saves and closes the text file with the data
End Sub


An error will occur if the folder you are saving the file does not already exist.

## CSV File

A .csv file (Comma Separated Values) is, as the name suggests, a text file in which the items in each row are separated by commas, delimiting what should go in each column.

It is a very common type of file, and since each line refers several times to multiple columns, it may require a treatment with loops and functions, such as BOF and EOF.

## Import CSV File

For ease of import, the Line Input instruction (which works line by line), rather than just Input (which works character by character).

Sub OpenTextToCSV()
Dim PathName As String
Dim FileNumber As Integer
Dim FileRow As String
Dim RowItem As Variant
Dim LastRow As Long

'Opens the dialog box to select the file
PathName = Application.GetOpenFilename()
'Or enter a path Ex: PathName = "C:\test\database.txt"

FileNumber = FreeFile ' Assigns the first available file number (Ex: #1)

Open PathName For Input As #FileNumber 'Opens the file in read mode

Do Until EOF(FileNumber)
Line Input #FileNumber, FileRow
RowItem = Split(FileRow, ", ")
i = i + 1
LastRow = UBound(RowItem)
For j = 1 To LastRow + 1
Cells(i, j).Value = RowItem(j - 1)
Next
Loop

Close #FileNumber
End Sub


## Export CSV File

Similar to exporting text, however mandatory the use of ", " in the separation of elements.

Sub SaveTextToCSV()

Dim LastRow As Long
Dim LastColumn As Long
Dim NewFile As String
Dim FileNumber As Integer
Dim CellData As Variant

FileNumber = FreeFile ' Assigns the first available file number (Ex: #1)

'Determines the last row of the worksheet with data
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Determines the last column of the worksheet with data
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
NewFile = "C:\Test\Export.csv" 'Use an existing folder

'Exports the data from the worksheet to the created file
Open NewFile For Output As #FileNumber
For i = 1 To LastRow
For j = 1 To LastColumn
If j = LastColumn Then
CellData = CellData & Cells(i, j).Value
Else
CellData = CellData & Cells(i, j).Value & ", "
End If
Next j
Print #FileNumber, CellData
CellData = ""
Next i
Close #FileNumber 'Saves and closes the text file with the data
End Sub


An error will occur if the folder you are saving the file does not already exist.

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.