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.

Copying to a new workbook and saving as text is an acceptable method. Or this: http://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba Or this: http://www.homeandlearn.org/write_to_a_text_file.html — Tim Williams, Oct 30 '13 at 16:22
It's your code - you can impose whatever restrictions you like. — Tim Williams, Oct 30 '13 at 19:02
Why use NotePad at all?? Just use Excel to create the text file. — Gary's Student, Oct 30 '13 at 16:26
no. I need to pass the txt file to another app, and for convenient reasons, best is to stick with Notepad. — gabx, Oct 30 '13 at 18:27

user2140261 · Answer 1 · 2013-10-31T12:25:47.353

2

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:

enter image description here

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

enter image description here

edited Oct 31 '13 at 12:25

answered Oct 30 '13 at 16:43

user2140261

7,855
7
32
45

Your solution is the cleanest and understandable I have seen so far. I will try tomorrow when back at work, and eventually accepted it. I already tried it **BUT** I had a delimiter issue. It looks like the `& " " ` will do the job.TY – gabx Oct 30 '13 at 18:23
@gabx As shown in my updated answer this method does work. Something you altered has broken it. Or the range you have selected is blank. Or you tried converting the range to an array. – user2140261 Oct 31 '13 at 12:26
i may give another look. But I have found some code as shown [here](http://stackoverflow.com/questions/19708149/select-range-copy-paste-to-notepad-almost-done/19708471?iemail=1&noredirect=1#19708471) – gabx Oct 31 '13 at 17:19
My only problem with this code is if you are dealing with a lot of rows, your code has to iterate through each row to input into the stream on a single thread. While this is cleaner and easier to understand from a coding perspective, sendkeys seems to provide the best performance for large sets of data. – William Oct 07 '16 at 20:43
1

This is one of the cleanest codes I came across...!! Thanks for that! Just changed the code to read from a specific range, however, how do I get this code to paste range VERTICALLY instead of HORIZONTALLY? Cheers! – Freedox Jan 04 '18 at 12:22
2

It looks like adding vbCrLf will solve this: `oFile.Write c.Value & vbCrLf` – Freedox Jan 04 '18 at 12:27

Copy a range selection to text file

1 Answers1