Save Multiple Cell Contents as Indiviudal Text Files

[ExcelMoMan]

Hello -

I have a spreadsheet that contains 2400 rows and 2 columns. Column A has client notes that need to be saved as a text file (all 2400 rows need to be saved individually as a txt file), and Column B contains the name of the text file I would like. I worked on creating a macro for this and got to a good place and was able to export the first row, but when I tried to test it on the second row, it didn't work and now it won't work on the first row anymore either. I now get a VB Error: Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed (Again, this worked fine before)

Does anyone know of any reason it would have stopped working? Additionally, I need help to automate this so that I can run the macro on all 2400 rows with one run of the macro. I am on a Mac, and am using Excel 2016. Here is the macro I used. When it worked, I got exactly what I needed for one row. I just need it to work for all rows, any help is appreciated.

Sub CopyToNew()

Application.DisplayAlerts = False

Dim Path As String

Path = "/Users/NAME/Desktop/Data/"


    Range("A2:B2").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    
    ActiveWorkbook.SaveAs filename:=Path & Range("B1") & ".txt", FileFormat:=xlTextPrinter

'Get rid of filename column as it is not needed.
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

'Save New Textfile
    ActiveWorkbook.Save
    ActiveWorkbook.Close

End Sub

 

[JLGWhiz]

This will do all of them at one operation. Check your directory path. Your original code as posted used forward slack as subdirectory separators, whereas the normal separator would be a backslash which I used in this code. Make change as deemed necessary for your needs.

Sub CopyToNew()
Dim c As Range, fPath As String, fName As String
fPath = "\Users\NAME\Desktop\Data\" 'Verify his directory, original used forward slash for separator.
    For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        If c.Offset(, 1) <> "" Then
            fName = c.Offset(, 1).Value & ".txt"
            Worbooks.Add
            c.Copy ActiveWorkbook.Sheets(1).Range("A1")
            ActiveWorkbook.SaveAs fPath & fName, FileFormat:=xlTextPrinter
            c.Offset(, 1).ClearContents
            ActiveWorkbook.Close False
        End If
    Next
End Sub

Your original code did not run the second time because you did not change cell and column references in all places in the code.

 

[ExcelMoMan]

Hi JLGWhiz -

Thanks for the assistance. The filepath stuff actually came from recording a macro - it worked for a bit then didn't... however, I was able to get onto a windows machine, and my original code worked fine (Once I update filepath stuff to windows syntax). So I think I am good on that issue.

Thanks to your code below I was able to export all the files! (One note, there is a typo on Workbooks.add below, it is just missing the "k" in "work").

I really appreciate the help, this will make it so much easier for me!

This will do all of them at one operation. Check your directory path. Your original code as posted used forward slack as subdirectory separators, whereas the normal separator would be a backslash which I used in this code. Make change as deemed necessary for your needs.

Sub CopyToNew()
Dim c As Range, fPath As String, fName As String
fPath = "\Users\NAME\Desktop\Data\" 'Verify his directory, original used forward slash for separator.
    For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
        If c.Offset(, 1) <> "" Then
            fName = c.Offset(, 1).Value & ".txt"
            Worbooks.Add
            c.Copy ActiveWorkbook.Sheets(1).Range("A1")
            ActiveWorkbook.SaveAs fPath & fName, FileFormat:=xlTextPrinter
            c.Offset(, 1).ClearContents
            ActiveWorkbook.Close False
        End If
    Next
End Sub

Your original code did not run the second time because you did not change cell and column references in all places in the code.

 

[JLGWhiz]

Hi JLGWhiz -

Thanks for the assistance. The filepath stuff actually came from recording a macro - it worked for a bit then didn't... however, I was able to get onto a windows machine, and my original code worked fine (Once I update filepath stuff to windows syntax). So I think I am good on that issue.

Thanks to your code below I was able to export all the files! (One note, there is a typo on Workbooks.add below, it is just missing the "k" in "work").

I really appreciate the help, this will make it so much easier for me!

you're welcome, and thanks for the feedback.
regards, JLG