CODEWITHSUNDEEP
excelvbanotepad
user6632933
user6632933

Reputation:

Copy Data from Excel to Notepad

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.

Upvotes: 3

Views: 37363

Answers (6)

JoshInDC
JoshInDC

Reputation: 31

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

Upvotes: 0

Slai
Slai

Reputation: 22876

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"

Upvotes: 1

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

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

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

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

Upvotes: 9

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 3

Vityata
Vityata

Reputation: 43585

Here is probably something that would work quite ok:

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

Upvotes: 0

Related Questions