How to save each row of a spreadsheet as separate text files?

%3CLINGO-SUB%20id%3D%22lingo-sub-1582516%22%20slang%3D%22en-US%22%3EHow%20to%20save%20each%20row%20of%20a%20spreadsheet%20as%20separate%20text%20files%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582516%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20%26gt%3B4000%20rows%2C%20where%20each%20row%20has%202%20columns.%26nbsp%3B%3CSPAN%3EI%20need%20separate%20txt%20files%20containing%20each%20report%20and%20hopefully%20each%20file%20under%20the%20name%20of%20the%20respective%20ID.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20that%3F%26nbsp%3BAn%20example%20of%20the%20structure%20of%20the%20data%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EID%20(number)%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EReport%20(text)%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3E1645%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EToday%20is%20a%20beautiful%20day...%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E1967%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EToday%20the%20sun%20is%20shinning%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1582516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582834%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20save%20each%20row%20of%20a%20spreadsheet%20as%20separate%20text%20files%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756300%22%20target%3D%22_blank%22%3E%40maxim545%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20SaveAsTextFile()%0A%20%20%20%20Dim%20strPath%20As%20String%0A%20%20%20%20Dim%20lngID%20As%20Long%0A%20%20%20%20Dim%20strReport%20As%20String%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20'%20Specify%20a%20different%20path%20if%20you%20wish%0A%20%20%20%20strPath%20%3D%20ThisWorkbook.Path%20%26amp%3B%20Application.PathSeparator%0A%20%20%20%20'%20Last%20row%0A%20%20%20%20m%20%3D%20Range(%22A%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20'%20Loop%20through%20the%20rows%0A%20%20%20%20For%20r%20%3D%202%20To%20m%0A%20%20%20%20%20%20%20%20'%20Get%20ID%20and%20Report%0A%20%20%20%20%20%20%20%20lngID%20%3D%20Range(%22A%22%20%26amp%3B%20r).Value%0A%20%20%20%20%20%20%20%20strReport%20%3D%20Range(%22B%22%20%26amp%3B%20r).Value%0A%20%20%20%20%20%20%20%20'%20Open%20text%20file%0A%20%20%20%20%20%20%20%20Open%20strPath%20%26amp%3B%20lngID%20%26amp%3B%20%22.txt%22%20For%20Output%20As%20%231%0A%20%20%20%20%20%20%20%20'%20Write%20data%0A%20%20%20%20%20%20%20%20Print%20%231%2C%20lngID%3B%20Tab%3B%20strReport%0A%20%20%20%20%20%20%20%20'%20Close%20the%20file%0A%20%20%20%20%20%20%20%20Close%20%231%0A%20%20%20%20Next%20r%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a spreadsheet with >4000 rows, where each row has 2 columns. I need separate txt files containing each report and hopefully each file under the name of the respective ID.

How can I do that? An example of the structure of the data:

ID (number)Report (text)
1645Today is a beautiful day... 
1967Today the sun is shinning

 

1 Reply
Highlighted

@maxim545 

Sub SaveAsTextFile()
    Dim strPath As String
    Dim lngID As Long
    Dim strReport As String
    Dim r As Long
    Dim m As Long
    ' Specify a different path if you wish
    strPath = ThisWorkbook.Path & Application.PathSeparator
    ' Last row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Loop through the rows
    For r = 2 To m
        ' Get ID and Report
        lngID = Range("A" & r).Value
        strReport = Range("B" & r).Value
        ' Open text file
        Open strPath & lngID & ".txt" For Output As #1
        ' Write data
        Print #1, lngID; Tab; strReport
        ' Close the file
        Close #1
    Next r
End Sub