Microsoft KB Archive/103985
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
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:
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.
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.
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.
For information on how to use a Visual Basic macro to export a text file with both comma and quote delimiters, please see the following article in the Microsoft Knowledge Base:
Q123183 : XL: Procedure to Export Text File with Comma AND Quote Delimiter
"Function Reference," version 4.0, page 182
"Function Reference," version 3.0, page 99
Additional query words: 3.0 4.00a export quotes marks howto
Keywords : kbmacro
Version : WINDOWS:3.0,4.0,4.0a; MACINTOSH:3.0,4.00
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo
Last Reviewed: April 6, 1999