Link to home
Start Free TrialLog in
Avatar of cebu1014
cebu1014

asked on

VBA copy from Excel to Notepad

Need some code to copy a range of cells from Excel,open up notepad and paste into it.
Anyway to have notepad save to a given csv file and save into a given folder using VBA?
Avatar of nutsch
nutsch
Flag of United States of America image

It's all possible, but it's not necessarily clean. Why don't you save the csv directly without going through notepad?

Thomas
Yes, I would suggest using VBA to simply create a new worksheet, paste the desired data into that sheet, then Save-As the sheet using CSV into the desired folder.
Avatar of cebu1014
cebu1014

ASKER

I would like to at least no how to open up notepad and to paste into it using VBA just to have the know how. There is some issues I am having otherwise when using double quotes in fields .
Avatar of byundt
If you want to learn how to open Notepad from Excel, paste data and save the resulting file, take a look at the code in this Mr. Excel thread: http://www.mrexcel.com/forum/excel-questions/46163-open-notepad-form-visual-basic-applications.html
A simple way to get started is this, but it won't save the file:

Sub CopyToNotepad()
    Shell "Notepad", vbNormalFocus
    AppActivate "Untitled - Notepad"
    SendKeys "123"
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I got it to work using your above routine as a guide. I removed some of the lines though and added  TRUE at end of sendkeys command in order to get it to work.

Sub CopyToNotepad()
   
   
     Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

 
    Shell "Notepad C:\Users\mw1\my Documents\abc.csv", vbNormalFocus


   
    SendKeys "^a", True
    SendKeys "{ENTER}", True
SendKeys ("^{HOME}"), True
SendKeys "^v", True
End Sub
Good Job
Glad it got you in the right direction.  Seems that you learned a few new things along the way too.