Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7293
  • Last Modified:

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?
0
cebu1014
Asked:
cebu1014
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
rspahitzCommented:
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now