WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Write Text File

VBA Write Text File

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj 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 a text file or a notepad file, there are two methods to do, one is by using the File System object property of VBA and another is by using the Open and write method in VBA.

In most of the corporate companies, once the report is finalized, they look to upload the report to the database. To upload to the database, they use the “Text Files” format to update the database. We usually copy the data from excel and paste to a text file. The reason why we rely on text files because those are very easy to work with because of their lightweight and simpler ways. By using VBA coding, we can automate the task of copying data from excel file to text file. In this article, we will show you how to copy or write data from an excel file to text file using VBA Code.

VBA Write Text Example

How to Write Data to Text Files using VBA?

Writing data from excel to text is complex coding and requires very good knowledge of VBA coding. Follow the below steps to write the VBA code to copy data from excel to a text file.

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

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. We can just read the contents of the text file.
  • Output Mode: Using this option, we can write the content on it. The point we need to remember here is all the existing data will be overwritten. So, we need to 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 actually 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. This will recognize the opened file numbers in integer values from 1 to 511. Assigning the file number is the tricky one and leads to a lot of 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 any kind of 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 have already had 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 are referring to, declare one more variable as Integer.

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 for the Path variable, assign the file path with a file name.

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:\Excel Files\VBA File\Hello.txt"
     'Change the path as per your requirement

End Sub

VBA Write Text Example 1-3

Step 4: Assign Free File Function

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

Code:

Sub TextFile_Example1()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:\Excel Files\VBA File\Hello.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 I 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 is opened, we need to write something in it. To write in the text file we need to use either the “Write” or “Print” method.

Code:

Sub TextFile_Example1()

    Dim Path As String
    Dim FileNumber As Integer

    Path = "D:\Excel Files\VBA File\Hello.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 the content is written 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 Files\VBA File\Hello.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 this manually or through shortcut excel key 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 excel sheet to a text file.

For this example, I 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: Now, 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 Loop to loop through the row (For next loop in VBA)

VBA Write Text Example 2-5

Step 7: Now, 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 line of data 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 written, 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 Files\VBA File\Hello.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 has been a guide to VBA Write Text File. Here we learn how to copy/write data from a worksheet to text file with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Wait
  • VBA FileDialog
  • InStr VBA Function
  • Free VBA Training
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Write Text File Template

Coursera IPO Financial Model & Valuation Free Download