Writing to Notepad from VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
NickHK,

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

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
 
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.
 
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
 
'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
 
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
 
Back
Top