Create text files from data rows in Excel

Question

I have a source spreadsheet in Excel with 450-or-so rows. Each row has 6 columns of data, and I need to create a separate file from each row with the filename = Column A and the contents = Columns B-G with a line break between them.

For example, I'm trying this but getting an error "File not found":

Sub DataDump()

Dim X
Dim lngRow As Long
Dim StrFolder As String

StrFolder = "/Users/danielfowler/Documents/_users_text_6.16"
X = Range([a1], Cells(Rows.Count, 2).End(xlUp))
For lngRow = 1 To UBound(X)
  Open StrFolder & "\" & X(lngRow, 1) & ".txt" For Output As #1
  Write #1, X(lngRow, 2)
  Close #1
Next
End Sub

I see a half dozen questions like this already here on StackOverflow...

But every one of these solutions returns a different error for me. I'm using Excel for Mac 2011, v14.4.2.

Brett's answer in your last linked question should work on a Mac. FilesystemObject is Windows-only. — Tim Williams, Jun 16 '14 at 21:28
I get "Path not found" error, after having plugged in my filepath. — Daniel Fowler, Jun 16 '14 at 21:32
@TimWilliams Maybe I just don't know how to use a file path on a Mac... I put in `StrFolder = "/Users/danielfowler/Documents/"` but to no avail. I also tried /Volumes/Macintosh HD/... and making all the forward slashes backslashes. The error I get now is "File not found" — Daniel Fowler, Jun 16 '14 at 21:44
Check `Application.PathSeparator` - on a Mac this is `:` vs `\\` on Windows — Tim Williams, Jun 16 '14 at 21:53
Thanks for adding the code; I've done a bit of rearranging, and reopened the question. — Andrew Barber, Jun 17 '14 at 13:26
@MarcZilla Yes - I tried it both ways. I also tried colons like Tim Williams suggested. — Daniel Fowler, Jun 18 '14 at 13:09
I don't have access to a Mac, so I can't offer any further help... — Tim Williams, Jun 18 '14 at 15:12
Hello @danfo, did you find any solution ? I'm also looking forward to make this work on a mac as I encounter the same error. Cheers. — Standaa - Remember Monica, Apr 12 '15 at 22:03
@Stanislasdrg I honestly don't remember (2 years ago). But vaguely I remember eventually just copy and pasting each one from the file into a new file. Took a few hours, but got it done. — Daniel Fowler, Sep 22 '16 at 16:31
@danfo I finally gave up with excel, preferred to workaround my issues using Google Apps Script, i.e using js. — Standaa - Remember Monica, Sep 23 '16 at 08:58

score 0 · Answer 1 · edited Jun 18 '15 at 19:48

0

Sub VBA_Print_to_a_text_file()
    Dim strFile_Path As String
    strFile_Path = "C:\temp\test.txt" ‘Change as per your test folder path
    Open strFile_Path For Output As #1
    Print #1, "This is my sample text"
    Close #1
End Sub

edited Jun 18 '15 at 19:48

josliber

43,891
12
98
133

answered Jun 18 '15 at 19:36

Pokuri Roshan

1

If this is an answer please add more details explaining the code – Ram Jun 19 '15 at 00:09

score 0 · Answer 2 · answered Jan 14 '16 at 01:23

This outputs a text file for each row with column A as the title and columns B to the last column as the content for each file. You can change the directory to whatever you want but currently it saves the text file(s) to the same directory as the Excel file. You can also change the file extension to whatever you want.

Sub toFile()

    Dim FilePath As String, CellData As String, LastCol As Long, LastRow As Long
    Dim Filenum As Integer

    LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    For i = 1 To LastRow
        FilePath = Application.DefaultFilePath & "\" & Trim(ActiveSheet.Cells(i, 1).Value) & ".xpd"
        Filenum = FreeFile

        Open FilePath For Output As Filenum
        CellData = ""

        For j = 2 To LastCol
        CellData = Trim(ActiveSheet.Cells(i, j).Value)
        Write #Filenum, CellData

        Next j

        Close #Filenum

    Next i
    MsgBox ("Done")
End Sub

As for the breaks in between each line, unfortunately I'm not experienced enough to know how to do that.

Create text files from data rows in Excel

2 Answers2