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

 

[NateO]

Howdy Raihnman,

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

<pre>Sub PrintToTextFile()
<FONT COLOR="#00007F">Dim</FONT> FileNum <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Integer</FONT>, cl <FONT COLOR="#00007F">As</FONT> Range, z <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Integer</FONT>, y <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Integer</FONT>
<FONT COLOR="#00007F">Dim</FONT> myStr <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>
FileNum = FreeFile <FONT COLOR="#007F00">' next free filenumber</FONT>
<FONT COLOR="#007F00">'Open "C:\Temp\TEXTFILE.TXT" For Output As #FileNum ' creates the new file</FONT>
<FONT COLOR="#00007F">Open</FONT> "C:\temp\TEXTFILE.TXT" <FONT COLOR="#00007F">For</FONT> <FONT COLOR="#00007F">Append</FONT> <FONT COLOR="#00007F">As</FONT> #FileNum
<FONT COLOR="#00007F">Print</FONT> #FileNum, [a1]
z = 10
<FONT COLOR="#00007F">For</FONT> <FONT COLOR="#00007F">Each</FONT> cl <FONT COLOR="#00007F">In</FONT> [b10:f30]
y = cl.Row
<FONT COLOR="#00007F">If</FONT> y = z <FONT COLOR="#00007F">Then</FONT>
myStr = myStr & "|" & cl
<FONT COLOR="#007F00"><FONT COLOR="#007F00"><FONT COLOR="#007F00">'appends the input to an existing file write to the textfile</FONT></FONT></FONT>
Else: <FONT COLOR="#00007F">Print</FONT> #FileNum, myStr
z = cl.Row
myStr = "": myStr = myStr & "|" & cl
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">If</FONT>
<FONT COLOR="#00007F">Next</FONT>
<FONT COLOR="#007F00"><FONT COLOR="#007F00"><FONT COLOR="#007F00">'appends the input to an existing file write to the textfile</FONT></FONT></FONT>
<FONT COLOR="#00007F">Print</FONT> #FileNum, myStr
<FONT COLOR="#00007F">Close</FONT> #FileNum <FONT COLOR="#007F00">' close the file</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT></pre>

Hope this helps. Incidentally, copy this code into Word before pasting it into your VBE module.

 

[raihnman]

Thanks NateO, I will give it a try.

Raihnman

 

[iask]

can this code be changed to copy selected data instead of fixed range A1:B10.

thanks,

iask

 

[Tom Urtis]

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

 

[iask]

let me be precise of what I need if I may,
copy some data from a column then transfer these data to notepad,
open a pop up message to name and date the notepad file to be saved inside a folder.

thanks,

iask

 

[iask]

I need help making a macro

1. find a the word "script" in the first row
2. copy from cell 2 to cell 6
3. Open Notepad using my VBA macro
4. Paste copied data from Excel cell to notepad
5. Name the text file and save it in a directory.
6. Close the text file

no idea how to even start it!

Thanks,

iask

 

[Dossfm0q]

' Try This

Option Explicit
Sub notepad()
Application.ScreenUpdating = False
Dim FF As Integer
Dim plik As String
Dim tekst As String
Dim kom As Range
Dim intResult As Variant
plik = ThisWorkbook.Path & "\webpage1.txt"
If FileOrDirExists(plik) Then
Kill plik
MsgBox "This File is Exists"
'Application.SendKeys "%{F4}", True 'close
Else
FF = FreeFile
Open plik For Output As #FF
For Each kom In Sheets(1).Range("A1:B16")
tekst = kom.Text
Print #FF, tekst
Next
Close #FF
intResult = Shell("Notepad.exe " & plik, vbNormalFocus)
Application.Wait
Application.SendKeys "%{F4}", True 'close
'or CloseAPP_B "Notepad.exe"
End If
Application.ScreenUpdating = True
End Sub
Function FileOrDirExists(PathName As String) As Boolean
 
Dim iTemp As Integer
 
On Error Resume Next
iTemp = GetAttr(PathName)
 
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
 
On Error GoTo 0
End Function
'**************************************
Sub KillTest()
MsgBox IIf(CloseAPP("notepad.exe", _
True, False), _
"Killed", "Failed")
End Sub
'**************************************
Sub KillTest_B()
CloseAPP_B "notepad.exe"
End Sub
'Close Application
'CloseApp KillAll=False -Only first occurrence
' KillAll=True -All occurrences
' NeedYesNo=True -Prompt to kill
' NeedYesNo=False -Silent kill
Private Function CloseAPP _
( _
AppNameOfExp _
As String, _
Optional _
KillAll _
As Boolean = False, _
Optional _
NeedYesNo _
As Boolean = True _
) _
As Boolean
Dim oProcList As Object
Dim oWMI As Object
Dim oProc As Object
CloseAPP = False
' step 1: create WMI object instance:
Set oWMI = GetObject("winmgmts:")
If IsNull(oWMI) = False Then
' step 2: create object collection of Win32 processes:
Set oProcList = oWMI.InstancesOf("win32_process")
' step 3: iterate through the enumerated collection:
For Each oProc In oProcList
'MsgBox oProc.Name
' option to close a process:
If UCase(oProc.Name) = UCase(AppNameOfExp) Then
If NeedYesNo Then
If MsgBox("Kill " & _
oProc.Name & vbNewLine & _
"Are you sure?", _
vbYesNo + vbCritical) _
= vbYes Then
oProc.Terminate (0)
'no test to see if this is really true
CloseAPP = True
End If 'MsgBox("Kill "
Else 'NeedYesNo
oProc.Terminate (0)
'no test to see if this is really true
CloseAPP = True
End If 'NeedYesNo
'continue search for more???
If Not KillAll And CloseAPP Then
Exit For 'oProc In oProcList
End If 'Not KillAll And CloseAPP
End If 'IsNull(oWMI) = False
Next 'oProc In oProcList
Else 'IsNull(oWMI) = False
'report error
End If 'IsNull(oWMI) = False
' step 4: close log file; clear out the objects:
Set oProcList = Nothing
Set oWMI = Nothing
End Function
'**************************************
'No frills killer
Private Function CloseAPP_B(AppNameOfExp As String)
Dim oProcList As Object
Dim oWMI As Object
Dim oProc As Object
' step 1: create WMI object instance:
Set oWMI = GetObject("winmgmts:")
If IsNull(oWMI) = False Then
' step 2: create object collection of Win32 processes:
Set oProcList = oWMI.InstancesOf("win32_process")
' step 3: iterate through the enumerated collection:
For Each oProc In oProcList
' option to close a process:
If UCase(oProc.Name) = UCase(AppNameOfExp) Then
oProc.Terminate (0)
End If 'IsNull(oWMI) = False
Next
End If
End Function

 

[Daviadio]

Great coding..

If I wanted to save the notebook file as a UTF-8 format; how would I do it ??

Thanks David

 

[Phil the Forecaster]

Brilliant code! Certainly something to learn from!
"Open plik For Output As #FF
For Each kom In Sheets(1).Range("A1:B16")
tekst = kom.Text
Print #FF, tekst
Next
Close #FF"
If I could replace the section of the code above to do a Find: Textstring1 and Replace: Textstring2 which I have in another Excelworkbook String... I would be very, very happy indeed!

I posted a Thread a couple of days describing what I was doing. I do hope you can help!