Solved

Saving as tab-deliminated text file

Posted on 2001-07-09
5
334 Views
Last Modified: 2008-03-10
In Excel, I'm trying to save as a tab-deliminated text file.  Which I can do, but the problem is that if a cell has any characters that it doesn't like, it puts the entire cell within quote marks.  How can I tell Excel to stop trying to "fix" what it sees as "broken"?

Thanks.
0
Comment
Question by:NetSales
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6268246
You can't when you save it to an tab-deliminated text file, Excel will put quotation marks on every field that could be split up if you try to import it again.
So try an other way to save it or tell me (us) why it must be a tab-deliminated text file, so that we possibly can advise you how to solve this with a workaround
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 6269266
I cannot find any way to prevent xl from placing Text Qualifiers around cells that have special characters that could also be seen as delimiters such as spaces, commas, tabs, linefeeds etc..
YOu could write your own custom export using the Open file method
Below is an example that exports what is in range A1 thru C4. This will create a tab delimited file without any text qualifiers (Quotes)

Sub ExportTabDelim()
    Dim r As Range
    Dim i As Integer
    Const sFileName As String = "C:\data\excel97\exptabs.txt"
    Dim hFile As Integer
    Set r = Sheets("Sheet1").Range("A1:C4")
   
    hFile = FreeFile
    Open sFileName For Output As #hFile
        For i = 1 To r.Rows.Count
            Print #hFile, r.Cells(i, 1).Value & vbTab & r.Cells(i, 2).Value & vbTab & r.Cells(i, 3).Value
        Next
    Close hFile
End Sub
0
 
LVL 6

Accepted Solution

by:
blakeh1 earned 50 total points
ID: 6269551
Here is a more portable routine, it will account for any number of columns or rows in the range. This way you ony need to change your range object. currently I have it set to grab the entire used range of the sheet (*note, using the SpecialCells(xlcelltypelastcell) as edning point may include blank cells that are only formatted). You can hard code in range, or change it to use Selection.Range and make sure to preselect the range to export.


Sub ExportTabDelim()
    Const sFileName As String = "C:\data\excel97\exptabs.txt"
    Dim r As Range
    Dim i As Long, j As Long
    Dim lngRows As Long
    Dim lngCols As Long
    Dim hFile As Integer
    On Error GoTo err_ExportTabDelim
    Set r = Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell))
   
    lngRows = r.Rows.Count
    lngCols = r.Columns.Count
   
    hFile = FreeFile
   
    Open sFileName For Output As #hFile
        For i = 1 To lngRows
            For j = 1 To lngCols
                If j = lngCols Then
                    ' Last column, so needs a new line instead
                    ' of another tab.
                    Print #hFile, r.Cells(i, j).Value
                Else
                    Print #hFile, r.Cells(i, j).Value & vbTab;
                End If
               
            Next
        Next
err_ExportTabDelim:
    ' Must make sure to close handle
    On Error Resume Next
    Close hFile
    Exit Sub
exit_ExportTabDelim:
    MsgBox "[" & Err.Number & "]: " & Err.Description, vbCritical, "ExportTabDelim"
    Resume exit_ExportTabDelim
End Sub
0
 

Expert Comment

by:costello
ID: 6415644
If no objections are made in the next 5 days, I will force accept the contribution by blakeh1.

costello
Community Support Moderator @ Experts-Exchange

P.S. This post is made as part of the general effort to clean up this topic area, meaning, looping through all the open questions with no activity since 21 days.
0
 

Expert Comment

by:ComTech
ID: 6690939
Answer accepted by:

Regards,
ComTech
Community Support Moderator
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question