Consulting | Excel Examples

Exporting Excel Data in Special Formats

You can use a VBA procedure to export data in comma delimited format. Or, change the delimiting character to export the data separated by semicolons, etc.

Microsoft Knowledgebase Article

PSS ID Number: Q123183
Article last modified on 09-15-1995
PSS database name: EXCEL
5.00 5.00c 7.00 | 5.00
WINDOWS | MACINTOSH
--------------------------------------------------------------------
The information in this article applies to:
- Microsoft Excel for Windows, versions 5.0, 5.0c
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for the Macintosh, version 5.0 --------------------------------------------------------------------
SUMMARY
=======
In Microsoft Excel, there is no menu command to automatically export
data to a text file so 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"
You can create this functionality in Microsoft Excel by using a
Visual Basic procedure.
MORE INFORMATION
================
You can use the Print # statement in a Visual Basic procedure similar to the following to export a text file with quotation marks AND commas as the delimiters. For the procedure to function properly, you must select the cells that contain your data before you run it. Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

Sub QuoteCommaExport()

' Dimension all variables
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination filename

DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number
FileNum = FreeFile()

' Turn error checking off
On Error Resume Next

' Attempt to open destination file for output
Open DestFile For Output As #FileNum

' If an error occurs report it and end
If Err <> 0 Then

MsgBox "Cannot open filename " & DestFile
End

End If

' Turn error checking on
On Error GoTo 0

' Loop for each row in selection
For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotes
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";

' Check if cell is in last column
If ColumnCount = Selection.Columns.Count Then

' If so then write a blank line
Print #FileNum,
Else

' Otherwise write a comma
Print #FileNum, ",";

End If

' Start next iteration of ColumnCount loop
Next ColumnCount

' Start next iteration of RowCount loop
Next RowCount

'Close destination file
Close #FileNum

End Sub

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

ARTICLE-ID: Q103985

TITLE : Macro to Export Text File with Comma AND Quote Delimiters

KBCategory: kbprg kbcode

KBSubcategory:

Additional reference words: 7.00 5.00 5.00c export quotes commas csv

=============================================================================

Copyright Microsoft Corporation 1995.

Last modified: May 21, 1996