need to copy few cells into notepad

[madtalent]

Good day, I'm new here, I'd like to ask if there is a way to copy certain cells into a notepad. lets say I'd like to copy the texts in cell A1 to notepad.txt inside the notepad written there is

notes in notepad. *insert text from cell A1 here* end of text.

is it possible? Thanks.

 

[Tom Urtis]

One way:

Sub AppendTextFile()
Open "C:\Your\File\Path\YourFileName.txt" For Append As #1
Print #1, Range("A1").Value
Close #1
End Sub

 

[madtalent]

You can use File Scripting as below to append to an existing text file

ie to append the value in A1 to a text fille with path "C:\temp\Test.txt"

hth

Dave

Const ForAppending = 8
Sub AppendText()
Dim objFSO
Dim objFIle
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFIle = objFSO.OpenTextFile("C:\temp\Test.txt", ForAppending)
objFIle.WriteLine [a1].Value
objFIle.Close
End Sub

One way:

Sub AppendTextFile()
Open "C:\Your\File\Path\YourFileName.txt" For Append As #1
Print #1, Range("A1").Value
Close #1
End Sub

yes it works but how to put/paste the value of A1 to a specific line inside the test.txt file? or replace a certain word/texts inside the test.txt file with the value of A1. either of them will do. Thanks for the reply.

 

[brettdj]

yes it works but how to put/paste the value of A1 to a specific line inside the test.txt file? or replace a certain word/texts inside the test.txt file with the value of A1. either of them will do. Thanks for the reply.

Its a bit more involved see below

To write A1 to line 10 you could
- read the file into an array
- split the existing file into lines
- close the file
- check if the file was longer than 10 lines, if not then
reopen the file and append A1 to the file
if longer then
- rewrite each line over the existing file , inserting line 10 at the appropriate time

Cheers

Dave

Sub AppendText()
    Dim strFile As String
    Dim objFSO
    Dim objFile
    Dim strAll
    Dim vArray
    Dim lngRow As Long
    Dim lngNew As Long
    lngNew = 10
    strFile = "C:\temp\test.txt"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile)
    strAll = objFile.ReadAll
    objFile.Close
    vArray = Split(strAll, vbNewLine)
    If lngNew > UBound(vArray) Then
        MsgBox "New line exceeds current file, line appended instead"
        Set objFile = objFSO.OpenTextFile(strFile, 8)
        objFile.WriteLine [a1].Value
    Else
        Set objFile = objFSO.createtextfile(strFile)
        For lngRow = 0 To UBound(vArray)
            If (lngNew - 1) = lngRow Then objFile.WriteLine [a1].Value
            objFile.WriteLine vArray(lngRow)
        Next
        MsgBox [a1].Value & " written to line " & lngNew
    End If
    objFile.Close
    Set objFSO = Nothing
End Sub