VBA Text Files helps us to communicate data between different computer systems. There is no format and data is available in binary format. We can import data from test files and export data to text files. Text files performance is very fast when writing, reading or storing data.
Note:The XML files are also text files. The only difference between these two are the XML files are more structured and highly organized format. It is widely used to communicate data across Internet. But it doesn’t contain different formats. Where as text files contains different formats to use.
- Objective
- Terms/Words Used in Text Files
- Creating a Text File
- Opening Text Files
- Deleting Text Files
- Writing to text Files
- Modifying Text Files
- Reading Text Files
- Appending Text Files
- Separators and Delimiters
- Import Data from Text Files
- Export Data To Text Files
- Instructions to Run VBA Macro Code
- Other Useful Resources
Terms/Words Used in Text Files
Let us see the terms or words used in the text files. It’s a best practice to know about different terms, which are used in text files programming.
- Mode : There are 3 types of control modes. The mode can be Input, Output, or Append. Let’s see.
- Input or Line Input : This Input mode represents to read text files only. We can’t write. The Line Input represents to read entire line of data considers as a single variable.
- Output : This Output mode represents to write text files. If already file exists with same name then it replaces the existing file.
- Append : This Append mode also represents to write text files. If data already exists, it appends new data at the end.
- FreeFile : The FreeFile returns a unique file number when a text file opens.
- FileNumber : The FileNumber consists of an integer value between 1 and 511. When multiple text files are open, it helps to recognize by this unique file number. It is automatically created by the FreeFile.
- Write or Print : The Write or Print statements are used to write data to text files.
- FileName : It represents text file path.
Create Text File
VBA Create Text File using CreateTextFile method of FileSystemObject (FSO) and Text Files Open statement in Excel. Read more details about by clicking on the following link.
Write to Text File
VBA Write to Text File using two statements. The first statement is Write or the second statement is Print. The Write statement generates a line of values and it is separated by commas, and adds hash marks(#) around dates and quotes(“) around strings. The Print statement generates a line which is suitable to be printed, with the data is arranged in columns with spaces between.
Open Text File
VBA Open Text Files using OpenTextFile method of FileSystemObject in Excel. Let us see examples by clicking on the following link.
Read Text File
VBA Read Text Files using the Input Statement or the Line Input statement. The Input statement expects data that generates by Write and reads data into a list of variables. The Line Input statement reads the whole line of data as a single string variable. We can read text files either line by line or all lines.
Delete Text File
VBA Delete Text Files using Kill statement or DeleteFile method of FileSystemObject(FSO). Click on the following link and let us learn how to delete text file in two different methods.
Append Text File
VBA Append to Text File using two statements. It is almost same like Write date to text files. There is only one difference between Writing and appending data to text files. Just need to replace in Write or Print statement with append instead of Input or mode mode.
Modify Text File
Separators and Delimiters
VBA Text File Separators and Delimiters
Import Data from Text File
VBA Import Data from Text File
Export Data To Text File
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers