Excel: Macro to Save Data as Tab-Delimited File without Quotes

 Last reviewed: June 30, 1997
Article ID: Q104997

The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

In Microsoft Excel, to save a selection of data as a tab-delimited text file, select the Text file format in the Save File As Type box in the Save As dialog box. When you save a file in this format, if any of the cells in the spreadsheet contain commas, quotation marks, or other characters that could be misinterpreted when you reopen the file, Microsoft Excel encloses those values in quotation marks.

If you want to create a tab-delimited text file and you do not want these additional quotation marks to be added, you must use a macro to save the data to a text file.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided as is and Microsoft in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements.

To create a tab-delimited text file without enclosing text strings in quotation marks:

  1. In a new macro sheet, type the following: 

          A1: TextFile Macro
      A2: =SET.NAME("nextcol",0)
      A3: getname=FOPEN(INPUT("Filename:",2),3)
      A4: =SET.NAME("colcount",COLUMNS(SELECTION()))
      A5: =FOR.CELL("current",SELECTION())
      A6: =IF(AND(ISNUMBER(current),GET.CELL(7,current)<>"General"))
      A7: =FWRITE(getname,TEXT(current,GET.CELL(7,current)))
      A8: =ELSE()
      A9: =FWRITE(getname,current)
      A10: =END.IF()
      A11: =SET.NAME("nextcol",nextcol+1)
      A12: =IF(nextcol=colcount)
      A13: =FWRITE(getname,CHAR(13)&CHAR(10))
      A14: =SET.NAME("nextcol",0)
      A15: =ELSE()
      A16: =FWRITE(getname,CHAR(9))
      A17: =END.IF()
      A18: =NEXT()
      A19: =FCLOSE(getname)
      A20: =RETURN()

  2. Select cell A1.

  3. From the Formula menu, choose the Define Name command. Verify that TextFile Macro appears in the Name box, and A1 appears in the Refers To box.

  4. Under Type, select the Command option and choose OK.

  5. Switch to the sheet that contains the range that you want to export and select that range.

  6. From the Macro menu, choose Run. In the Macro dialog box, select the TextFile macro and choose OK.

A dialog box will prompt you for a filename, and the file will be written to that name.

CAUTION: Any existing files with that same name will be overwritten by the new text file.

Explanation of macro

A1: Macro name A2: Initialize a variable "nextcol" equal to zero A3: Open a sequential file for write access using user-provided name 

    stored in "getname"
A4: Initialize a variable "colcount" to number of columns in the 
    selected range
A5: Begin a FOR.CELL loop through the selected range A6: Check to see if current cell contains a number not formatted to 
    General
A7: If the current cell meets above criteria, write current cell 
    contents to file with formatting
A8: Otherwise A9: Write current cell contents to file without formatting A10: End If clause A11: Increment "nextcol" A12: Check to see if end of current row in selection A13: Write return and line feed to file A14: Reset "nextcol" to zero A15: Otherwise A16: Write a tab character to file A17: End If clause A18: Go to next loop iteration A19: Close file on completion A20: End macro

Additional reference words: 4.00 4.0a 4.00a
Keywords : kbmacro kbprb kbprg kbprb
Version : 2.X 3.00 4.00 4.00a | 2.2 3.00
Platform : OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.