Need Solution save as ASCII
 

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate file....
How do I actually get this going?
Tx,
S

"Tom Ogilvy" wrote in message
...
Looks like most of it came from Chip Pearson's site:

http://www.cpearson.com/excel/imptext.htm

--
Regards,
Tom Ogilvy


"Michael J. Malinsky" wrote in message
...
I'm sure I got the following code from somewhere in the NGs but I forget
who
wrote it (it wasn't me though so I can't take credit) The only thing
you
may want to change is the FName varable for the filename:

Public Sub ExportToTextFile()
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim FName As String
Dim Sep As String
Dim SelectionOnly As Boolean
FName = "testing.txt"
Sep = ","
SelectionOnly = True

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

"saturnin02" <saturnin02_at_hotmail.com wrote in message
...
XL 2002 Win XP HE

Hi, Is there a way to save the active sheet in a workbook (which
contains
only values) as a ASCII file with extension txt and ANSI encoding? In
other
words, a plain vanilla text file that would be usually created or read
in
NotePad, etc.

I have tried (obviously) all of the options to Save As....
None of them give me what the SIMPLE text format that I want: ASCII
file
with extension txt and ANSI encoding.

Tx a lot!!
S

Need Solution save as ASCII
 

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate file....
How do I actually get this going?
Tx,
S


--

Dave Peterson

Need Solution save as ASCII
 

Great, Tx Dave.
Let me check it out and see how it works.
Best,
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson

Need Solution save as ASCII
 

Actually Dave, THe macros don run.
I copied and pasted each one in a separate module and no go.
It asks for an "identifier" after "...Sep as String" in the:
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
What to do?
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson

Need Solution save as ASCII
 

Make sure you run this one:

Public Sub DoTheExport()

You don't want to run the exporttotextfile directly.

saturnin02 wrote:

Actually Dave, THe macros don run.
I copied and pasted each one in a separate module and no go.
It asks for an "identifier" after "...Sep as String" in the:
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
What to do?
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson


--

Dave Peterson

Need Solution save as ASCII
 

Hi Dave,
That I knew.
The problem was identified by Chip in adjacent tread.
I had a line break and fixed it and it works.
Tx for the feedback!
S
"Dave Peterson" wrote in message
...
Make sure you run this one:

Public Sub DoTheExport()

You don't want to run the exporttotextfile directly.

saturnin02 wrote:

Actually Dave, THe macros don run.
I copied and pasted each one in a separate module and no go.
It asks for an "identifier" after "...Sep as String" in the:
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
What to do?
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the
selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson


--

Dave Peterson

Need Solution save as ASCII
 

Glad you got your solution, but that's one of the problems of multiple posts for
the same question.



saturnin02 wrote:

Hi Dave,
That I knew.
The problem was identified by Chip in adjacent tread.
I had a line break and fixed it and it works.
Tx for the feedback!
S
"Dave Peterson" wrote in message
...
Make sure you run this one:

Public Sub DoTheExport()

You don't want to run the exporttotextfile directly.

saturnin02 wrote:

Actually Dave, THe macros don run.
I copied and pasted each one in a separate module and no go.
It asks for an "identifier" after "...Sep as String" in the:
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
What to do?
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the
selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Need Solution save as ASCII
 

Correct.
My fault entirely.
S
"Dave Peterson" wrote in message
...
Glad you got your solution, but that's one of the problems of multiple
posts for
the same question.



saturnin02 wrote:

Hi Dave,
That I knew.
The problem was identified by Chip in adjacent tread.
I had a line break and fixed it and it works.
Tx for the feedback!
S
"Dave Peterson" wrote in message
...
Make sure you run this one:

Public Sub DoTheExport()

You don't want to run the exporttotextfile directly.

saturnin02 wrote:

Actually Dave, THe macros don run.
I copied and pasted each one in a separate module and no go.
It asks for an "identifier" after "...Sep as String" in the:
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
What to do?
S

"Dave Peterson" wrote in message
...
If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But yeah, create a new workbook.
hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer

rightclick on your project and select Insert|module
Paste the code in.

But visit Chip's site and take a couple of procedures:

Steal this one (near the bottom)
Public Sub DoTheExport()

And this one (about halfway down):
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)



then back to excel and save that workbook with a nice name.

Then open your workbook to be exported.

Select your range (or not, it'll ask you if you want just the
selection).

then tools|macro|macros...
click on "DoTheExport" (with the workbook name prepended)

And watch the data fly!

======
Some links that accomplish the same thing as Chip's code:

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)


saturnin02 wrote:

Tom,
I am a newbie in VBA.
Do I just copy and paste the code into a module...? In a
separate
file....
How do I actually get this going?
Tx,
S


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson