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

• 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"

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.

REFERENCES

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:

   ARTICLE-ID: Q123183
   TITLE     : XL: Procedure to Export Text File with Comma AND Quote
               Delimiter