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?
cebu1014Asked:
Who is Participating?
 
rspahitzConnect With a Mentor Commented:
Or another way that will create the file then open it, insert data, then save:
Sub CopyToNotepad()
    Dim iFileNumber As Integer
    
    Selection.Copy' copy the current Excel selection

    iFileNumber = FreeFile()' find the next free file identifier

   ' create the file
    Open "C:\Users\{username}\Documents\abc.txt" For Output As #iFileNumber
    Close #iFileNumber

    ' launch the file in notepad
    Shell "Notepad C:\Users\{username}\Documents\abc.txt", vbNormalFocus

   ' activate the window so we can talk to it
    AppActivate "abc.txt"

   ' send a Ctrl+V (paste) and Ctrl+S (save) to notepad...could also send an Alt-FX to exit if desired
    SendKeys "^v^s"
End Sub

Open in new window


Note that SendKeys is considered occasionally flaky and not always reliable.
0
 
nutschCommented:
It's all possible, but it's not necessarily clean. Why don't you save the csv directly without going through notepad?

Thomas
0
 
rspahitzCommented:
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.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cebu1014Author Commented:
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 .
0
 
byundtCommented:
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
0
 
rspahitzCommented:
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

0
 
cebu1014Author Commented:
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
1
 
cebu1014Author Commented:
Good Job
0
 
rspahitzCommented:
Glad it got you in the right direction.  Seems that you learned a few new things along the way too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.