Need Solution save as ASCII

[saturnin02]

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

 

[Michael J. Malinsky]

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]

Michael,
TX!
Let me try it out and I'll post back.
S

 

[Tom Ogilvy]

Looks like most of it came from Chip Pearson's site:

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

 

[saturnin02]

Beautiful.
Tx!!
S

Looks like most of it came from Chip Pearson's site:

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

 

[saturnin02]

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]

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]

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

 

[saturnin02]

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]

Make sure you run this one:

Public Sub DoTheExport()

You don't want to run the exporttotextfile directly.

 

[saturnin02]

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]

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

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

 

[saturnin02]

Correct.
My fault entirely.
S

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