Hi,

I'm writing a macro using VBA in Excel and to get the output from the macro
to be written in Notepad rather than an Excel worksheet. How do I do this, if
it's at all possible?

Thanks.

Stuart,
Do you mean actually write to the Notepad app, or just write a text file ?

NickHk

"stuart" ...
Hi,

I'm writing a macro using VBA in Excel and to get the output from the
macro
to be written in Notepad rather than an Excel worksheet. How do I do this,
if
it's at all possible?

Thanks.

NickHK,

It can write to any text editor. But I only have Notepad, Wordpad and Word
on my PC.

Thanks.

"NickHK" wrote:

Stuart,
Do you mean actually write to the Notepad app, or just write a text file ?

NickHk

"stuart" ...
Hi,

I'm writing a macro using VBA in Excel and to get the output from the
macro
to be written in Notepad rather than an Excel worksheet. How do I do this,
if
it's at all possible?

Thanks.

Stuart,
What I mean, you you have to write an open text editor program directly or
is creating a text file on the system that you later open in Textpad (or
whatever) OK ?
The second option is prettyy straightforward ; check out the Open statement
in VBA help.
The first option would be more convoluted, unless it's Word.

NickHK

"stuart" ...
NickHK,

It can write to any text editor. But I only have Notepad, Wordpad and Word
on my PC.

Thanks.

"NickHK" wrote:

Stuart,
Do you mean actually write to the Notepad app, or just write a text file
?

NickHk

"stuart" ...

Hi,

I'm writing a macro using VBA in Excel and to get the output from the
macro
to be written in Notepad rather than an Excel worksheet. How do I do
this,
if
it's at all possible?

Thanks.

It can write to the text editor and after it's finished I can then see what
has been written. The editor doesn't need to be open during the task.

Thanks.

"NickHK" wrote:

Stuart,
What I mean, you you have to write an open text editor program directly or
is creating a text file on the system that you later open in Textpad (or
whatever) OK ?
The second option is prettyy straightforward ; check out the Open statement
in VBA help.
The first option would be more convoluted, unless it's Word.

NickHK

"stuart" ...
NickHK,

It can write to any text editor. But I only have Notepad, Wordpad and Word
on my PC.

Thanks.

"NickHK" wrote:

Stuart,
Do you mean actually write to the Notepad app, or just write a text file
?

NickHk

"stuart" ...

Hi,

I'm writing a macro using VBA in Excel and to get the output from the
macro
to be written in Notepad rather than an Excel worksheet. How do I do
this,
if
it's at all possible?

Thanks.

Stuart,
If the editor is not open, you can't write to it.
So, write to a file:

Dim FF As Long
FF = FreeFile
Open "C:/TextFile.txt" For Output As #FF
Print #FF, "Some Text"
'or a Value from the WS
Print #FF, Range("A1").Value
Close #FF

NickHK

"stuart" ...
It can write to the text editor and after it's finished I can then see
what
has been written. The editor doesn't need to be open during the task.

Thanks.

"NickHK" wrote:

Stuart,
What I mean, you you have to write an open text editor program directly
or
is creating a text file on the system that you later open in Textpad (or
whatever) OK ?
The second option is prettyy straightforward ; check out the Open
statement
in VBA help.
The first option would be more convoluted, unless it's Word.

NickHK

"stuart" ...

NickHK,

It can write to any text editor. But I only have Notepad, Wordpad and
Word
on my PC.

Thanks.

"NickHK" wrote:

Stuart,
Do you mean actually write to the Notepad app, or just write a text
file
?

NickHk

"stuart" ...

Hi,

I'm writing a macro using VBA in Excel and to get the output from
the
macro
to be written in Notepad rather than an Excel worksheet. How do I do
this,
if
it's at all possible?

Thanks.

'Method 1.
Public Sub Data2TxtFile1()
Dim fso, f
Dim wb As Workbook, ws As Worksheet
Dim textString As String
Const forWriting = 2
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("C:\testfile.txt", forWriting, False)
ws.Activate
textString = ws.Cells(1, 1).Value
f.WriteLine textString
f.Close
Set f = Nothing
Set fso = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub

'Method 2.
Public Sub Data2TxtFile2()
Dim wb As Workbook, ws As Worksheet
Dim textString As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
ws.Activate
textString = ws.Cells(2, 1).Value
Open "C:\testfile.txt" For Append As #1
Write #1, textString
Close #1
Set ws = Nothing
Set wb = Nothing
End Sub




"stuart" schreef in bericht
...
It can write to the text editor and after it's finished I can then see
what
has been written. The editor doesn't need to be open during the task.

Thanks.

"NickHK" wrote:

Stuart,
What I mean, you you have to write an open text editor program directly
or
is creating a text file on the system that you later open in Textpad (or
whatever) OK ?
The second option is prettyy straightforward ; check out the Open
statement
in VBA help.
The first option would be more convoluted, unless it's Word.

NickHK

"stuart"
...
NickHK,

It can write to any text editor. But I only have Notepad, Wordpad and
Word
on my PC.

Thanks.

"NickHK" wrote:

Stuart,
Do you mean actually write to the Notepad app, or just write a text
file
?

NickHk

"stuart"
...

Hi,

I'm writing a macro using VBA in Excel and to get the output from
the
macro
to be written in Notepad rather than an Excel worksheet. How do I do
this,
if
it's at all possible?

Thanks.

Both writing a text file and opening that text file are quite simple:

To write to text:

Sub StringToTextFile(ByVal txtFile As String, _
ByVal strString As String)

Dim hFile As Long

hFile = FreeFile

Open txtFile For Output As hFile
'Write #hFile, strString 'this will make start- and end quotes
Print #hFile, strString;
Close #hFile

End Sub


To open the text file:

Sub OpenAsText(strPath As String)

Dim lRetVal As Long 'can do without this

On Error GoTo CANTOPEN

If bFileExists(strPath) Then
lRetVal = Shell("write" & " " & """" & strPath & """", 1)
Else
MsgBox "There is no valid file path supplied", , "opening text file"
End If

Exit Sub
CANTOPEN:

MsgBox "Could not open" & _
vbCrLf & vbCrLf & _
strPath, , "opening text file"

End Sub

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS


"stuart" wrote in message
...
Hi,

I'm writing a macro using VBA in Excel and to get the output from the
macro
to be written in Notepad rather than an Excel worksheet. How do I do this,
if
it's at all possible?

Thanks.