Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to copy only selected cells in excel to notepad in same format

0

I want VBA to copy only cells selected in excel to notepad in same format like if there is space between selection then space should appear in notepad & one below the other.

Along with this i want the name "THE END" to appear on the next line of notepad.

Path for notepad should be to save on desktop with file name XXX.txt if any file name with similar name already on desktop then overwrite it.

Output photo is attached.

Thanks for yor time & consideration.

E.g.

A1= Apple

A2=Ball

A4=Cat

then output in notepad should look like

Apple

Ball  

Cat

THE END

Answer
Discuss

Answers

0

EDIT (earlier macros removed)

(next time, include the code that you are currently working with from the start)

Based on your comment, here is the edited version of the macro that should do what you want.

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:\path\"

Set FSO = CreateObject("Scripting.FileSystemObject")


Set oFile = FSO.CreateTextFile(strPath & strName)

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

oFile.Write "THE END"

oFile.Close

End Sub
Discuss

Discussion

What you are doing I already did means searching on the net so the closest is this but it doesnot gives me same format (like one below the other as in multiple cells selection A1 to A4) & THE END in the last line is not included in that code.

https://stackoverflow.com/questions/19688269/copy-a-range-selection-to-text-file
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
AlexR Nov 20, '17 at 5:12 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login