In this article I am going to teach you in details – How to deal with Text Files in Excel VBA. As explained above, Text files are very useful while developing any tool or add-in in Excel where you want to store some configuration data – in order to run your program or add-in.
Using Excel file to store such data will make the program very slow.I don’t need to explain much – why Text file would be a better option, rather you will find it by yourself at the end of this article.So let’s start then in a logical order Opening a file reading a file writing into a file. Topics covered in this ArticleIn this article, following topics are covered. Click on the below links to directly jump to that section. Excel VBA Code to Open a Text FileFollowing is the code Syntax to open a Text file:Open file Path For mode As #FileNumberWith the Above code Syntax, as you can see, there are 3 parameters.
file Path mode and #FileNumberSo, lets discuss about these three parameters – who are they and what role do they play in opening the text file: What is File NameThis is the path of the Text file which is going to be opened. What is Mode in Open Text File VBA CodeAs the name suggests, its the control which you want to specify before opening it. There are mainly 3 types of controls or modes possible – Input, Output and Append.Lets discuss one by one these 3 modes Input ModeThis mode is used for READ ONLY control. It means, if you open a Text file in Input mode, then you can not write anything in it.
All you can do is – read all the content inside the text file. Therefore you can say. This a read only mode. Output ModeIf your text file is open in this mode, then you can write content in it.
But what is important here to note is: In this mode, your existing file gets overwritten. This means, if there are content already in there in the text file then it will be replaced by the new data which you are trying to save. Therefore, be careful while choosing the mode of the file while opening it.Now you must be thinking – Then how to append content in an existing text file without overwriting it. Therefore the next mode – Append Mode. Append ModeAs the name suggests, this mode allow you to append the new content at the end of the text file.
It does not overwrite the existing content in the text file.So now we have an idea about all these 3 modes. It will be more clear when we use them in the below examples. Now Let’s learn about our second parameter – #FileNumber #FileNumberWhen Text files are opened then windows recognize them by a unique integer value. Valid range of Integers for this parameter is between 1 to 511.As I mentioned above, it should be a unique integer, it is challenging for you to give a fixed number here in case you are dealing with multiple text files. To overcome this challenge you can use a function called FreeFileWhat is FreeFile Function?FreeFile function returns a unique integer value which represents the file number of the file you opened. This way always a unique (or a FREE File Number – which is not used already) file-Number is assigned automatically by this function.Now you know about – How to open a Text file. Let’s see some examples and learn how to use it.
Input Data1, 2, 3,:This is the data which you want to write in the Textfile. You can write many different kind of data in each line. Each of these data will be written in textfile in a single line separated by comma.Important to note:Based on the type of the data which you put in InputData 1, 2 etc.
Object File
Write statement does some common changes while putting them in to TextFile.If the data is Date Type: Then date value is closed within hash sign. Date – 2010-10-13 will be written in TextFile as #2010-10-13#String type data: They are stored in double quotes. For example: Input Data – Vishwamitra Mishra will be stored as “Vishwamitra Mishra”.Integer, Double, Long etc: They will be written as they are. There is not formatting done before they are written in text file. Example:Let’s take an example.
Export the following table in excel to a Text file using Write statement.From the above excel cells, I will start reading one by one every column values of each row and write them in Text File. Important to note:Unlike Write statement, this does not change any of the formatting of the data for Date or String type. It just put them as they are.Values of difference columns are not separated by Comma. Rather they are separated by space(s) depending on how many spaces required to make the textfile in a printable format.
Example: To write Text File using PRINT StatementLet’s take the same example as above. Important to knowThere is another – in fact important – difference between Write and Print statement. That you will realize while reading above two TextFiles –1. Written using Write Statement2. Written using Print Statement. This is a hint for now.
What Is Text File
It will be explained in detail in the next article, where we will be talking all about reading a TextFile.In all the above examples of writing to a text box, I have used the File Open mode as Output. This means, every time you run the code, all the content of the text file will be replaced with the new one. Therefore, let’s take an example, of how can we append to the existing content in a text file using write or print statement.VBA Code to Append to Text FileThe whole trick lies in to the mode you open your text file during your VBA program.
Ascii Text File
Why I say that, because you do not need to change anything else in the Write or Print statements in order to append and not to replace. Isn’t it simple? So the complete VBA code remains same as it is there for replacing the whole content – except changing the open mode – from Output to Append.