Copy from Cell In Excel and copy to next available Line in Text File

Question

I have created a VBA macro to convert an excel sheet into appropriate forms and create a one cell string on my 4th worksheet. I am looking to copy that string in excel and paste it into a notepad at a predefined location. If it can paste it to the next row in the text file that would be all the better. I'm not sure if its possible to save the text file either but that would be great too.

Basically its Copy Cell A1 on sheet 4, Open Notepad, Paste in next available row, save notepad

Answer 1

Append String to Text File

The Calling Procedure (Example)

Sub Test()
    
    Const FILE_PATH As String = "C:\Test\Test.txt"
    Dim MyString As String: MyString = "Test"

    AppendString FILE_PATH, MyString
    
End Sub

The Called Procedure (Method)

Sub AppendString(FilePath As String, WriteString As String)
    Dim FileNumber As Long: FileNumber = FreeFile
    Open FilePath For Append As FileNumber
        Print #FileNumber, WriteString
    Close FileNumber
End Sub

Answer 2

To write the content of a cell to the end of a text file, use the good old combination of open, print and close commands. No need to involve cut&paste or the Notepad.

Dim file As Integer
file = FreeFile()
Open "C:\TEMP\Test1.txt" For Append As #file
Print #file, ThisWorkbook.sheets(4).Range("A1").value
Close #file

(choose the file path and name you need).

Opening a file For Append will add new data at the end of the file. If the file doesn't exist, it will be created, so you don't need to worry about that. Print will print a line of text (including the Line-End characters, so the next Print will go into a new line).

If I were you, I would create a routine for that

Sub appendTextToFile(FileName As String, txt As String)
    Dim file As Integer
    file = FreeFile()
    Open FileName For Append As #file
    Print #file, txt
    Close #file
End Sub

In your code, you just need to write

appendTextToFile "C:\temp\test1.txt", ThisWorkbook.sheets(4).Range("A1").value

Answer 3

Depending on your needs, you can do something like this. You will have to add Microsoft Scripting Runtime reference or use an object as stated in comments.

Also note that it will copy ActiveSheet A1 cell to ThisWorkbook A1 cell on 4th Worksheet.

Sub DoYourJob()
Dim sPath As String: sPath = "C:\YourPathTo\File.Txt"
Dim fso As FileSystemObject ' As Object if no reference to Microsoft Scripting Runtime

    ' Copy A1 to Worksheet 4
    ThisWorkbook.Worksheets(4).Range("A1").Value = ActiveSheet.Range("A1").Value
    
    ' Append to txt file
    Set fso = New FileSystemObject ' CreateObject("Scripting.FileSystemObject") if no reference to Microsoft Scripting Runtime
    If fso.FileExists(sPath) Then
        With fso.OpenTextFile(sPath, ForAppending)
            .WriteLine ActiveSheet.Range("A1").Value
        End With
    End If
End Sub