Comma Delimited ASCII "SaveAs" File

Posted by Paul Cullen on December 01, 2001 5:28 AM

ASCII Export Problem

It seems like this should be simple problem, but I can't figure it out.

This is best asked by example:

Cell A1 = 123
Cell B1 = ABC
Cell C1 = 456
Cell A2 = 7890123
Cell B2 = DEF
Cell C2 = 4567890

I want to "SaveAs" a comma delimited ASCII file that looks like this:

"123","ABC","456"
"7890123","DEF","4567890"

HELP --- How do you get those crazy quotes to export
correctly to the ASCII file. I can get no quotes, or I can get triple quotes, but I can't seem to get a single quote.

Posted by Ivan F Moala on December 01, 2001 12:38 PM

Try this;

Select your data then run this macro;

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 file name.
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 &LT;&GT; 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 quotation marks.
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

Ivan