REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

VBA code to write to a text file from Excel Range Macros Examples for writing to text files using VBA in MS Office Word, PowerPoint, Access, Excel 2003, 2007, 2010, 2013 and VBScript. This Example VBA Program and function will help you to know how to read the data from excel and write to text file from a Range using Excel VBA.

Writing to a text file from Excel Range Using VBA

Here is the Procedure, Example VBA Syntax and Example VBA Macro code for writing to a text file from Excel Range. This will help you to know how to write to a text file using VBA.

VBA write to a text file from Excel Range: Procedure

We will first open the text file for writing as output file with a file number. Then we will read the data from excel and write to the text file using File Number.

VBA write to a text file from Excel Range: Syntax

Here is the VBA code and syntax for Writing to a text file Using VBA. Here we are reading the data from Range A1 and Writing to the text File.

strFile_Path = "Your file Path"
Open strFile_Path For Output As #1
Write #1, Range(“A1”)

VBA write to a text file from Excel Range: Example Macro Code

Following is the sample Excel Macro to write to a text file from worksheet using Excel VBA. We are reading the first to rows in column A and writing to the text file using For loop.

Sub VBA_write_to_a_text_file_from_Excel_Range()
    Dim iCntr as Lonng
    Dim strFile_Path As String
    strFile_Path = "C:temptest.txt"

    Open strFile_Path For Output As #1
For iCntr = 1 To 10
    Write #1, Range("A" & iCntr)
Next iCntr
    Close #1
End Sub 

Instructions to run the VBA Macro code to read from excel and write to a text file

Please follow the below steps to execute the VBA code to read the data from Excel and write to a text file using Excel VBA Editor.
Step 1: Open any Excel workbook [ To Open MS Excel: Go to Start menu, All programs and select Excel from MS Office OR You can simply type excel in the run command (Press Windows+ r key to open run dialog)] Step 2: Press Alt+F11 to open the VBA Editor [You can also open the VBE from the Developer Tab in the Excel ribbon] Step 3: Insert a code module [Go to insert menu in the VBE and then press Module OR Simply press the Alt+i then m to insert code module] Step 4: Copy the above Example Macro code and paste in the code module which have inserted in the above step
Step 5: Change the folder path as per your testing folder structure
Step 6: Enter some sample data in Range A1 to A10 for testing purpose
Step 7: Now press the F5 to Run and Execute the Macro
You can press the F8 to debug the macro line by line and see the result immediately

Once you are done with the macro execution, now you can observe that a text file in the test folder. And the text file in the folder is updated with the data from the specified range A1 to A10.

VBA write to a text file from Excel Cells Macro Explained

Here is the detailed explanation of the Excel macro to write to text file from Excel Cells using VBA.

  • Starting the program and sub procedure to write VBA code to read data from Excel and write the data to a text file.
  • Declaring iCntr variable as Long Data Type to use it in for loop to repeat the range A1 to A10.
  • Declaring the strFile_Path variable as String Data Type to store the text file path.
  • Assigning the File path to the variable strFile_Path.
  • Opening the text file for Output with FileNumber as 1.
  • ‘Looping through the cells in the range A1 to A10 and writing the date from excel into the File using FileNumber and Write Command.
  • Closing the File using FileNumber.
  • Ending the Sub procedure to write VBA Code to read data from Excel and write the data to a text file.

Here is the commented macro code for writing to text file using VBA.

‘Starting the program and sub procedure to write VBA code to read data from Excel and write the data to a text file.
Sub VBA_write_to_a_text_file_from_Excel_Range()

‘Declaring iCntr variable as Long Data Type to use it in for loop to repeat the range A1 to A10.
Dim icntr As Long

‘Declaring the strFile_Path variable as String Data Type to store the text file path.
Dim strFile_Path As String

‘Assigning the File path to the variable strFile_Path.
strFile_Path = “C:temptest.txt”

‘Opening the text file for Output with FileNumber as 1.
Open strFile_Path For Output As #1

‘Looping through the cells in the range A1 to A10 and writing the date from excel into the File using FileNumber and Write Command.
For icntr = 1 To 10
Write #1, Range(“A” & icntr)
Next icntr

‘Closing the File using FileNumber.
Close #1
Ending the Sub procedure to write VBA Code to read data from Excel and write the data to a text file.

VBA write to a text file from Excel Range without double quotes: Example Macro Code

Following is the sample Excel Macro to write to a text file without double quotes from worksheet using Excel VBA. We are reading the rows first in column A and writing to the text file using For loop.

