Microsoft KB Archive/103985

{|
 * width="100%"|

XL4: Macro to Export Text File with Commas and Quotation Marks

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

-

SUMMARY
In Microsoft Excel, there is no menu command to automatically export data to a text file such that the text file is exported with quotation marks AND commas as delimiters. For example, there is no command to automatically create a text file that contains the following:

  "Text1","Text2","Text3"

If you want to export data that resembles the above example, you can use the FWRITE function in a macro to export a text file. The "More Information" section of this article includes a sample macro that demonstrates one way to do this.

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 does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

Note that you must select the cells that you want to export before you run the following sample macro.

Sample Macro
   A1: =ERROR(FALSE) A2: =SET.NAME("FName",INPUT("Export to Path\Filename: ",2)) A3: =IF(FName=FALSE,RETURN) A4: =SET.NAME("FileNum",FOPEN(FName,3)) A5: =IF(ISERROR(FileNum),RETURN) A6: =SET.NAME("StartCell",TEXTREF(GET.CELL(1,SELECTION),TRUE)) A7: =FOR("RowCount",0,ROWS(SELECTION)-1) A8: =FOR("ColCount",0,COLUMNS(SELECTION)-1) A9: =SET.NAME("CurrentCell",OFFSET(StartCell,RowCount,ColCount)) A10: =IF(AND(ISNUMBER(CurrentCell),GET.CELL(7,CurrentCell)<>"General")) A11: =FWRITE(FileNum,""""&TEXT(CurrentCell,GET.CELL(7,        CurrentCell))&"""") A12: =ELSE A13: =FWRITE(FileNum,""""&CurrentCell&"""") A14: =END.IF A15: =IF(ColCount=COLUMNS(SELECTION)-1,FWRITELN(FileNum,""),        FWRITE(FileNum,",")) A16: =NEXT A17: =NEXT A18: =FCLOSE(FileNum) A19: =RETURN

Explanation of Above Macro --

A1: Turns error checking off. A2: Prompts for the filename to export to. A3: Checks to see if Cancel was selected from line two. If so, ends the macro. A4: Opens a new file and stores the file number to variable "FileNum." A5: Checks to see if an empty file was created, if not, it ends the macro. A6: Determines the top left cell of the current selection, and assigns it to variable "StartCell." A7: Begins loop based on the number of rows selected. A8: Begins loop based on the number of columns selected. A9: Determines the current cell in the loop by offsetting the "StartCell" by the loop counters "RowCount" and "ColCount." A10: Tests if "CurrentCell" is numeric and formatted other than a       General number format. A11: If line 10 is true, write the contents of "CurrentCell" to the file retaining the number format enclosed in quotation marks. A12: Else statement for line 10. A13: If line 10 is false, write the contents of "CurrentCell" to the file, and enclose this entry in quotation marks (").  A14: End the prior IF statement.   A15: Check to see if "CurrentCell" is in the last column. If so, go to        next line in export file. Otherwise, write a comma  to the        current line in the export file.   A16: Loops back to line 8.   A17: Loops back to line 7.   A18: Closes the export file.   A19: Ends the macro.