writing data from Excel to a textfile

Question

           K               L           M
    1    Starting_Year    Code        ID
        ------------------------------------
    2    1982            ALLRIN     400200583
    3    1983            ALLRIN     000083522
    4    1983            ALLRIN     400200583

Any way to create two text files, there name will be "1982", "1983" according to the "Starting_Year", And "1982" file contains "400200583", also "1983" file contains "000083522" and "400200583".

it is what I had to try ,I am wondering if I have to use a for loop to catch the data from "Starting_Year", "ID"? Any idea, and how I can do it? Big thanks!

 Public Function CreateTextFile(FileName As String, Optional Overwrite As Boolean = True, Optional Unicode As Boolean = False) As Scripting.TextStream

    Dim oTs as Scripting.TextStream
    set oTs = CreateTextFile("W:\starting_Year.txt",True)
    oTs.Write("ID")
    oTs.close

    End Function

Please post any attempts you have made. Also try googling `copy row to another sheet based on criteria vba` then google `save sheet as text file vba`. this has been answered so many times on this site alone. — Scott Craner, Apr 11 '16 at 14:20
Please show us what you've tried. That way we can help guide you and point out any mistakes/common errors you may be making. Otherwise, @ScottCraner's got you on the right path. — BruceWayne, Apr 11 '16 at 14:22
Another option might be to use the VBA command `Write` as used in this example: http://stackoverflow.com/questions/35434220/vba-replacing-commas-in-csv-not-inside-qoutes/35440236#35440236 — Ralph, Apr 11 '16 at 14:34
Thank you for your help!! I have edited my question including the code I have tried. — Isaac.H, Apr 11 '16 at 14:50

Jeremie · Accepted Answer · 2016-04-18T14:21:20.473

1

Sub CreateTextFile() Dim ifree, iyear, i, j As Long Dim ipath As String

ipath = "C:\Users\You" 'TO UPDATE

ifree = FreeFile
i = 2
While Cells(i, 1) <> ""

    iyear = Cells(i, 1)
    ifree = FreeFile
    Open ipath & "\" & iyear & ".txt" For Output As ifree

        j = 2
        While Cells(j, 1) <> ""
            If Cells(j, 1) = iyear Then Print #ifree, Cells(j, 3)
            j = j + 1
        Wend
    Close ifree

i = i + 1
Wend
End Sub

With starting_year in Col A, Code in Col B, ID in Col C.

edited Apr 18 '16 at 14:21

answered Apr 11 '16 at 16:17

Jeremie

51
5

Thank you!! @crea it shows "Path not found" when I run it, the error comes from "Open ipath & "\" & iyear & ".txt" For Output As ifree". need to find the path in different way? – Isaac.H Apr 11 '16 at 16:41
Just set the var ipath with a real path ;): C:\Users\You – Jeremie Apr 11 '16 at 17:02

writing data from Excel to a textfile

1 Answers1