VBA Excel, Writing to a Text File

In this article I will explain how you can write data to a text file.

Jump To:

You can download the file and code related to this article here.


Example 1, Write One Line of Data to Text File:

The example below creates a text file at the location “D:Temp” under the name “Test.txt” and writes the value in cell A1 in the first line of the text file:

Sub Example1()
Open "D:TempTest.txt" For Output As #1
Write #1, Cells(1, 1)
Close #1
End Sub

The first line checks if the file “D:TempTest.txt” exists. If it doesn’t it will create a file named “Test.txt” at the location ” D:Temp”. If it does exist it will overwrite the file.

Open "D:TempTest.txt" For Output As #1

In our example the file does not exist, therefore a new file is created:

Excel VBA, Writing to Text Files, Example 1, New File It has 3 parts:

“D:TempTest.txt”: Specifies the full path of the file.

Output: Specifies that this file is for writing to. This is opposed to the value Input used in the article Excel VBA, Reading Text Files

#1: This could be any number between #1 and #511. Whatever number is chosen here should be used in the rest of the program to reference this file.

The next line prints the data in cell A1 to the first line of the text file:

Write #1, Cells(1, 1)

The content of cell A1 can be seen below:

Excel VBA, Writing to Text Files, Example 1, Cell A1 The last line of code closes the file that has the reference #1:

Close #1

Had we chosen another number when opening the file, we would have had to choose the same number here when closing it. For example lets say we decide to user the number #4. We would have to user the #4 throughout the code:

Sub Example1()
Open "D:TempTest.txt" For Output As #4
Write #4, Cells(1, 1);
Close #4
End Sub
End Sub

The code above will yield the same result as our original code.

Always remember to close the file at the end of your code. Closing the file does the following:

  • Saves the data written to the file.
  • Allows other programs to modify the file.
  • If the file is not closed, you will not be able to reopen it using another number index.

Final Result:

Excel VBA, Writing to Text Files, Example 1, Final Result


Example 2, Write Multiple Lines of Data to Text File:

The example below creates a text file at the location “D:Temp” under the name “Test.txt” and writes all the data in column A to it:

Sub Example2()
Dim flag As Boolean
Dim i As Integer
'open the file for writing
Open "D:TempTest.txt" For Output As #4
flag = True
i = 1
'keeps going until the end of the file is reacheed
While flag = True
'check if the current cell has data in it
If Cells(i, 1) <> "" Then
'write the data to the file
Write #4, Cells(i, 1)
'go to next cell
i = i + 1
Else
'if the last row has been reached exit the loop
flag = False
End If
Wend
'close the file
Close #4
End Sub

The following data was written in column A:

Excel VBA, Write to Text File, Example 21

Result:

Excel VBA, Write to Text File, Result


Example 3, Save File Dialog:

In the example below the user will initially be asked to choose the location for the output text file using a save file dialog. A text file will then be created and the data in column A will be printed to the text file. For more information about save file dialogs please see the following links:

Sub Example3()
Dim flag As Boolean
Dim i As Integer
Dim strPath As String
strPath = Application.GetSaveAsFilename(FileFilter:= _
"Text Files (*.txt), *.txt", Title:="Save Location")
If strPath <> "False" Then
'open the file for writing
Open strPath For Output As #4

flag = True
i = 1

'keeps going until the end of the file is reacheed
While flag = True
'check if the current cell has data in it
If Cells(i, 1) <> "" Then
'write the data to the file
Write #4, Cells(i, 1)
'go to next cell
i = i + 1
Else
'if the last row has been reached exit the loop
flag = False
End If
Wend
'close the file
Close #4
End If
End Sub

The highlighted code creates a save file dialog and asks the user to select a location to save the file:

VBA, Write To Text File, Save File Dialog The final result is similar to the previous examples.

Sub Example4()
Dim flag As Boolean
Dim i As Integer
Dim strPath As String
On Error GoTo lblError:
strPath = Application.GetSaveAsFilename(FileFilter:= _
"Text Files (*.txt), *.txt", Title:="Save Location")
If strPath <> "False" Then
'open the file for writing
Open strPath For Output As #4
flag = True
i = 1
'keeps going until the end of the file is reached
While flag = True
'check if the current cell has data in it
If Cells(i, 1) <> "" Then
'write the data to the file
Write #4, Cells(i, 1)
'go to next cell
i = i + 1
Else
'if the last row has been reached exit the loop
flag = False
End If
Wend
'close the file
Close #4
End If
Exit Sub
lblError:
Err.Clear

End Sub

You can download the file and code related to this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

