Copy a range selection to text file

Question

I can not find a clean answer about copy/paste a range from Excel to a Notepad text file. What I tried ( a For Each Cell) does not give me the desired output (everything is in one column).

This following code works well :

With Application
Selection.Copy
Shell "notepad.exe", 3
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With

But I would like to save the file in a dedicated path and not having to deal manually with Notepad. My cells to copy are text, and the output has to be delimited with blancks and respects the rows & columns.

I found solutions with copying the selection to another Woksheet, then saving it as text, but it sound to me rather hasardous for my workbook sanaty or heavy. My question can seems stupid but I really can't find a clean answer, and most important, a code I will understand.

Answer 1

Sub writeCells()
Dim strPath As String
Dim strName As String
Dim FSO As Object
Dim oFile As Object
Dim c As Range

strName = "YourFileName.txt"
strPath = "C:\Your\Path\"

Set FSO = CreateObject("Scripting.FileSystemObject")


Set oFile = FSO.CreateTextFile(strPath & strName)

For Each c In Selection
    oFile.Write c.Value & " "
Next c

oFile.Close

End Sub

In this example A1:B10 is selected.

so starting with a blank file:

After running this macro and reopen the file I end up as below: