Save Notepad through Excel: Help required

[MI man]

Hi,
I have an Excel sheet with repeating data in column A. I have made a unique filter of that data and placed it in column E.
I used shell function (shell "Notepad.exe") to open the Notepad
I copied the data from column E of Excel and pasted it in Notepad by using SendKeys.
(SendKeys "^V").
I want to save this notepad to a particular location, but how to do it...??
Can anybody help me...

Here is the code snippet:

Range("A1").AutoFilter
    Range("A:A").AdvancedFilter action:=xlFilterCopy, CopyToRange:=Range( _
        "E1"), Unique:=True


'Range("E1").End(xlDown).Copy
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Ntpd = Shell("Notepad.exe", vbNormalFocus)
SendKeys "^V"

 

[Trevor G]

Try creating the file first then once you have done everything else you want with the text document then you open it with the shell command.

Here is an Example.

Sub WriteToATextFile()
Dim myFile As String, retVal As String
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
myFile = "M:\Access\whateveryouwant.txt"
'set and open file for output
fnum = FreeFile()
Open myFile For Output As fnum
'write project info and then a blank line. Note the comma is required
Write #fnum, "I wrote this"
Write #fnum,

'use Print when you want the string without quotation marks
Print #fnum, "I printed this"
Close #fnum
'Use shell command
retVal = Shell("C:\WINDOWS\notepad.exe M:\Access\whateveryouwant.txt", 1)
End Sub