Solved

Saving as tab-deliminated text file

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

947 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

21 Experts available now in Live!

Get 1:1 Help Now