Solved

Saving as tab-deliminated text file

Posted on 2001-07-09
5
306 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
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now