VBA Write Text File

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Write Text File

In VBA, we can open or read or write a text file. To write a text file means the data we have in an Excel sheet, and we want it to be a text file or a notepad file. Therefore, there are two methods: the FileSystemObject property of VBA and the Open and Write method in VBA.

In most corporate companies, once finalizing the report, they look to upload the report to the database. They use the “Text Files” format to update the database to upload to the database. We usually copy the data from Excel and paste it into a text file. We rely on text files because they are very easy to work with because of their lightweight and simpler ways. By using VBA codingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, we can automate the task of copying data from an Excel file to a text file. This article will show you how to copy or write data from an Excel file to a text file using VBA code.

VBA Write Text File

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Write Text File (wallstreetmojo.com)

How to Write Data to Text Files using VBA?

Writing data from Excel to text is complex and requires very good knowledge of VBA coding. Follow the below steps to write the VBA code to copy dataWrite The VBA Code To Copy DataFile Copy is an inbuilt VBA function that is used to copy a file from one location to another. To use this function, we must specify the current file path as well as the destination file path. read more from Excel to a text file.

Before we show you how to write the code, let me explain how to open the text file using an open statement.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Syntax of Open Text File

Open [File Path], For [Mode], As [File Number]

File Path: The path of the file we are trying to open on the computer.

Mode: Mode is the control we can have over opening text files. We can have three types of control over the text file.

  • Input Mode: This suggests “Read-only” control of the opening text file. If we use “Input Mode,” we cannot do anything with the file. Instead, we can just read the contents of the text file.
  • Output Mode: We can write the content on this option. We need to remember that it will overwrite all the existing data. So, we must be wary of the possible loss of old data.
  • Append Mode: This mode is completely the opposite of the Output Mode. Using this method, we can write the new data at the end of the existing data in the file.

File Number: This will count the text file number of all the opened text files. It will recognize the opened file numbers in integer values from 1 to 511. However, assigning the file number is tricky and leads to confusion. For this, we can use the free File function.

Free File returns the unique number for the opened files. This way, we can assign the unique file number without duplicate values.

You can download this VBA Write Text File Template here – VBA Write Text File Template

Example #1

Follow the below steps to write the code to create a new text file.

Assume you already have a text file named “Hello.txt” in your computer storage, and we will show you how to write the data in it.

Step 1: Declare Variable

Declare the variable to hold the file path as String.

Code:

Sub TextFile_Example1()

  Dim Path As String

End Sub
VBA Write Text Example 1-1
Step 2: Determine File Number

To determine which file number we refer to, declare one more variable as IntegerVariable As IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.read more.

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

End Sub
VBA Write Text Example 1-2
Step 3: Assign File Path

Now, assign the file path with a name for the Path variable.

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:Excel FilesVBA FileHello.txt"
     'Change the path as per your requirement

End Sub
VBA Write Text Example 1-3
Step 4: Assign Free File Function

Now, assign the function “Free File” to store unique file numbers for the File Number variable.

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:Excel FilesVBA FileHello.txt"
    'Change the path as per your requirement

  FileNumber = FreeFile

End Sub
VBA Write Text Example 1-4
Step 5: Open Text File

Now, we need to open the text file to work with it. As we have explained, we need to use the OPEN statement to open the text file.

Example 1-5

Step 6: Use the Print/Write Method

Once the file opens, we need to write something in it. We need either the “Write” or “Print” method to write in the text file.

Code:

Sub TextFile_Example1()

    Dim Path As String
    Dim FileNumber As Integer

    Path = "D:Excel FilesVBA FileHello.txt"
       'Change the path as per your requirement

    FileNumber = FreeFile

Open Path For Output As FileNumber

    Print #FileNumber, "Welcome"
    Print #FileNumber, "to"
    Print #FileNumber, "VBA"

End Sub
Example 1-6

First, we need to mention the file number (here, we have assigned the file through the “FileNumber” variable), then we need to add the content we want to add to a text file.

Step 7: Save and Close Text File

Once we write the content in a text file, we need to save and close the text file.

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:Excel FilesVBA FileHello.txt"
     'Change the path as per your requirement

   FileNumber = FreeFile

Open Path For Output As FileNumber

   Print #FileNumber, "Welcome"
   Print #FileNumber, "to"
   Print #FileNumber, "VBA"

   Close FileNumber

End Sub
Example 1-7

Now, run the code manually or through the shortcut excel keyShortcut Excel KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more F5. It will write the mentioned content in the mentioned text file.

VBA Write Text Example 1-8

Example #2

Now, we will see how to write the data of the Excel sheet into a text file.

For this example, we have created simple data in Excel like below.

VBA Write Text Example 1

Step 1: With the continuation of the old example, define two more variables as Integer to find the last row and last column.

Code:

Sub TextFile_Example2()

   Dim Path As String
   Dim FileNumber As Integer
   Dim LR As Integer
   Dim LC As Integer

End Sub
VBA Write Text Example 2

Step 2: Find the last used row and column in the worksheet.

VBA Write Text Example 2-1

Step 3: Now, assign the file path and file number.

VBA Write Text Example 2-2

Step 4: Use the OPEN statement to open the text file.

VBA Write Text Example 2-3

Step 5: We need to loop through rows and columns, so declare two more variables as Integer.

VBA Write Text Example 2-4

Step 6: Now, open the loop to loop through the row (For next loop in VBAFor Next Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more)

VBA Write Text Example 2-5

Step 7: To loop through columns, open one more loop inside the existing loop.

VBA Write Text Example 2-6

Step 8: We need to write the same data line until it reaches the last column. So for this, apply the IF statement in VBA.

Example 2-7

Step 9: Now, save and close the text file.

Example 2-8

This code will write the details to a text file, but to open the text file after writing, we need to use the below code.

Example 2-9

Code:

Sub TextFile_Example2()

    Dim Path As String
    Dim FileNumber As Integer
    Dim LR As Integer
    Dim LC As Integer

    Dim k As Integer
    Dim i As Integer

   LR = Worksheets("Text").Cells(Rows.Count, 1).End(xlUp).Row
   LC = Worksheets("Text").Cells(1, Columns.Count).End(xlToLeft).Column

   Path = "D:Excel FilesVBA FileHello.txt"
   FileNumber = FreeFile

   Open Path For Output As FileNumber

   For k = 1 To LR

       For i = 1 To LC
           If i <> LC Then
               Print #FileNumber, Cells(i, k),
           Else
              Print #FileNumber, Cells(i, k)
           End If
      Next i

   Next k

   Close FileNumber
 
   Shell "notepad.exe " & Path, vbNormalFocus

End Sub

So, run the code using the F5 key or manually. Then, it will copy the data below.

VBA Write Text Example 2-10

Recommended Articles

This article is a guide to VBA Write Text File. Here, we learn how to copy/write data from a worksheet to a text file with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –