VBA write to a text file from Excel Range

Home/VBA/VBA write to a text file from Excel Range

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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:\temp\test.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:\temp\test.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:\temp\test.txt"

 Open strFile_Path For Output As #1

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

 Close #1
End Sub 


Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |January 19th, 2015|VBA|19 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

19 Comments

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

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

    • PNRao June 4, 2015 at 2:57 PM - Reply

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

  2. Dushyant June 5, 2015 at 4:37 PM - Reply

    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

    • PNRao June 5, 2015 at 4:59 PM - Reply

      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:\temp\test.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!

  3. Dushyant June 18, 2015 at 11:25 AM - Reply

    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:\Users\dpadhya\Desktop\VBA\test.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:\Users\dpadhya\Desktop\VBA\test.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

  4. James Tait July 15, 2015 at 5:40 PM - Reply

    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:\TESTFROM\FILETRANSFERTEST2.TXT"
     Open strFile_Path For Append As #1
     Range("AB" & TextRange).Select
     Write #1, TextWrite
     Close #1
    TextRange = TextRange + 1
    Loop
    End Sub
    
    • PNRao July 15, 2015 at 8:10 PM - Reply

      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:\TESTFROM\FILETRANSFERTEST2.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!

      • Ravi June 11, 2016 at 5:56 PM - Reply

        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

  5. James Tait July 15, 2015 at 9:14 PM - Reply

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

  6. K August 25, 2015 at 5:21 AM - Reply

    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!!

  7. PCFreak February 19, 2016 at 9:59 AM - Reply

    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.

  8. Charley T April 11, 2016 at 6:47 PM - Reply

    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?

  9. Saleem Ahmed June 19, 2016 at 8:11 PM - Reply

    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:\work\test.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:\work\test.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:\work\test.txt”, ForWriting)

    objFile.Write strNewContents

    objFile.Close
    End Sub

  10. Vivek October 25, 2016 at 3:43 PM - Reply

    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

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

    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:\temp\Import_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

  12. PRANAB December 8, 2016 at 9:54 PM - Reply

    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.

  13. kishore February 24, 2017 at 4:02 PM - Reply

    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.

Leave A Comment Cancel reply


Related pages


how to create macros in excelsimple excel macro exampledefine workbook in excelplot chart excelactive cell vbalearn vba for exceldynamic chart in excelwhile loop in excel macrosas interview questions pdf free downloadvba programming examplesunprotect excel workbook 2010 with passwordproject dashboard template powerpoint freegantt chart sample excelchart wizard excelvba excel replaceexcel vba clear rangeunprotect workbook structuresas sql examplesvba access database connection exampledim vbafile handling in vbscriptaccess vba close reportwhat does vlookup meanexcel insert shortcuthow to hide columns in excel shortcutunlock excel 2010 password protected filecomplex pivot tableshow to make drop down in excel 2007how do you remove duplicate rows in excelexcel macro save filecool excel templatesexcel macro copy rowexcel vba cell containsvba yes novba code to close excel filehow to create pivot tables in excel 2007sql technical interview questions and answers for freshersbasic vlookup exampleexcel timeline chart templatepurge excel fileaccess vba is not nullexcel charting toolsapplication screenupdatingmicrosoft word project management templaterun sub in vbalearn to write macros in excelmultiple selection list boxcapacity planning spreadsheet excelvb source code examplescolor code in vbaexcel vba format celldelete selection vbavlookup formula in excel with exampleexcel advanced filter tutorialsample vba code for excelvlookup and hlookup in excel 2010sql mcq questions with answers pdfvba remove passwordinterview common questions and answers for fresherscost analysis spreadsheet templatebreak password protected excelform excel vbafor vba stepformulas in excel 2007 with examplesinterview questions for freshers pdfexcel bar chart examplesuserform excel vbasort selected cells by last name in ascending orderexcel macro to copy data from one workbook to anotherexcel vba compare two worksheetsvba application calculationvloopup exampleexcel chart wizardexcel special characters in formulameeting recap formathow to delete excel columnsformula of countif in excelrun a macro in excelsql recordsetvba excel redim preserve