Copy a range of values into an external .txt line VBA

Question

I have an Excel worksheet and I need to copy a range of values into an external .txt file at a given line. (E.g. copy the values of cells A1:A7, and paste them in line 100 of a pre-existing .txt file. I was able to select the range of values, however I can not paste into a specific line. Any ideas?

Answer 1

This is a hack:

Public Const FPath = "C:\Temp\YourFile.txt"

Sub test()
Dim fso As New Scripting.FileSystemObject
Dim txtFile As Scripting.TextStream

Dim val As String
Dim valArr() As Variant, v As Variant
valArr = Sheet1.Range("A1:A7").Value2

Dim count As Long
For count = LBound(valArr) To UBound(valArr)
    'choose your delimiter, I used space
    val = val & IIf(count = LBound(valArr), "", " ") & valArr(count, 1)
Next count

Set txtFile = fso.OpenTextFile(FPath, ForReading, False)

Dim txtArr() As String
txtArr = Split(txtFile.ReadAll, vbCrLf)
txtFile.Close

If UBound(txtArr) >= 4 Then 'I am using 5 instead of 100 - change as necessary
    txtArr(4) = val
    're-write file
    Set txtFile = fso.OpenTextFile(FPath, ForWriting, False)
    txtFile.Write Join(txtArr, vbCrLf)
    txtFile.Close
Else
    'append to file if it has less then 5 lines
    Set txtFile = fso.OpenTextFile(FPath, ForAppending, False)
    txtFile.Write val
    txtFile.Close
End If

Set txtFile = Nothing
End Sub