Solved

Saving as tab-deliminated text file

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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 viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

685 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