This article was previously published under Q104997
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel, to save a selection of data as a tab-delimitedtext file, select the Text file format in the Save File As Type box inthe Save As dialog box. When you save a file in this format, if any ofthe cells in the spreadsheet contain commas, quotation marks, or othercharacters 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 wantthese additional quotation marks to be added, you must use a macro tosave the data to a text file.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To create a tab-delimited text file without enclosing text strings inquotation 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.
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 bewritten to that name.
CAUTION: Any existing files with that same name will be overwrittenby 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.