URGENT - Saving Excel Sheet as ASCII Text

[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.

Tx a lot!!
S

 

[Frank Kabel]

Hi
in the SaveAs Dialog choose the appropiate format (*.txt for example)

 

[saturnin02]

Tx, for your suggestion but I have already tried it.
It doe not seem to be that simple, unfortunately.
There are plenty of different txt format and not all are compatible with
what I have mentioned in my previous post.
In, fact I have not found even ONE that is, in all of the format provided
under the Save As....
Hence, my post....
S

 

[saturnin02]

I forgot to mention that the file ALSO needs to be COMMA DELIMITED and that
the .CSV extension does not seem to create the proper format.
Tx,

 

[Frank Kabel]

Hi
the CSV format should create a delimited output. What result did you
get?

 

[saturnin02]

I do get delimited but since my CSV files are associated with excel by
default Ecel probably adds something to the value...it does not seem to be
plain vanilla text.

I tried associating my CSV files with NotePad but when I save the active
sheet as a CSV file, the values are put between quotation marks--which is
not what I want.

"ABC"
"DEF"

I need
ABC
DEF

Since I use Excel to create the values, I do not want the intermediate step
of cutting and pasting into a NotePad file and then save as .txt.

I can do it but it adds a LOT of work as this is a process that I need to
repeat very often.
I need to save from Excel into an ASCII file with extension txt and ANSI
encoding (as a result). Any ideas?

S

 

[mudraker]

Sounds like you have explored all the normal save as options.

A macro could do what you are after...


Not tested...


Sub SaveAsTxt()
Dim Ws As Worksheet
Dim r As Long
Dim c As Integer
Dim i As Integer
Dim l As Long
Dim iFree As Integer
Dim sFile As String
Dim sTxt As String

Close
Set Ws = ActiveSheet
sFile$ = Application.GetOpenFilename("Text Files (*.*),*.txt")
If sFile = False Then
End
End If
iFree = FreeFile
Open sFile For Output As #iFree

r = Ws.SpecialCells(xlLastCell).Row
c = Ws.SpecialCells(xlLastCell).Column

For l = 1 To r Step 1
sTxt$ = ""
For i = 1 To c Step 1
sTxt$ = sTxt & "," & Ws.Cells(l, i).Value
Next i
sTxt$ = Mid(sTxt, 2) ' remove 1st char which is a comma due to loo
structure
Print #iFree, sTxt
Next l
Close
End Su

 

[saturnin02]

Hi,
I could certainly try the Macro. Tx.
Since you have not tested it, meaning you have not run it yourself to check
it?
May I kindly ask where it is coming from before I cut and paste it?
Tx,
S

 

[mudraker]

I have similar code to this that i have used in some of my vba macro
placed on a normal module sheet, so I expect it to work ok.

As the code does not change anything in your excel sheet it will no
damage your data. Just it may error or not produce the output file i
exactly the format that you want

 

[saturnin02]

OK, I'll give it a shot.
Tx,
S

 

[saturnin02]

No go.
Problem at see below (PROBLEM HERE)

Sub SaveAsTxt()
Dim Ws As Worksheet
Dim r As Long
Dim c As Integer
Dim i As Integer
Dim l As Long
Dim iFree As Integer
Dim sFile As String
Dim sTxt As String

Close
Set Ws = ActiveSheet
sFile$ = Application.GetOpenFilename("Text Files (*.*),*.txt")
If sFile = False Then
End
End If
iFree = FreeFile
Open sFile For Output As #iFree

r = Ws.SpecialCells(xlLastCell).Row PROBLEM HERE
c = Ws.SpecialCells(xlLastCell).Column

For l = 1 To r Step 1
sTxt$ = ""
For i = 1 To c Step 1
sTxt$ = sTxt & "," & Ws.Cells(l, i).Value
Next i
sTxt$ = Mid(sTxt, 2) ' remove 1st char which is a comma due to loop
structure
Print #iFree, sTxt
Next l
Close
End Sub

S

 

[mudraker]

saturnin02

Looks like i should have tested it.

fixed up several errors

I tested it using a very small amount of data and now appears to be ok


Sub SaveAsTxt()
Dim Ws As Worksheet
Dim r As Long
Dim c As Integer
Dim i As Integer
Dim l As Long
Dim iFree As Integer
Dim sFile As String
Dim sTxt As String

Close
Set Ws = ActiveSheet
sFile$ = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")

If sFile = "" Then
End
End If
iFree = FreeFile
Open sFile For Output As #iFree

r = Ws.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
c = Ws.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

For l = 1 To r Step 1
sTxt$ = ""
For i = 1 To c Step 1
sTxt$ = sTxt & "," & Ws.Cells(l, i).Value
Next i
' remove 1st char which is a comma due to loop structure
sTxt$ = Mid(sTxt, 2)
Print #iFree, sTxt
Next l
Close
End Su