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.
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:
Open PathName For Mode As #FileNumber.
Each of these parameters will be detailed below.
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.
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 sizeThe focus of this tutorial will be only on: Input (import) and Output (export).
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.
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.
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.
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
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.
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.
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
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.
© 2024 SuperExcelVBA | ABOUT