- Create a folder for the text to be exported to
- Open the Excel document and click on Developer and the on Macro
- If Developer is missing, click on File, select Options and then select Customize Ribbon and check Developer and click on OK
- Enter a name for the macro and click on Create
- Copy and paste the following code in between Sub () and End Sub
- Text in red are variables that can be changed.
- Click on Run (Green play button) or press F5 to start the export.
- Make sure all the files have been exported.
Code:
Dim FNum As Integer
Dim CellValue As String
Dim FName As String
Dim Counter As Integer
Application.ScreenUpdating = False
On Error GoTo EndMacro:
For Counter = 1 To 10
Worksheets("Sheet1").Cells(Counter, 2).Select
FName = "C:\...\text export\" & Cells(Counter, 5).Text & ".txt"
FNum = FreeFile
CellValue = Cells(Counter, 7).Value
Open FName For Output Access Write As #FNum
Print #FNum, CellValue
Close #FNum
Next Counter
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
Explanation of code:
For Counter = 1 To 10
- This is the range of which rows to export. If there is a header, start with 2 and not 1.
FName = "C:\...\text export\" & Cells(Counter, 5).Text & ".txt"
- This is the path where the text files are exported to. The folder must exist for it to work.
- 5 is the column where it'll look to name the text file.
CellValue = Cells(Counter, 7).Value
- 7 is the column where the text resides.