VBA Write Text File

Write Text File in Excel VBA

We all come across with such kind of situation where we had to put huge data in excel files, but eventually, we have faced the situation when the size of that file with the huge data went so up that we had to convert that into a text file. Due to this we could neither work on that file nor saving the file into excel format. This kind of situation we have seen in many projects where we need to extract the data from the database. Extracting the data from the database is possible if use text file for this purpose. The text file doesn’t get hanged and we can store as many data and tables into a single text file without crashing the file. For this, we need to convert the excel file into a text file. Although it is not a big task to when it is a repetitive activity and may take huge time to perform.

To avoid this, we have a process in VBA, with that, we can convert the excel file into a text file without harming the data. By this, we can save a huge set of data in the text file by reducing and compressing the file size as well.

Understanding the VBA code of writing the text file is quite complex but it is not difficult to understand.

Process followed:

• File Path: Putting the path of the file where we have kept the excel file.
• Mode: For what purpose we need to open the file. It can be the read-only mode or writing purpose or we need to use it for appending the data.
• File Number: By this, we can count the number of time we have used for excel file for a text file.

How to Write Text File in Excel VBA?

We will learn how to write a text file using VBA code in excel with a few examples.

Example #1 – VBA Write Text File

In this example, we will see how to map file location with VBA code to write it over the Text file format. For this, we need a module.

Step 1: Go to Insert menu option and select Module as shown below.

Step 2: Now, in the opened module, write the subcategory of VBA Write Text or we can choose any name as per our choice.

Code:

Sub WriteTextFile2()

End Sub

Step 3: In the first step of coding, declare the variable myFile as String as shown below.

Code:

Sub WriteTextFile2()

Dim myFile As String

End Sub

Step 4: In the assigned variable put the location of the file which we need to convert from Excel to Text. Here we have named that file as Test with the extension .txt as shown below.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = "D:\VPB File\April Files\Final location\Final Input.txt"

End Sub

Step 5: As we have seen in the description of VBA Write Text File, we will first open the excel file and then append the data from Excel to a Text file with below command line.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = "D:\VPB File\April Files\Final location\Final Input.txt"

Open myFile For Append As #1
End Sub

Step 6: Now let’s put some text in a text file which we would like to see as shown below with the help of Write #1 for the first table. We have entered some random car brands with model name and specifications.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = "D:\VPB File\April Files\Final location\Final Input.txt"

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

End Sub

Step 7: Now close the first table as shown below.

Code:

Sub WriteTextFile2()
Dim myFile As String
myFile = "D:\VPB File\April Files\Final location\Final Input.txt"

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
End Sub 

Step 8: We would also like to see the message once the process is done.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = "D:\VPB File\April Files\Final location\Final Input.txt"

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
MsgBox "Saved"

End Sub

Step 9: Now compile the code and run it by clicking on the Play button located below the menu bar as shown below. If there is no error found, then we would get the message box with the message as Saved as shown below.

Step 10: Now go to the location which we have mentioned in the code above, we will see the text file with the name Test as shown below.

Now to check whether this text file has the same data which we have entered through VBA, open it. We will see the same data has been entered.

Example #2 – VBA Write Text File

There is another way to the same work.

Step 1: In the example, we will use the same code which we have written above but there would be a slight change in the code used. Let’s consider the same code as shown in example-1.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile =

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
MsgBox "Saved"

End Sub

Step 2: Now in place of file location, select the active workbook with ActiveWorkbook as shown below.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = ActiveWorkbook.

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
MsgBox "Saved"

End Sub

Step 3: Now select the Path from the dropdown list of ActiveWorkBook.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = ActiveWorkbook.Path

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
MsgBox "Saved"

End Sub

Step 4: Then select the path where we need to append the data into the text file separated by an ampersand.

Code:

Sub WriteTextFile2()

Dim myFile As String
myFile = ActiveWorkbook.Path & "\VPB File"

Open myFile For Append As #1
Write #1, "Ford", "Figo", 1000, "miles", 2000
Write #1, "Toyota", "Etios", 2000, "miles",

Close #1
MsgBox "Saved"

End Sub

Step 5: Now again compile the code and run it after that. On the successful completion of run, we will get the message as Saved.

Now again go to that path where we have kept this file which is having the code and see if the Text file is saved there or not.

Now open the file to check if the data is properly appended or not. We will find that data is appended in the similar way as it happened in example-1.

Pros of Excel VBA Write Text File

• We don’t even have to open any file to append the data.
• If the data is huge then we can append the data from Excel to Text without opening the file. So, there is no problem in handling the file as well.
• If we go with the process shared in example-2, then we don’t even have to give the path where we want to see the output Text file.

Things to Remember

• In the process shown in example-1, we can place the file in the location where we want to see the output.
• In the process shown in example-2, it will automatically consider the file location which has VBA code of VBA Write Text File.
• We can even select the data from any excel file which we want to convert in the Text file.
• We can select and create any many as a table that we want to append in the Text file.

Recommended Articles

This is a guide to VBA Write Text File. Here we discuss how to write a Text File in Excel using VBA code along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA InStr
2. VBA Integer
3. VBA ISNULL
4. VBA Text