How open notepad.exe and write text line by line via excel vba?

  • Thread starter Thread starter geniusideas
  • Start date Start date
G

geniusideas

Hi,
At the end of my code I need to open notepad.exe and write text line by line.
How to do with excel vba.
Please help. Thanks
 
geniusideas said:
At the end of my code I need to open notepad.exe and
write text line by line. How to do with excel vba.
Click to expand...

No need to run notepad.exe. See the following paradigm.

FYI: The macro writes the same line twice two different ways to demonstrate
the difference. I use the Print...myLine method in order to have complete
control over spacing.


Sub makeTextFile()
Const myDir = "C:\temp\"
Const myOutFile = "temp.txt"
Dim fnOut As Long
Dim n As Long, myLine As String, myOutPath As String

myOutPath = myDir & myOutFile

fnOut = FreeFile
Open myOutPath For Output Access Write As #fnOut

For n = 1 To 10
myLine = "Line " & n & Format(Timer(), " 0.000000")
Print #fnOut, myLine
Print #fnOut, "Line "; n; Format(Timer(), " 0.000000")
Next
n = n - 1

Close
MsgBox "done" & vbNewLine & n & " lines"
End Sub
 
Hi,
At the end of my code I need to open notepad.exe and write text line
by line. How to do with excel vba.
Please help. Thanks
Click to expand...

Expanding on joeu2004's example in another way...

Writing large amounts of text to a file is slow at best, and so I offer
the following reusable procedure that will let you write any amount of
text to a file, OR append any amount of text to an existing file.

Assemble your entire file content into a string containing line returns
for each line of text, then *dump* the string into a file in one
shot...

Sub WriteTextFileContents(TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends large amounts
' of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

...so, for example, if your text was in an array you could simply write
to file like this...

WriteTextFileContents Join(myArray, vbCrLf), myFilename

...where 'myFilename includes the full path.

To append more text to an existing file...

WriteTextFileContents Join(myArray, vbCrLf), myFilename, True

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top