Copy Data from Excel to Notepad

Question

I copy data from Excel to Notepad using the SendKeys Excel VBA function.

I'm looking to avoid using the sendkeys.

I have this code:

sub test()

    dim wb as Workbook
    set wb = "C:\Documents\test.xlsx"
    wb.Sheets(2).Range("C2:C" & lRow).Copy
    myApp = Shell("Notepad.exe", vbNormalFocus)
    SendKeys "^v"
    Application.CutCopyMode = False
    wb.Sheets(2).Range("C2:C" & lRow).NumberFormat = "@"
 end sub

This only copies the data from Excel to Notepad, but after doing some corrections in the Excel file, I want the data in Notepad to be copied to Excel starting from C2.

Is there a particular reason you are using the notepad - will the text file be used somewhere else? If you are just looking for somewhere to "store" some data while you do something else then you may be better putting it into an array. — Mark.R, Feb 14 '17 at 11:37
are you trying to copy the values without the formulas? I can't think of any good reason to paste and copy from Notepad. — Slai, Feb 14 '17 at 12:42
@Slai Yes, I'm just copying the values and paste again to the excel sheet. — , Feb 15 '17 at 01:23
then just use `.PasteSpecial xlPasteValues` to paste only the values without the formulas http://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues — Slai, Feb 15 '17 at 01:27
@Slai The main reason I have to copy the values to notepad is that because the column contains `Date` and I have to convert it to text. If I directly convert the column to text and paste back the values, the cell content and value is displayed in formula bar is different. Example is `2017/01/01` in the cell and `01-Jan-2017` in the Formula bar. I would like it to be the same. Thanks. — , Feb 16 '17 at 03:06
Here is a quick method using VBA: https://stackoverflow.com/questions/24910288/leave-out-quotes-when-copying-from-cell/55399893#55399893 — JoshInDC, Mar 28 '19 at 14:49

score 9 · Answer 1 · answered Feb 14 '17 at 12:18

This is an alternative process to SendKeys:

gets values from a range of cells on a worksheet
copies to clipboard
gets the clipboard content into a string
saves that string to a temp file
opens Notepad.exe with the content of the temp file

Code:

Option Explicit

Sub OpenNotepadWithTempFileWithClipboardContent()

    Dim rngData As Range
    Dim strData As String
    Dim strTempFile As String

    ' copy some range values
    Set rngData = Sheet3.Range("B1:B5")
    rngData.Copy

    ' get the clipboard data
    ' magic code for is for early binding to MSForms.DataObject
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipBoard
        strData = .GetText
    End With

    ' write to temp file
    strTempFile = "D:\temp.txt"
    With CreateObject("Scripting.FileSystemObject")
        ' true to overwrite existing temp file
        .CreateTextFile(strTempFile, True).Write strData
    End With

    ' open notepad with tempfile
    Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide

End Sub

Thank you for your solution. It has been very helpful. Whenever possible, I would like you to explain the **`"New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"`** line. I need to know that if something else will be dumped in the temporary memory every time the code is executed and, if it is the case, will this memory be emptied when the notepad is closed? — Luiz Vaughan, Oct 23 '18 at 15:42

score 3 · Answer 2 · answered Feb 14 '17 at 11:55

You can use the file system object to write into a text file:

Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim oFile as Object
Set oFile = FSO.CreateTextFile(strPath)

oFile.WriteLine "test" 

oFile.Close
Set fso = Nothing
Set oFile = Nothing

For more info see here: https://technet.microsoft.com/en-us/library/ee198716.aspx

score 1 · Answer 3 · answered Feb 16 '17 at 13:24

A bit too late, but you can copy the data to the clipboard and paste it as text (tested and works):

Dim r As Range
Set r = wb.Sheets(2).Range("C2:C" & lRow)
r.Copy

With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    Application.CutCopyMode = False
    .PutInClipboard
End With

r.Select
r.NumberFormat = "@"
r.Worksheet.PasteSpecial "Text"

score 0 · Answer 4 · edited May 23 '17 at 11:46

0

Here is probably something that would work quite ok:

Copy this to a different sheet: wb.Sheets(2).Range("C2:C" & lRow).Copy
Save this sheet as a *.csv file: Saving excel worksheet to CSV files with filename+worksheet name using VB
Then the *.csv file is a Notepad file. :)

edited May 23 '17 at 11:46

Community

1
1

answered Feb 14 '17 at 11:51

Vityata

42,633
8
55
100

score 0 · Answer 5 · answered Feb 15 '17 at 05:12

0

    'This macros may solve your problem
    Sub SaveMySheetAsTextFile()
        Sheets("Sheet1").Select
        ActiveWorkbook.SaveAs Filename:="C:\mynotepadfile.txt", FileFormat:=xlText
    End Sub

answered Feb 15 '17 at 05:12

Jayant Kumar jain

27
6

score 0 · Answer 6 · edited Mar 28 '19 at 15:42

0

Try this:

Sub SimpleVBAWriteToFileWithoutQuotes()

        Open "c:\TEMP\Excel\out.txt" For Output As #1
        Print #1, Application.ActiveSheet.Cells(2, 3)
        Close #1

End Sub

edited Mar 28 '19 at 15:42

Til

5,150
13
26
34

answered Mar 28 '19 at 15:09

JoshInDC

31
3

Copy Data from Excel to Notepad

6 Answers6

Linked