Hi - I have a some text in A1, formatted using vbLF, that I need to save. The task is to:
[1] open Notepad
[2] copy content of A1 in to Notepad
[3] save Notepad using title that is in B1
I already have the path to the location of the Notepad file. I just need to append the title.
(I do not wish to copy the worksheet)
Thanks in advance!
You don't have to go through Notepad to save the value as a text file. You can
write directly to that text file.
With no validation at all (to make sure B1 is a good file name):
Option Explicit
Sub testme()
Dim MyFileName As String
Dim myStr As String
Dim FileNum As Long
With Worksheets("sheet1")
myStr = .Range("a1").Value
'maybe...
myStr = Application.Substitute(myStr, vbLf, vbCrLf)
MyFileName = .Range("b1").Value
End With
FileNum = FreeFile
Close FileNum
Open MyFileName For Output As FileNum
Print #FileNum, myStr
Close FileNum
End Sub
I put: c:\myfile.txt
in B1
elrussell wrote:
>
> Hi - I have a some text in A1, formatted using vbLF, that I need to
> save. The task is to:
> [1] open Notepad
> [2] copy content of A1 in to Notepad
> [3] save Notepad using title that is in B1
>
> I already have the path to the location of the Notepad file. I just
> need to append the title.
>
> (I do not wish to copy the worksheet)
>
> Thanks in advance!
>
> --
> elrussell
> ------------------------------------------------------------------------
> elrussell's Profile: http://www.excelforum.com/member.php...o&userid=31020
> View this thread: http://www.excelforum.com/showthread...hreadid=533159
--
Dave Peterson
You don't need to go through notpad
Sub WriteFile()
Dim s As String, s1 As String
Dim ff As Long
ff = FreeFile
s = Range("A1").Value
Open Range("B1").Value & ".txt" For Output As #ff
For i = 1 To Len(s)
sChr = Mid(s, i, 1)
If sChr = vbLf Then
Print #ff, s1
s1 = ""
Else
s1 = s1 & sChr
End If
Next
If Len(s1) > 0 Then
Print #ff, s1
End If
Close #ff
End Sub
I don't know what is in B1, so you might need to adjust this to account for
path and extension (if it already has an extension)
--
Regards,
Tom Ogilvy
"elrussell" <[email protected]> wrote
in message news:[email protected]...
>
> Hi - I have a some text in A1, formatted using vbLF, that I need to
> save. The task is to:
> [1] open Notepad
> [2] copy content of A1 in to Notepad
> [3] save Notepad using title that is in B1
>
> I already have the path to the location of the Notepad file. I just
> need to append the title.
>
> (I do not wish to copy the worksheet)
>
> Thanks in advance!
>
>
> --
> elrussell
> ------------------------------------------------------------------------
> elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
> View this thread: http://www.excelforum.com/showthread...hreadid=533159
>
I tried it that way and it didn't produce satisfactory results when looked
at in Notepad. The results were all displayed on one line.
--
Regards,
Tom Ogilvy
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You don't have to go through Notepad to save the value as a text file.
You can
> write directly to that text file.
>
> With no validation at all (to make sure B1 is a good file name):
>
> Option Explicit
> Sub testme()
> Dim MyFileName As String
> Dim myStr As String
> Dim FileNum As Long
>
> With Worksheets("sheet1")
> myStr = .Range("a1").Value
> 'maybe...
> myStr = Application.Substitute(myStr, vbLf, vbCrLf)
> MyFileName = .Range("b1").Value
> End With
>
> FileNum = FreeFile
> Close FileNum
> Open MyFileName For Output As FileNum
> Print #FileNum, myStr
> Close FileNum
>
> End Sub
>
> I put: c:\myfile.txt
> in B1
>
>
>
> elrussell wrote:
> >
> > Hi - I have a some text in A1, formatted using vbLF, that I need to
> > save. The task is to:
> > [1] open Notepad
> > [2] copy content of A1 in to Notepad
> > [3] save Notepad using title that is in B1
> >
> > I already have the path to the location of the Notepad file. I just
> > need to append the title.
> >
> > (I do not wish to copy the worksheet)
> >
> > Thanks in advance!
> >
> > --
> > elrussell
> > ------------------------------------------------------------------------
> > elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
> > View this thread:
http://www.excelforum.com/showthread...hreadid=533159
>
> --
>
> Dave Peterson
I used UltraEdit to view it, but I just tried opening the text file in Notepad
and it looked ok.
Hmmm.
(Windows XP Home Notepad????)
Tom Ogilvy wrote:
>
> I tried it that way and it didn't produce satisfactory results when looked
> at in Notepad. The results were all displayed on one line.
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You don't have to go through Notepad to save the value as a text file.
> You can
> > write directly to that text file.
> >
> > With no validation at all (to make sure B1 is a good file name):
> >
> > Option Explicit
> > Sub testme()
> > Dim MyFileName As String
> > Dim myStr As String
> > Dim FileNum As Long
> >
> > With Worksheets("sheet1")
> > myStr = .Range("a1").Value
> > 'maybe...
> > myStr = Application.Substitute(myStr, vbLf, vbCrLf)
> > MyFileName = .Range("b1").Value
> > End With
> >
> > FileNum = FreeFile
> > Close FileNum
> > Open MyFileName For Output As FileNum
> > Print #FileNum, myStr
> > Close FileNum
> >
> > End Sub
> >
> > I put: c:\myfile.txt
> > in B1
> >
> >
> >
> > elrussell wrote:
> > >
> > > Hi - I have a some text in A1, formatted using vbLF, that I need to
> > > save. The task is to:
> > > [1] open Notepad
> > > [2] copy content of A1 in to Notepad
> > > [3] save Notepad using title that is in B1
> > >
> > > I already have the path to the location of the Notepad file. I just
> > > need to append the title.
> > >
> > > (I do not wish to copy the worksheet)
> > >
> > > Thanks in advance!
> > >
> > > --
> > > elrussell
> > > ------------------------------------------------------------------------
> > > elrussell's Profile:
> http://www.excelforum.com/member.php...o&userid=31020
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=533159
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
Here's a function I got from Francesco Balena that you could try. It takes
two arguments, the text and the filename. I use this a lot in a cnc program
file manager add-in that reads/writes partial or entire lines, blocks,
...including find & replace text. <requires separate read function> The add-in
happens to use Notepad as a file editor, so I know the text displays exactly
as desired, -with no surprises.
Function WriteFileContents(vText As Variant, szFileName As String)
' Writes the edited contents back to the file
' This function actually creates a new file with new text, overwriting the
original file
Const sSource As String = "WriteFileContents()"
Dim iNum As Integer, bOpen As Boolean
On Error GoTo ErrHandler
iNum = FreeFile() 'Get the next file number
'Read the entire file
Open szFileName For Binary As #iNum
bOpen = True 'If we got here the file opened without error
Put #iNum, , vText 'Dump the contents into the file
ErrHandler:
'We're done with the file so close it
If bOpen Then Close #iNum
End Function
HTH
Regards,
Garry
Yep. It is working for me now (and yours worked). I guess I had the file
open or something.
--
Regards,
Tom Ogilvy
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I used UltraEdit to view it, but I just tried opening the text file in
Notepad
> and it looked ok.
>
> Hmmm.
>
> (Windows XP Home Notepad????)
>
> Tom Ogilvy wrote:
> >
> > I tried it that way and it didn't produce satisfactory results when
looked
> > at in Notepad. The results were all displayed on one line.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > You don't have to go through Notepad to save the value as a text file.
> > You can
> > > write directly to that text file.
> > >
> > > With no validation at all (to make sure B1 is a good file name):
> > >
> > > Option Explicit
> > > Sub testme()
> > > Dim MyFileName As String
> > > Dim myStr As String
> > > Dim FileNum As Long
> > >
> > > With Worksheets("sheet1")
> > > myStr = .Range("a1").Value
> > > 'maybe...
> > > myStr = Application.Substitute(myStr, vbLf, vbCrLf)
> > > MyFileName = .Range("b1").Value
> > > End With
> > >
> > > FileNum = FreeFile
> > > Close FileNum
> > > Open MyFileName For Output As FileNum
> > > Print #FileNum, myStr
> > > Close FileNum
> > >
> > > End Sub
> > >
> > > I put: c:\myfile.txt
> > > in B1
> > >
> > >
> > >
> > > elrussell wrote:
> > > >
> > > > Hi - I have a some text in A1, formatted using vbLF, that I need to
> > > > save. The task is to:
> > > > [1] open Notepad
> > > > [2] copy content of A1 in to Notepad
> > > > [3] save Notepad using title that is in B1
> > > >
> > > > I already have the path to the location of the Notepad file. I just
> > > > need to append the title.
> > > >
> > > > (I do not wish to copy the worksheet)
> > > >
> > > > Thanks in advance!
> > > >
> > > > --
> > > > elrussell
> > >
> ------------------------------------------------------------------------
> > > > elrussell's Profile:
> > http://www.excelforum.com/member.php...o&userid=31020
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=533159
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson
I'm not sure about the second leading comment being true. It used to be when
I did it similar to what Dave and Tom have suggested, but I switched to this
technique to preserve the existing file structure. (The add-in uses file
properties to store info about the file) It doesn't replace an existing file
with a new one anymore. It does replace the contents with the new text. It
also creates a new file if it doesn't exist.
Regards,
Garry
Tom, Garry, and Dave - thanks! It Works like a charm. I enjoyed considering and learning from the different 'spins'on this question.
After reviewing the project I copied the previously posted function from, I
realized it had been modified specifically to work for that project.
Unfortunately I did not revise the leading comments accordingly. That said,
the function will not work for you in the context you need here.
Dave and Tom both have posted the right (and proper) solution.
I did find the proper function and have included it here, along with a
function to read file contents. My intent here is to share an alternative to
hard-coding your procedure by simply calling these "drop-in" functions as
needed. Otherwise, there's really no difference from what Dave and Tom have
posted.
I sincerely apologize for the screw up!
Function CreateTextFile(szText As String, szFileName As String)
' Creates a plain text file.
' Replaces an existing file if it exists.
Dim iNum As Integer, bOpen As Boolean
On Error GoTo ErrHandler
iNum = FreeFile() 'Get the next file number
'Create/Open the file
Open szFileName For Output As #iNum
bOpen = True 'If we got here the file opened without error
Print #iNum, Trim(szText) 'Write the contents into the file
ErrHandler:
'We're done with the file so close it
If bOpen Then Close #iNum
End Function
Function szReadFileContents(szFileName As String) As String
' Reads the entire file contents into an array
Dim iNum As Integer, bOpen As Boolean
On Error GoTo ErrHandler
iNum = FreeFile() 'Get the next file number
'Read the entire file
Open szFileName For Input As #iNum
'If we got here the file opened without error
bOpen = True
szReadFileContents = Input(LOF(1), iNum)
ErrHandler:
'We're done with the file so close it
If bOpen Then Close #iNum
End Function
Regards,
Garry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks