Open Notepad form VBA

[raihnman]

I am sure this is easy but I need some help.

I want to:
1. Open Notepad using my VBA macro
2. Paste data from a specific Excel cell to the text file
3. Copy data from a range and paste that after the previous data in the text file.
4. Name the text file and save it in a directory.
5. Close the text file

any suggestions?

Thanks,

Raihnman

 

[stephensibert]

Have you tried something like this...
On a worksheet there appears the phrase "Well I'll be a monkey's uncle". Your user wishes to say "Well I'll be a monkey's aunt".

Option Explicit


Sub SomeCodeSomewhereInYourApp()
Call SearchAndReplace(SearchFor:="uncle", ReplaceWith:="aunt")
End Sub




Private Sub SearchAndReplace(ByVal SearchFor As String, ByVal ReplaceWith As String)
ActiveSheet.Cells.Replace _
What:=SearchFor _
, Replacement:=ReplaceWith _
, LookAt:=xlPart _
, SearchOrder:=xlByRows _
, MatchCase:=False _
, SearchFormat:=False _
, ReplaceFormat:=False
End Sub

 

[tuine]

Howdy Raihnman,

It kind of depends what you're doing, but here's an example:

Sub PrintToTextFile()

Dim FileNum As Integer, cl As Range, z As Integer, y As Integer

Dim myStr As String

FileNum = FreeFile ' next free filenumber

Open "C:\Temp\TEXTFILE.TXT" For Output As #FileNum ' creates the new file

'Open "C:\temp\TEXTFILE.TXT" For Append As #FileNum

'Print #FileNum , [a1]

z = 10
For Each cl In [b10:f30]
y = cl.Row
If y = z Then
myStr = myStr & cl & ","
'appends the input to an existing file write to the textfile
Else: Print #FileNum , myStr
z = cl.Row
myStr = "": myStr = myStr & cl & ","
End If
Next
'appends the input to an existing file write to the textfile
Print #FileNum , myStr
Close #FileNum ' close the file
End SubHope this helps. Incidentally, copy this code into Word before pasting it into your VBE module.

Hi Nate,
Thanks very much for your code. It fits perfectly for my task after a little amended.
now I got like this.

b10,c10,d10,e10,f10,
b11,c11,d11,e11,f11,
.
.
.
.
.


b27,c27,d27,e27,f27,
b28,c28,d28,e28,f28,
b29,c29,d29,e29,f29,
b30,c30,d30,e30,f30,

Now I need to remove the last comma (,) on each row. How do I amend the code?
Could you please kindly to help?
Many thanks again.

 

[ZVI]

... Now I need to remove the last comma (,) on each row. How do I amend the code? ...

In the Nate's code replace this line (two places):

      Print #FileNum, myStr

by that one:

      Print #FileNum, Left(myStr, Len(myStr) - 1)

 

[tuine]

Thanks very much ZUI,
That's so simple but I have never thought of. Instead, I tried to add more codes to remove them (it is still ok but with a lot of troubles)! haha
You save my date, ZUI.
thanks again.

 

[vicsar]

Sub CopySelectionNotepad()
With Application
Selection.Copy
Shell "Notepad.exe", 3
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With
End Sub

Great. However, this code need a small rewrite, Notepad loses focus so copied text might go somewhere else, here are my 2 cents, comments added for newbies (been there):

Sub CopySelectionNotepad()
    With Application
        Selection.Copy
        ' 3 = Window is maximized with focus.
        Shell "Notepad.exe", 3
        ' Ensure Notepad has the focus before pasting
        VBA.AppActivate "Untitled - Notepad"
        ' Paste
        SendKeys "^v"
        ' Activating Excel
        VBA.AppActivate .Caption
        ' "Freeing" memory
        .CutCopyMode = False
    End With
End Sub