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

ID: Q104997

The information in this article applies to:

Microsoft Excel 97 for Windows
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 programming 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

To create a tab-delimited text file without enclosing text strings in quotation marks, use the appropriate method for your version of Excel.

Microsoft Excel 97

In a new Microsoft Excel 97 workbook, insert a macro sheet by following these steps:


      a.  Right-click a sheet tab and then click Insert.
      b.  In the Insert dialog box, click to select MS Excel 4.0 Macro
      c.  Click OK.

Click the macro sheet tab and 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()

Select cell A1, on the Macro sheet.

On the Insert menu, point to Name and click Define. Click the TextFile Macro so that the title appears in the "Names in workbook" box.

In the "Refers to" box, verify the address of the macro is:

=macrosheetname!$a$1

Under Macro, click Command, and then click OK.

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

On the Macro menu, click Run. In the Macro dialog box, select the TextFile macro and click OK.

A dialog box will prompt you for a file name, and the file will be written to that name. Name should include the file name extension if needed.

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

Microsoft Excel Version 4.0a and Earlier

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()

Select cell A1.

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.

Under Type, select Command and click OK.

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

On the Macro menu, click Run. In the Macro dialog box, click the TextFile macro and click OK.

A dialog box will prompt you for a file name, 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 query words: 4.00a

Keywords : kbmacro kbprg kbprb
Version : WINDOWS:2.X,3.0,4.0,4.0a,97; OS2: 2.2,3.0
Platform : OS/2 WINDOWS
Issue type :