Sub VBA_write_to_a_text_file_from_Excel_Range()
    Dim iCntr
    Dim strFile_Path As String

    strFile_Path = "C:temptest.txt"

    Open strFile_Path For Output As #1

    For iCntr = 1 To 10
        Print #1, Range("A" & iCntr)
    Next iCntr

    Close #1
End Sub 
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBATags: Last Updated: January 19, 2015

22 Comments

  1. hyderabad June 4, 2015 at 11:51 AM

    Thanks it was a great help for a beginner like me!!

  2. PNRao June 4, 2015 at 2:57 PM

    You are most welcome! Enjoy learning VBA to do awesome things at your work place!
    Thanks-PNRao!

  3. Dushyant June 5, 2015 at 4:37 PM

    Hi

    Tried this code it is working fine but output is coming inside braces, double cords (” “) can please tell how to remove this

    thanks and regards

    Dushyant

  4. PNRao June 5, 2015 at 4:59 PM

    Hi Dushyant,

    Here is the code which will help you to solve your requirement.

    Sub VBA_write_to_a_text_file_from_Excel_Range()
    Dim iCntr
    Dim strFile_Path As String
    strFile_Path = “C:temptest.txt”

    Open strFile_Path For Output As #1
    For iCntr = 1 To 10
    Print #1, Range(“A” & iCntr)
    Next iCntr
    Close #1
    End Sub

    Thanks-PNRao!

  5. Dushyant June 18, 2015 at 11:25 AM

    Hi

    With the help of your above example i have created below code which is creating one batch file which have some SVN command

    Sub CopyToTxt()

    Dim Rows

    ‘ Declaring the strFile_Path variable as String Data Type to store the text file path.

    Dim strFile_Path As String

    ‘ Assigning path where bat file will create followed by file name with extension as .bat

    strFile_Path = “D:UsersdpadhyaDesktopVBAtest.bat”

    ‘ opening the bat file for writing value with #1 command

    Open strFile_Path For Output As #1

    ‘ printing value from defined column (Range- Rows 1 to 10000) to notepad,

    For Rows = 1 To 10

    Print #1, Range(“A” & Rows); ” “; Range(“B” & Rows); ” “; Range(“C” & Rows); ” “; Range(“D” & Rows); ” “; Range(“E” & Rows)

    Next Rows

    Close #1

    ‘ command to run batch file

    Shell (“D:UsersdpadhyaDesktopVBAtest.bat”)

    i want to write this excel data direct to command prompt, can you please tell me what changes i need to do to accomplish this

  6. James Tait July 15, 2015 at 5:40 PM

    Hi,
    Love this code, after hours of looking am 99% where I need to be.
    Need some help though, when the code writes to the text file it stores as “My Text Here” But I need to get rid of the ” as it prevents me from creating what I need.
    I am using this VBA to create a .Bat script daily to transfer files, the column it looks at has the copy commands the bat needs to run.
    I modified the code to loop though the column and place in the text but the ” makes it unusable.
    Please Help! Code Below:

    Sub VBA_write_to_a_text_file_New_Line()
    Sheets("TESTTRANSFER").Select
    MaxRange = Range("AB1").Value
    TextRange = 2
    
    Do Until TextRange = MaxRange
    
    TextWrite = Range("AB" & TextRange).Value
        Dim strFile_Path As String
        strFile_Path = "C:TESTFROMFILETRANSFERTEST2.TXT"
        Open strFile_Path For Append As #1
        Range("AB" & TextRange).Select
        Write #1, TextWrite
        Close #1
    TextRange = TextRange + 1
    
    Loop
    
    End Sub
    
  7. PNRao July 15, 2015 at 8:10 PM

    Hi James,

    Even I faced this issue on my first time. Please replace “Write” Keyword with “Print”. Here you go:

    Sub VBA_write_to_a_text_file_New_Line()
    Sheets("TESTTRANSFER").Select
    MaxRange = Range("AB1").Value
    TextRange = 2
    
    Do Until TextRange = MaxRange
    
    TextWrite = Range("AB" & TextRange).Value
        Dim strFile_Path As String
        strFile_Path = "C:TESTFROMFILETRANSFERTEST2.TXT"
        Open strFile_Path For Append As #1
        Range("AB" & TextRange).Select
        Print #1, TextWrite
        Close #1
    TextRange = TextRange + 1
    
    Loop
    
    End Sub
    

    Thanks-PNRao!

  8. James Tait July 15, 2015 at 9:14 PM

    Works Great, Thank You so much for the help.
    Site Bookmarked :)

  9. PNRao July 16, 2015 at 9:23 PM

    Welcome James!
    Please ask questions in our QA forum to get quick answers: http://analysistabs.com/qa/

    Thanks-PNRao!

  10. K August 25, 2015 at 5:21 AM

    Thanks for the code!! It does exactly what I want.

    One question:
    How do I name the file name as the value of one of the cells in the worksheet?

    Thanks!!

  11. PNRao August 25, 2015 at 9:28 PM

    Hi, You can refer the below example and modify as per your requirement:
    http://analysistabs.com/vba/save-sheet-as-workbook-excel-macro-code/#comment-21735

    Thanks-PNRao!

  12. PCFreak February 19, 2016 at 9:59 AM

    Looking to get a macro that will Write one column range (unknown/variable number of Rows) to a file (File name from existing header). I also need to loop through a number of columns. …….. Maybe a selected range across all the data then create the files and will need the ability to over write existing files.

  13. Charley T April 11, 2016 at 6:47 PM

    This does exactly what I need except the ability to specify the file name without editing the script. I need to create multiple txt files with different names. Is there a way to have the script to ask for a file name before writing the data to it?

  14. Ravi June 11, 2016 at 5:56 PM

    After executing this code my text file is increasing but no data is saved and excel and vba goes not responding
    could please help me

  15. Saleem Ahmed June 19, 2016 at 8:11 PM

    Hi I am not good at programming. However, I trying to provide a solution to an issue at my workplace. I managed to build the below
    code which is working and resolved the issue partially. The below code is helping me copy data from excel sheet and create a text
    files by deleting blank lines. But, each time new text file is created it is replacing the old file. Can anyone please help me with the code to retain all the old files in the folder and simultaneously create a new file?

    Dim iCntr As Long
    Dim strFile_Path As String
    strFile_Path = “C:worktest.txt”
    Open strFile_Path For Output As #1
    For iCntr = 6 To 30
    Print #1, Range(“B” & iCntr)
    Next iCntr
    Close #1
    Const ForReading = 1

    Const ForWriting = 2

    Set objFSO = CreateObject(“Scripting.FileSystemObject”)

    Set objFile = objFSO.OpenTextFile(“C:worktest.txt”, ForReading)

    Do Until objFile.AtEndOfStream

    strLine = objFile.Readline

    strLine = Trim(strLine)

    If Len(strLine) > 0 Then

    strNewContents = strNewContents & strLine & vbCrLf

    End If

    Loop

    objFile.Close

    Set objFile = objFSO.OpenTextFile(“C:worktest.txt”, ForWriting)

    objFile.Write strNewContents

    objFile.Close
    End Sub

  16. Vivek October 25, 2016 at 3:43 PM

    Hey,

    The article was of great help. I just need one modification in this.

    I want the data in different cells to be copied to different text files.

    Could you help me with this.

    Thanks,
    Vivek

  17. Pranab Chaturvedi December 2, 2016 at 11:28 PM

    Need help.I am using this VB code, but for me its difficult to predict the range how do I do that:

    Private Sub CommandButton1_Click()
    Dim iCntr
    Dim strFile_Path As String
    strFile_Path = “C:tempImport_To_CCH.txt”
    Open strFile_Path For Output As #1
    For iCntr = 2 To 2313
    Print #1, Range(“D” & iCntr)
    Next iCntr
    Close #1
    End Sub

  18. PRANAB December 8, 2016 at 9:54 PM

    Thank you PN Rao ji for this. Can you let me know how to do this VB when we want the data to be fetched from another Sheet.

  19. kishore February 24, 2017 at 4:02 PM

    HI,
    I have xml file and I wanna convert that to XLS file with some specific attributes inside a specific tag. Can any one please give me sample windows batch file. It will be very helpful to me.

    Thank you,
    Kishore T.

  20. B.M.MEGHNATHI March 28, 2019 at 12:48 AM

    How to file name is my cell value?

  21. PNRao July 4, 2019 at 6:40 PM

    Let us say, you have the filename in the Range B1, your code will be as shown below:
    strFile_Path = “C:temp”&Range(“B1”).value

  22. Jeni September 25, 2020 at 9:19 AM

    Hi..
    I need to convert a excel to txt file using Vb macro ex: my data’s are in excel in A colum n B coulmn i need those datas to convert in txt file as single row like A column data then BColumn data again A coimn data n B column data without anyspace need to copy only the column datas in single row

    For example: excel data As
    A. B
    134578888 W677888Z01
    144533888 Z0677888Z02

    Then I need to convert as txt file as single row
    134578888W677888Z01144533888Z0677888Z02

    Can anyone help me with the coding am entirely new to this

Leave A Comment