Saving to a text file on close

[PeterOz]

Hi
I have found part of what I need on this site but can't work out how to finish it properly.
I have a list in column A which will grow. It is Just text. What I would like to do is on close have it create a plain text file - Not csv.
The code I have found works for data in More then 1 column and 10 rows.
I have been trying to have it save what is in column A (this is the only column with data) If I Change A1 :C10 to 1:1 I only get the first cell.
If I put A1:A2500 I get blank at the end of the text file. Also if I go past row 2500 it will not give the extra text unless the code is updated.
Using the above A1 C10 it will work if I change a name save then close it produces the text file with the changed name.
The other part I have not got quite right is the Close. It seems I need the code in twice.
If I put the save code in the Sub Before Close only it will not work.
If I put it as a new sub after the Before close it will not work.
When I have it in both it works as needed except I do not get all of column A. (I know the range is A1 to C10)
I am using office 2007

Cheers
Peter

 

[HighAndWilder]

Try this. Put it in the ThisWorkbook code module.

Change the following line to include the name of the text file.

strFileName = ActiveWorkbook.Path & "\Exported.txt"

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ws As Worksheet
Dim rng As Range
Dim strText As String
Dim strFileName As String
Dim rngData As Range

    ' Set worksheet object.
    Set Ws = ActiveSheet
        
    ' Set range for column of data.
    Set rngData = Ws.Range("A2").Resize(Ws.Cells(Rows.Count, 1).End(xlUp).Row - 1, 1)
    
    ' Loop through data range.
    For Each rng In rngData.Cells
        strText = strText & IIf(rng.Row > 2, vbCrLf, "") & rng.Value
    Next rng
    
    ' Assign filename.
    strFileName = ActiveWorkbook.Path & "\Exported.txt"
    
    ' Delete text file if it already exists.
    If Dir(strFileName) <> "" Then
        Kill strFileName
    End If
    
    ' Open file and save data.
    Open strFileName For Output As #1
    Print #1, strText
    Close #1
    
    ' Confirm file saved.
    MsgBox "File Saved", vbInformation, "Confirmation"

End Sub

 

[PeterOz]

Thank you HighAndWider
This works perfect. It is different to what I started with so I will be interested in dissecting the code
I also included a sub that on open it inserts a blank row. - It works.
I might see if I can add an auto sort after the new line has been added
I have done the auto sort with tables and it works See how I go with this
Cheers and many thanks
Peter

 

[HighAndWilder]

I'm pleased that it works for you.