26 thoughts on “VBA Excel, Writing to a Text File”

  1. bob says:

    great article! very helpful

    one question – how do you save multiple lines without quotation marks?

    1. pedrumj says:

      Hi there

      Use “Print” rather than “Write” and you won’t have the quotation marks.

      1. Nantachai Areejitsakul says:

        You save my life.

      2. Nam Pham says:

        Thank you very much, very simple :))

        1. pedrumj says:

          No problem, glad I could help 🙂

  2. Javier Luce says:

    I use this code on XP and it works great to read/write files on a net location, it’s done in a second, but if I use it on win 7 or win 8 it takes about 5 seconds to open the file, another 5 to save an another 5 to close!, Why is that? Can I solve it? Anyone?

    1. pedrumj says:

      Hi there

      Well actually I’m using VBA on windows 7 and the code is working fine on my side. Could you please send me your workbook so that I could have a look at it.

  3. Javier Luce says:

    Hi pedrumj thanks for your help, I have no problems for “input” or
    “append”, but when I use “output” the “close” instruction takes 10-15 seconds, here is a sample:
    Open “\Server1Tiempo_ExtraDatosProceso3.Txt” For Output As #1
    Write #1, 0
    Close #1 ‘This instruction takes 10-15 secs on win 7 or win 8
    Open “\Server1Tiempo_ExtraDatosTimer_Proc.Txt” For Output As #1
    Write #1, Timer
    Close #1 ‘This instruction takes 10-15 secs on win 7 or win 8

    I have tried in many win 7 PCs and I got the same response.
    However this code on XP takes nothing, do you have any idea? Thanks in advance.

    1. pedrumj says:

      Well unfortunately I can’t reproduce the error on my side, so I won’t be able to help you on the “Output” command. But if the “Append” command is working correctly a workaround would be to use the method explained in the article below to delete the file:

      http://software-solutions-online.com/2014/05/28/vba-delete-file/

      And to use the “Append” command to create a new file. Note that “Append” will create a new file if the file is not found.

      1. Javier Luce says:

        Hi pedrumj, I changed the “output” lines; deleted the file first and then used “append” as you recomended and it is working as fast as it was before on XP. Thank you very much for your help.

        1. pedrumj says:

          No problem, glad you found a solution 🙂

  4. squall says:

    hi. this article is great!! very helpful for my work. but 1 question; you know that .txt file that create is encode in ANSI right? i want to know if you know how to change the encode to UTF-8 and of course by code in the VBA. in simple word, i want a .txt file that we create is automatically encode into UTF-8. i’ve search so many source in the internet but majority of them is to convert the excel sheet. not the .txt file that created.

    1. pedrumj says:

      Hi there

      You could try something like this:

      Dim fsT As Object
      Set fsT = CreateObject("ADODB.Stream")
      fsT.Type = 2 'Specify stream type - we want To save text/string data.
      fsT.Charset = "utf-8" 'Specify charset For the source text data.
      fsT.Open 'Open the stream And write binary data To the object
      fsT.WriteText "special characters: äöüß"
      fsT.SaveToFile "D:StuffBusinessTemptest.txt", 2 'Save binary data To disk
      End Sub
      

      Please let me know if you have any other questions.

  5. David says:

    Hi, after a long time of searching the right code for my needs I found this page. I’d like to use the code from your “Example3”. I found the solution to remove the quotation marks in one of the questions/answers here. But I have another problem with the code, when I save the file in my new saved text-file is only the text from cell A, but I also need the text from cell B in my outputted text file. How can I get that?
    Thanks for your help!
    David

    1. pedrumj says:

      Hi there

      You could use something like this:

      Sub Example3()
      Dim flag As Boolean
      Dim i As Integer
      Dim strPath As String
      strPath = Application.GetSaveAsFilename(FileFilter:= _
      "Text Files (*.txt), *.txt", Title:="Save Location")
      If strPath  "False" Then
          'open the file for writing
          Open strPath For Output As #4
      
          flag = True
          i = 1
      
          'keeps going until the end of the file is reacheed
          While flag = True
              'check if the current cell has data in it
              If Cells(i, 1)  "" Then
              'write the data to the file
                 Write #4, Cells(i, 1)
                 Write #4, Cells(i, 2)
                 'go to next cell
                 i = i + 1
              Else
                 'if the last row has been reached exit the loop
                  flag = False
              End If
          Wend
          'close the file
          Close #4
      End If
      End Sub
      

      The output lines will be as follows:

      A1
      B1
      A2
      B2
      ….

      Please let me know if you are still experiencing any trouble

  6. David says:

    Hey, I tried the code but I need to have in the output under A just the contents of A (there are a lot of cells with text in it) and when I put my comments in the same sheet under B for each cell next to A the outputted text should be the same then.

    Like this:

    A | B
    Text | Text
    Text | Text
    Text | Text

    Output:
    Text Text

    1. pedrumj says:

      Please send me your files and workbook and I will have a look at them

      1. David says:

        Hey, to which email should I send it?

  7. Pavel says:

    Hi
    Thanks for the article!
    I would like to check if there is a possibility to select a line (or several lines) with data in different cells (which corresponding to different columns) and print the selected cell’s data with additional predefined text in between the cell’s data into a text file (or buffer – which is better for me)? If several lines are selected than each line should have the same template means the same column number for each cell (when the data is printed to a text file from line to line).

    Thanks in advance.

  8. srinivasan says:

    you are a good teacher good communicator

    please publish short tutorial on all topics of vba for excel and word

  9. Emre says:

    Hello,

    your example 2 is the code which I search for but with one difference. Your code take only the first column of the excel sheet but I need more columns to export to a .txt file. I tried it but couldn’t find a solution. Do you have maybe an idea?

    And sorry for my english. I’m from germany, I hope you understood my problem. 🙂

  10. Noufer says:

    Very Useful.
    This is what I wanted

  11. Kev says:

    Hi,

    Would it be possible to make this work if you had multiple columns rather than just the one column? I’m a bit of a VBA newbie and can’t quite get my head round how to do it!

    Kev

  12. Vijay K says:

    Very useful code. specially how to get rid of the quote marking by print command and allowing user to save the text file in the user selected location.

    Regards,
    Vijay K

  13. Liza says:

    Your tutorial is very helpful but I am not sure if it covers the issue I am having. I have three functions that need to go to txt file (all separate lines). I did the error check and all is good but when I tell each one to go to the filename the only one that goes is the one with create TextFile. What code word would I use in order to send the other ones at the same time?

  14. Mostafa says:

    Hey Boss !!
    you are awesome “Example 4” very helpful for me
    i need to ask a question
    what if i need to export from Cell Number “C9:C5000: only
    your example for Column A at All
    how can i modify it
    Greetings
    Appreciate your help

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
Additional Info