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