"s when saving to text

[a]

Hello,

I'm trying to save a large quantity of text contained in an Excel worksheet
as a text file i.e. SaveAs and then changing file type to .txt.
Unfortunately, if a cell contains a leading space, a trailing space, or any
quotation marks, the text file produced will surround that text with
quotation marks. Is there any way to prevent the quotaion marks from being
added so that it just saves the file as it is displayed in the spreadsheet?

Any help greatly appreciated
/Dom

 

[DNF Karran]

The " is caused by the cell format being text. Although I know no direc
solution in Excel, you could open the file in a text editor and use
find/replace to find the " and repace with nothing.

Dunca

 

[Bernie Deitrick]

Dom,

You can use a macro to write your values out to a file in any way that you
want. If you are OK with a macro solution, post back and I will dig up an
example.

HTH,
Bernie
MS Excel MVP

 

[a]

That would be great. I'm fine with macros. Didn't really expect it to be
possible any other way really.

Thanks, Dom

 

[Bernie Deitrick]

Dom,

The code below will do a comma delimted export to a PRN file.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dom,

Ooops, hit the wrong key combo there - I didn't mean to send that last
message incomplete. Code below. Select your range prior to running the
macro, or modify the code to act on a specific range rather than the
selection.

HTH,
Bernie
MS Excel MVP

Sub ExportToCommaDelimitedPRN()

Dim FName As String
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

FName = Application.GetSaveAsFilename(Left(ActiveWorkbook.FullName, _
Len(ActiveWorkbook.FullName) - 4) & ".prn")

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

With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol - 1
WholeLine = WholeLine & Cells(RowNdx, ColNdx).Text & ","
Next ColNdx
WholeLine = WholeLine & Cells(RowNdx, EndCol).Text
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
With Application
.ScreenUpdating = True
.DisplayAlerts = False
Close #FNum
.DisplayAlerts = True
End With
End Sub

 

[Earl Kiosterud]

a,

The Text Write Program at www.tushar-mehta.com will do it all for you. You
can instruct it to not put text qualifiers (quotes) around fields. But...

Depending on what the program that will read this file expects for field
delimiters (usually comma) and text qualifiers (usually quotes), you may not
get the correct results without the quotes.