Paste data in a new notepad File, save it and close it

[Suril]

Dear All,

I want to open a notepad file from my excel file, paste a range of cells from my excel file to the current notepad file, name the notepad file same as my current file name and ultimately save it on either Desktop or the same path where my excel file exists and close the notepad file.

Would be very grateful for some light on the same.

PS: i have the code for copying the required data - once this data is copied from Excel, i use the following code:
With Application
Shell "notepad.exe", 3
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With

However, this doesnt activate the notepad file, plus I want to name this notepad and close it

 

[patel45]

Sub rangeExport()
    Application.DisplayAlerts = False
    Range("A1:D5").Copy ' change range
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    ActiveSheet.Copy
    With ActiveWorkbook.Sheets(1)
        .SaveAs "C:\test\test.txt", xlTextMSDOS ' change filename
        .Parent.Close False
    End With
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

 

[Suril]

Thanks for the code Patel. Unfortunately my workbook is going to be locked, hence this code wont be of much use. Can I do this without adding another worksheet? Also, is there a way in which I can save the notepad file on the users' desktop - be it any user.

Sub rangeExport()
    Application.DisplayAlerts = False
    Range("A1:D5").Copy ' change range
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    ActiveSheet.Copy
    With ActiveWorkbook.Sheets(1)
        .SaveAs "C:\test\test.txt", xlTextMSDOS ' change filename
        .Parent.Close False
    End With
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

 

[patel45]

I added a new sheet because I don't know if you have an empty sheet for service, suppose it is Sheet2

Sub rangeExport()
    Application.DisplayAlerts = False
    Range("A1:D5").Copy Sheets(2).Range("A1")
    Sheets(2).Copy
    With ActiveWorkbook.Sheets(1)
        .SaveAs "C:\test\test.txt", xlTextMSDOS ' change filename
        .Parent.Close False
    End With
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub